maxium date used

[kumari070481]

Hi All,
I have 5 primary keys. And one of the primary key is date. I have write a sql in such way that I have to extract the date which is max( i mean recent dates).And extract the data for remaining keys.
I am writing the sql qury in this way:

SELECT A1,A2,A3,MAX(DATE),A4 FROM T1
WHERE A1=?,A2=?,A3=?,A4=?
GROUP BY A1,A2,A3,A4

but this query returns one row. I want all the row which as this date in it. Can anyone suggest how can i do that ..

Thank you..

 

[ddiamond]

Do you want to select all records who's date equals the maximum date in the table. If so the following should work:

SELECT A1,A2,A3,DATE,A4 
FROM T1
WHERE DATE IN (
  SELECT MAX(DATE)
  FROM T1)
;

 

[MarcLodge]

Hi,
a furthe thought...... if the 4 key fields could be the same with only the date being different..... and you wanted the most up to date row for each combination of the four key fields.... you could:

SELECT A.A1,A.A2,A.A3,A.DATE,A.A4
FROM T1 A
WHERE A.DATE =
(SELECT MAX(Z.DATE)
FROM T1 Z
WHERE Z.A1 = A.A1
AND Z.A2 = A.A2
AND Z.A3 = A.A3
AND Z.A4 = A.A4)

Hope this helps.

Marc