Skip to content
Advertisement

Selecting most recent rows in a SQL query

I want to join two tables, selecting the most recent rows for an ID value present in table 1.

i.e. For each ID value in table 1, only return the most recently added row for an ID value. For example, table 1 looks something like this:

So if the same ID value is found twice in this table, only return the more recent entry, row 1 in the above case.

I then want to join these rows with information present in a second table. e.g. table 2 looks something like this:

  • My sql query below works as expected for joining the two tables
  • but what I can’t work out is how to only return the most recent rows from table 1 when duplicate ID values have been entered on multiple dates
  • Also not sure if the date filtering should occur as part of the SELECT or when first fetching data from table 1

From looking elsewhere in StackOverflow the suggestions are things like MAX(date_time) – but my understanding is that this will only return the maximum date time value, not the most recent row – correct me if I’m wrong. My query looks something like this:

Other suggestions on StackOverflow: SELECT TOP id_1...min(info_4) (gives syntax error), ORDER BY id_1... date_time DESC LIMIT 1 (only returns one row – i.e. most recent date time).

ROW_NUMBER() OVER (PARTITION BY id, ORDER BY date_time) AS 'row_number' returns a row number, not the most recent row.

Advertisement

Answer

So if the same ID value is found twice in my table, only return the more recent entry, row 1 in the above case.

You can use row_number():

I really have no idea what your query has to do with your question. If your “table” is really the result of the query, then just use a CTE or subquery:

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement