|
View previous topic :: View next topic
|
| Author |
Message |
ashishsr123
New User
Joined: 06 May 2008
Posts: 33
Location: Chennai
|
|
|
|
Hello Everyone!,
I need help in framing one query:
Details:
Below are 3 fields and I want to group rows ,condition as follows.
Show row for latest date (for field B )
by latest date I mean 06/28/2002 >01/14/2001 , 06/28/2002 is latest date.
If
there are two latest date
then
take field C and find out latest date rows in C
See below for more explanation.
Table(TB ) has following fields( just giving fields in question):
| Code: |
A B C
2 06/28/2002 01/14/2001
2 11/21/1997 04/25/1995
2 09/19/2006 12/27/2002 <--- B has latest date ,so we show this row
3 01/23/2001 11/22/1999
3 02/19/2007 10/23/2002 <--- B has latest date , so show this row
3 17/06/2000 09/14/2002
4 10/25/2002 06/22/2001
4 10/25/2002 04/24/1980
<--- B has latest date but 10/25/2002 appears twice ,now consider C and 06/22/2001 > 04/24/2001, so show this row.
4 04/25/2002 05/12/1981
4 03/13/1999 02/11/1985
5 06/06/1998 05/05/1995
The output should be :
A B C
2 09/19/2006 12/27/2002
3 02/19/2007 10/23/2002
4 10/25/2002 06/22/2001
5 06/06/1998 05/05/1995
|
The query for just finding latest of B is as followed
| Code: |
SELECT A, MAX(B),C
FROM TB
GROUP BY A
WITH UR;
|
I want to extend it to above requirement.
Kindly help .. |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| what vsn of db2 are you using? |
|
| Back to top |
|
|
ashishsr123
New User
Joined: 06 May 2008
Posts: 33
Location: Chennai
|
|
|
|
DB2
Version 8 Release 1 |
|
| Back to top |
|
|
Poonam Jain
New User
Joined: 06 Apr 2009
Posts: 5
Location: mumbai
|
|
|
|
Hi,
Try below query............
SELECT X.A,X.B,MAX(Y.C)
FROM
(
SELECT A, MAX(B) AS B
FROM TABLE
GROUP BY A) X INNER JOIN TABLE Y
ON X.B= Y.B
GROUP BY X.A,X.B |
|
| Back to top |
|
|
|
|
