Create SQLite request for deleting records with duplicated values

[Vitaliy4us]

I have a table having the structure like this:

test_key | step number | step           |   test result |
xxxx        1             aaaaaaaaa         bbbbbbbbb
xxxx        2             ccccccccc         ddddddddd
..........................
xxxx        n             eeeeeeeee         fffffffff

yyyy        1             ggggggggg         hhhhhhhhh
yyyy        2             ggggggggg         hhhhhhhhh
..........................
yyyy        s             iiiiiiiii         jjjjjjjjj

..........................
..........................

xxxx        1             kkkkkkkkk         lllllllll ==> needs to be deleted
xxxx        2             mmmmmmmmm         ooooooooo ==> needs to be deleted
..........................                            ==> needs to be deleted
xxxx        n             ppppppppp         ppppppppp ==> needs to be deleted

..........................
..........................

The table contains some sets of test_key values which duplications need to be deleted. As you can see I want to keep only one set of the steps for the each test (e.g. xxxx with 10 steps, yyyy with 20 steps, zzzz with 5 steps etc.). The thing I need to do is to create SQL request for deleting all of the xxxx, yyyy, zzzz except of the first set which will be found in the table. Could anyone advice a solution?

[Jos]

... and how is this an AutoIt3 support question? 

Jos

[jchd]

This is in fact a generic SQL question.

An SQL table has no inherent order (it's a mathematical set) so your phrase "deleting all of the xxxx, yyyy, zzzz except of the first set which will be found in the table" has no meaning.  In the absence of an ORDER BY clause SQLite, as any SQL engine, is free to return return of any run of the same SELECT in any random order it wants, and even if this apparent "order" seems stable that doesn't mean it's the same as the visiting order used for either UPDATE or DELETE.

[Nine]

Just dump the table into an array, delete all the unwanted rows, recreate the table with a unique index (to enforce unicity), copy back the rows...

[mikell]

When deleting lines from an array some kind of condition is also needed
It's faster/cleaner to define a precise DELETE...WHERE... instruction to directly work on the database

[Zedna]

create table test
(
 test_key varchar(4),
 step_num int,
 step varchar(10),
 test_result varchar(10)
)

insert into test (test_key, step_num, step, test_result)
values('xxxx',1,'aaa','bbb')
insert into test (test_key, step_num, step, test_result)
values('xxxx',2,'ccc','ddd')
insert into test (test_key, step_num, step, test_result)
values('xxxx',3,'eee','fff')

insert into test (test_key, step_num, step, test_result)
values('yyyy',1,'g1','h1')
insert into test (test_key, step_num, step, test_result)
values('yyyy',2,'g2','h2')
insert into test (test_key, step_num, step, test_result)
values('yyyy',3,'g3','h3')
insert into test (test_key, step_num, step, test_result)
values('yyyy',4,'g4','h4')

select * from test

delete test
 where test_key + convert(varchar(10),step_num) not in
        (select max(b.test_key + convert(varchar(10),b.step_num)) from test b where b.test_key = test.test_key)

select * from test

drop table test

 

Results:

test_key    step_num    step    test_result
xxxx    1   aaa bbb
xxxx    2   ccc ddd
xxxx    3   eee fff
yyyy    1   g1  h1
yyyy    2   g2  h2
yyyy    3   g3  h3
yyyy    4   g4  h4

-->

test_key    step_num    step    test_result
xxxx    3   eee fff
yyyy    4   g4  h4

 

EDIT:

To maintain FIRST group instead of LAST one, change SELECT MAX() to SELECT MIN() ...

In that case result will be:

test_key    step_num    step    test_result
xxxx    1   aaa bbb
yyyy    1   g1  h1

 

Edited October 29, 2019 by Zedna

[jchd]

@Zedna, SQLite uses || for string concatenation and cast(id, type) for conversion.  Your snippet isn't SQLite-compatible, which isn't my real point.

Anyway I fail to see how this query could delete what the OP calls "subsequent sets".  Again, a table has no order and there is no possibility (given the little information the OP gave) to differentiate between the first line shown:

xxxx        1             aaaaaaaaa         bbbbbbbbb

and the subsequent line

xxxx        1             kkkkkkkkk         lllllllll ==> needs to be deleted

Unless there is a way to determine which "set" or "group" a row is part of, in conformance to what the OP has in mind, either the job can't be done or we are missing hidden information.

[Zedna]

@jchd

1) My SQL is in MS SQL dialekt.

2) According to first post: combination of test_key and step_number is unique and OP needs to keep row with first step_number for each unique test_key

  which is exactly what my SQL does.

[jchd]

Please reread the OP: the issue is to delete the "subsequent" group of keys xxxx (the rows marked "needs to be deleted") but keep the "first batch" of keys xxxx and step_number 1 to n.

And that isn't reliably feasible in SQL unless there is a way to differentiate between the "first batch in table" with key xxxx (which has no meaning since an SQL table has no inherent order) and "subsequent batches" of the same key xxxx.

Your SQL deletes everything but one (and only one as max() returns a single value)  of the rows which the implementation chooses to select because the  expression

max(b.test_key + convert(varchar(10),b.step_num))

requires a choice between the two rows

xxxx        1             aaaaaaaaa         bbbbbbbbb
xxxx        1             kkkkkkkkk         lllllllll ==> needs to be deleted

Which one is left to the implementation and an SQL engine is free to choose one of them at random, even return different rows on two identical queries launched "in a row".

Edited October 30, 2019 by jchd

[Zedna]

OK.

Thanks for clarification.

Now after rereading I can see you are right.

Sorry to all for my misreading.

[jchd]

No problem it happens daily.

[jchd]

Besides this little misreading, the issue of "SQL table order" is extremely common.

People new to SQL more than often think of an SQL table as an Excel sheet (formulas apart).  There is a huge difference between both: an Excel sheet is a list while an SQL table is a set.  In a mathematical set you can't have twice the same value, which is possible in a list.  And while the order of a list is important and must be conserved until explicitely changed (think of the steps of a recipe or build process or composition of rotations in 3D space [they aren't commutative]), there is no order in the set of ingredients of a recipe, nor in the set of tools needed to build something.

The best way to get rid of this mental biais is to remember that a SELECT without an explicit ORDER  BY clause needs to be regarded as if it had an implied ORDER BY random() clause and think of your tables this orderless way.

@Vitaliy4us apply the above rule to your request and see that is doesn't have any meaning unless more discreminating information is available.