DISTINCT and GROUP BY with SQL Server

Question

I have the following table (sql server) and i'm looking for a query to select the last two rows with all fields:

• order by created_at

• group by / distinct type_id

  
  id type_id some_value created_at 
  1  B       mk2        2016-10-01 00:00:00.000
  2  A       mbs        2016-10-01 10:02:39.077
  3  B       sa         2016-10-02 10:03:08.123
  4  A       xc         2016-10-02 10:03:28.777
  5  B       q1         2016-10-03 10:04:20.920
  6  A       tr         2016-10-03 10:04:48.533
  7  A       1a         2016-09-30 10:36:26.287
  

In MySQL its an easy task - but with SQL Server all fields have to be contained in either an aggregate function or the GROUP BY clause. But that results in field combinations that does not exist.

Is there a way to handle this?

Thanks in advance!

Answer 1

Solution

Based on the comment from Andrew Deighton i did this:

SELECT *
FROM (
       SELECT
         id,
         type_id,
         some_value,
         created_at,
         ROW_NUMBER()
         OVER (PARTITION BY type_id
           ORDER BY created_at DESC) AS row
       FROM test_sql
     ) AS ts
WHERE row = 1
ORDER BY row

Conclusion: No need for GROUP BY and DISTINCT.