query group by with order by

Question

i need to create query with group by and order by, and i dont know how to do it.

query should return one record for the newest date for existing device_serial_number. enter image description here

so i would to get id 591 nad 592

solution can be in sql or the best way it will be in symfony, through query builder etc.

Answer 1

There are many ways to accomplish what you want.

First Way The oldest way to select first, best, worst, whatever within a group is with a correlated subquery:

Select * from mytable outer
Where created_at = (
    Select max(created_at)
    from mytable inner
    Where inner.device_serial_number = outer.device_serial_number
)

Second Way Use a subselect to find earliest dates for all devices, them join back to the original table to filter:

Select a.*
From mytable a Inner Join
     (Select device_serial_number, max(created_at) as latedate
       From mytable b
       Group By device_serial_number
      ) b
      On a.device_serial_number=b.device_serial_number
       And a.created_at=b.latedate

Third way Use a window function to rank order all the dates and then pick the number one ranking.

Select * From (
    Select *
        , rank() Over (Partition By device_serial_number Order by created_at desc) as myrank
    From mytable
)
Where myrank=1

Notice that while these 3 solutions use different aspects of SQL, they all have a common analytical approach. They are all two step processes whose first (inner) part involves finding the most recent created_at date for each device_serial_number and then reapplying that result back to the original table in the second (outer) part.