How to select the first row from each group in SQL Server

Question : How to select the first row from each group in SQL Server ?

 > SELECT * FROM Tickets:
ticketid | Name   | CreatedTime
 1 | Jack      | Feb 1st 
 2 | Jill    | Feb 2nd
 3 | Jack     | Feb 4th
 4 | JIll    | Feb 9th

If you want to retrieve the most recent date when each customer has bought their ticket. What is the latest date of Jack bought a ticket ? Solution:

WITH Ticks AS (
    SELECT tic.ticketid, 
           tic.name, 
          tic.CreatedTime, 
           ROW_NUMBER() OVER(PARTITION BY p.name
                                 ORDER BY p.CreatedTime DESC) AS rk
      FROM TICKETS tic)
SELECT t.*
  FROM Ticks t
 WHERE t.rk = 1

Share this