问题:

I'm using Microsoft SQL. I have a table that contains information stored by two different categories and a date. For example:

ID   Cat1   Cat2   Date/Time   Data  
1    1      A      11:00       456
2    1      B      11:01       789
3    1      A      11:01       123
4    2      A      11:05       987
5    2      B      11:06       654
6    1      A      11:06       321

I want to extract one line for each unique combination of Cat1 and Cat2 and I need the line with the oldest date. In the above I want ID = 1, 2, 4, and 5.

Thanks

回答1:

Have a look at row_number() on MSDN.

SELECT  *
FROM    (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY date_time, id) rn
        FROM    mytable
        ) q
WHERE   rn = 1

(run the code on SQL Fiddle)

回答2:

Quassnoi's answer is fine, but I'm a bit uncomfortable with how it handles dups. It seems to return based on insertion order, but I'm not sure if even that can be guaranteed? (see these two fiddles for an example where the result changes based on insertion order: dup at the end, dup at the beginning)

Plus, I kinda like staying with old-school SQL when I can, so I would do it this way (see this fiddle for how it handles dups):

select *
from my_table t1
  left join my_table t2
    on t1.cat1 = t2.cat1
    and t1.cat2 = t2.cat2
    and t1.datetime > t2.datetime
where t2.datetime is null