[Solved] Select from Select [closed]

by


If I understand correctly what you want you just need to JOIN with Car table like this

SELECT c.PlateNo, TicketStatus, EventTime 
  FROM 
(
  SELECT ROW_NUMBER() OVER 
       (PARTITION BY CarID ORDER BY EventTime) AS rnum
       , CarID, TicketStatus, EventTime
   FROM Event
) a JOIN Car c
   ON a.CarID = c.CarID  
 WHERE c.Package = 4
   AND a.rnum > 1

or

SELECT PlateNo, TicketStatus, EventTime
  FROM 
(
  SELECT e.CarID, e.TicketStatus, e.EventTime, c.PlateNo,
         ROW_NUMBER() OVER 
         (PARTITION BY e.CarID ORDER BY e.EventTime) AS rnum 
    FROM Event e JOIN Car c 
      ON e.CarID = c.CarID 
   WHERE c.Package = 4
) a
 WHERE a.rnum > 1

Here is SQLFiddle demo

solved Select from Select [closed]