CODEWITHSUNDEEP
sqlduplicatesgrouping
Tr4nce
Tr4nce

Reputation: 87

SQL - Grouping duplicates and unique id issue

hope someone can point me in the right direction. I'm having a bit of a hair pulling moment.

Here is some sample data:

ID    SUBID   COL1      COL2      COL2      COL3
1     10001   Test 1    Test 2    Test 3    Test 4
2     10002   Test 1    Test 2    Test 3    Test 4
3     10003   Test 1    Test 2    Test 3    Test 4
4     10004   Test 1    Test 2    Test 3    Test 4
5     10005   Test 5    Test 6    Test 7    Test 8

I need the result to look like this:

ID    SUBID   COL1      COL2      COL2      COL3
4     10004   Test 1    Test 2    Test 3    Test 4
5     10005   Test 5    Test 6    Test 7    Test 8

I need to group all duplicates based on COL1, COL2, COL3 & COL4 which is easy but I need to output a number from the groupings in the ID and SUBID column, it doesn't matter which of the grouped rows this comes from, it just needs a value.

Performance is also key in doing this as I'm querying millions of rows.

Hope the above makes sense and thank you.

Upvotes: 2

Views: 2567

Answers (2)

juergen d
juergen d

Reputation: 204784

select id, subid, COL1, COL2, COL3, COL4
from your_table
where id in (
             select min(id)
             from your_table
             group by COL1, COL2, COL3, COL4
            )

Upvotes: 0

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

You could choose one by using min or max. They won't be from the same row, but it is "just a value".

select min(id), min(subid), col1, col2, col3, col4
from tab
group by col1, col2, col3, col4

Upvotes: 1

Related Questions