SQL Select all records where unique composite key

Question

Example table:

foo bar baz gif xyz
--- --- --- --- ---
me  my  at  bb  cc
me  my  at  aa  zz
qq  33  er  tt  oo
22  jj  pp  ww  mm

I need a SQL that returns the unique records for fields "foo, bar, baz". In this example, three of the four records should be returned:

me, my, at, bb, cc
qq, 33, er, tt, oo
22, jj, pp, ww, mm

What makes you choose bb and cc? Does it matter if you get aa and cc? is there another column eg ID or datetime that gives precedence? — gbn, Mar 14 '11 at 19:56
Generally speaking it is an invalid task for SQL since your first row is ambigious and you are not saying how to determine which row to take - the one ending with `bb, cc` or another one with `aa, zz` at the end. In your sample you've selected `bb, cc` due to some reason (order?), but SQL doesn't have an `order` for rows until you specify `order by` — Snowbear, Mar 14 '11 at 19:58

score 2 · Accepted Answer · answered Mar 14 '11 at 19:57

2

In the absence of any rules for gif and xyz, this will take the lowest values of each

SELECT
   foo, bar, baz, MIN(gif), MIN(xyz)
FROM
   MyTable
GROUP BY
   foo, bar, baz

answered Mar 14 '11 at 19:57

gbn

422,506
82
585
676

this will return the three records only? – bmw0128 Mar 14 '11 at 20:03
1

@bmw0128: yes, it's an aggregate- However, check the comments on your question for how to choose gif and xyz – gbn Mar 14 '11 at 20:05
i now see what you mean, i just want to know if the combo exists, so I do not care about gif and xyz...so if your solution will only bring back unique combo values, that's what i need – bmw0128 Mar 14 '11 at 20:08
1

Well it will not isolate 'the row' just the minimum values for the other columns - you would not get the output that you stated you needed – Transact Charlie Mar 14 '11 at 21:00

score 1 · Answer 2 · answered Mar 14 '11 at 20:58

You haven't said which version of SQL this is.....?

In sql server 2005 or greater this is a ROW_NUMBER() function that will let you select the first row (ordered by whatever predicate you provide)

The query would look like

SELECT
    [foo]
    , [bar]
    , [baz]
    , [gif]
    , [xyz]
FROM
    (
    SELECT
        [foo]
        , [bar]
        , [baz]
        , [gif]
        , [xyz]
        , ROW_NUMBER() OVER (
            PARTITION BY [foo], [bar], [baz]
            ORDER BY [gif] DESC, [xyz] DESC
            )
            AS [rnk]
    FROM
        <TheTable>
    )
WHERE
    [rnk] = 1

I believe there is a similar function (they are called windowing functions) in Oracle. I don't know about mysql

score 0 · Answer 3 · answered Mar 14 '11 at 19:56

0

Not much to it, really:

select *
from myTable t1
join ( select distinct foo,bar,baz
       from myTable
     ) t2 on t2.foo = t1.foo
         and t2.bar = t1.bar
         and t2.baz = t1.baz

answered Mar 14 '11 at 19:56

Nicholas Carey

71,308
16
93
135

SQL Select all records where unique composite key

3 Answers3