1

I want to return date of max value for a particular id. Look at this table.

ID       Date       Value 
___      ____       _____
4545     9/17/12    5
4545     9/16/12    100
4545     9/15/12    20
2121     9/16/12    12
2121     9/15/12    132
2121     9/14/12    4
9999     9/16/12    45
9512     9/15/12    128
9512     9/14/12    323
2002     9/17/12    45

The results should be:

ID       Date       Value 
___      ____       _____ 
4545     9/16/12    100       date for ID(4545) for max value(100)  is "9/16/12"
2121     9/15/12    132       date for ID(2121) for max value1(32)  is "9/15/12"
9999     9/16/12    45        date for ID(9999) for max value(45)   is "9/16/12"
9512     9/14/12    323       -||-
2002     9/17/12    45        -||-

How i get this date?

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
Lukáš Pichl
  • 15
  • 4
  • What platform/language/database? And what have you tried? It's hard to reply without details. – Matt Johnson-Pint Sep 14 '13 at 16:03
  • Language sql, database MySQL. I need to copy date of max value for particular id from onne table to another table. I am beginer, so dont know what more do you need to help me. But thx for help :) – Lukáš Pichl Sep 14 '13 at 16:15
  • This is very similar to [this question](http://stackoverflow.com/questions/12464669/return-value-at-max-date-for-a-particular-id) although not an *exact* duplicate because it's taking the max date rather than the max value you are looking for. But you should be able to adjust the answer accordingly. – Matt Johnson-Pint Sep 14 '13 at 16:32

2 Answers2

0

Assuming you are using SQL, you should try something like this (this is MS SQL syntax):

SELECT [ID], [Date], [Value]
FROM [YourTable] AS [SRC]
WHERE NOT EXISTS (SELECT *
                  FROM [YourTable] AS [OTHER]
                  WHERE [OTHER].[ID] = [SRC].[ID] AND [OTHER].[Date] > [SRC].[Date])

This is probably not the most efficient way to do this, but it should work...

Barak Itkin
  • 4,872
  • 1
  • 22
  • 29
0

Calculate the set of IDs and their maximum values, then intersect that with your original set.

SELECT * FROM t
JOIN (SELECT id, MAX(value) AS value FROM t GROUP BY id) AS max_t
USING (id, value)
Chris Bandy
  • 1,498
  • 13
  • 7