-1

I have a dataset with:

Id.   Code. Update time
222.  A.    1/1/16
222   B.    1/1/17
123   c     1/1/16
123   d.    1/1/17

I want the data set to filter to:

222   B.    1/1/17
123   d.    1/1/17

How do I do the query for this?

David
  • 1
  • Looks like you want to `SELECT` the second and fourth lines. What is your criteria? My guess: for each `Id`, select the latest `update time`. – Bob Kaufman Jan 18 '17 at 23:08
  • What flavor of SQL? Sql Server? Oracle? MySQL? (insert other SQL dialects here). – STLDev Jan 18 '17 at 23:11
  • Possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – rghome Jan 22 '17 at 13:55
  • This has been asked many times before. Please do not answer and close as duplicate. – rghome Jan 22 '17 at 13:56
  • See here: http://stackoverflow.com/questions/tagged/greatest-n-per-group+sql –  Jan 22 '17 at 14:44

2 Answers2

0

First write a sub-query that finds the latest date for each ID. Then join the sub-query back to the main table to grab the other column(s):

SELECT t.ID, t.code, t.updatetime
FROM table t
INNER JOIN
(SELECT ID, MAX(updatetime) AS mxtime
 FROM table
 GROUP BY ID) sub
ON t.ID = sub.ID
AND t.updatetime = sub.mxtime
kjmerf
  • 4,275
  • 3
  • 21
  • 29
0

As written, this is a simple aggregation query:

select id, max(code) as code, max(updatetime) as updatetime
from t
group by id;

If this is not what you want, then you need to be clearer about the logic you want to implement.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786