3

I have this table

create table  sd_devices (
device_code varchar(128),
vin varchar(128),
created_at  datetime,
loaded_at   datetime
)

I would like to select for each device_code the first vin order by created_at desc, loaded_at desc.

In oracle, I would have use keep dense_rank, but here with MySQL i do not know how to do that.

Steven
  • 14,048
  • 6
  • 38
  • 73

1 Answers1

2

this should do it:

SELECT DISTINCT device_code,
       vin
       FROM (
SELECT sd_devices.*
FROM sd_devices
ORDER BY created_at DESC, loaded_at DESC ) AS a
GROUP BY device_code

In the subquery you select all the rows and order them in the correct order, Then in the outside query you use DISTINCT to get rid of any Duplicate rows and it will use the first one it encounters. Because we already put it in the correct order it will grab the one row we want.

Jester
  • 1,408
  • 1
  • 9
  • 21
  • ;') there are worse/longer queries to be found :p no problem, good luck! – Jester Apr 04 '16 at 05:53
  • @Jester - I believe the bleeding eyes were from the all-caps rather than the length of the query. {:-) –  Dec 02 '16 at 18:45
  • @mathguy oh i'm not the only one :p you can even find some debate about it on this site: http://stackoverflow.com/questions/608196/why-should-i-capitalize-my-sql-keywords It's kind of a habit of mine, it's useful when you have queries in strings in your code which doesn't get recognized by your IDE. But others, like the answer with the most votes in that discussion, just find it annoying and sound like shouting :p – Jester Dec 02 '16 at 19:27
  • I am not taking sides; I also tend to write everything in lowercase (I liked C better than FORTRAN in that regard) but my eyes don't bleed over UPPERCASE, I have no problem with it. Just pointing out that the responder seemed to have meant something different from what you thought. :-) –  Dec 02 '16 at 20:18