SQL Select all records where unique composite key

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, 3开发者_如何转开发3, er, tt, oo
22, jj, pp, ww, mm

---

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

---

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

---

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