Choosing SQL Server

Question

I want to write a query like this:

For a table that has these columns: ColA ColB ColC, ColD

select first (ColA, ColB, ColC, ColD) (ColB, ColC) from the table order from ColD

The query must order a table by ColD, and then group the results by combinations of ColB and ColC (they can have different data types) and returns the first rows (with all columns of the table) in groups.

How is this possible in MS SQL Server 2005?

Answer 1

It looks like you want "max for each group."

- windowing ROW_NUMBER , 1:

SELECT ColA, ColB, ColC, ColD
FROM
(
    SELECT
         ColA, ColB, ColC, ColD,
         ROW_NUMBER(PARTITION BY ColB, ColC ORDER BY ColD) AS rn
    FROM table1
) T1
WHERE rn = 1