|
View previous topic :: View next topic
|
| Author |
Message |
PrabakarV
New User
.jpg)
Joined: 21 Dec 2007
Posts: 88
Location: My Desk
|
|
|
|
Hi,
| Code: |
C1 C2
2 12/31/1980
2 12/31/1983
2 12/31/1985
3 12/31/1986
3 04/30/1987
4 12/31/1987
4 12/31/1988
4 07/31/1990
5 04/30/1996
5 06/30/1997
6 12/31/1997
6 04/30/1998 |
I need to select distinct Column 1 and the max date for each column 1 value as below,
| Code: |
2 12/31/1985
3 04/30/1987
4 07/31/1990
5 06/30/1997
6 04/30/1998 |
Please assist me with the query.
Thanks |
|
| Back to top |
|
 |
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| How about showing us what you have tried and the results? |
|
| Back to top |
|
|
PrabakarV
New User
Joined: 21 Dec 2007
Posts: 88
Location: My Desk
|
|
|
|
| Code: |
SELECT C1, MAX(C2)
FROM TABLE
GROUP BY C1
WITH UR; |
Got it. Thanks for the help. |
|
| Back to top |
|
|
dejunzhu
Active User
Joined: 08 May 2008
Posts: 390
Location: China
|
|
|
|
what if I want to retrieve all the rows except the max date???
I want to get below result, how to compose the SQL statement?
| Code: |
2 12/31/1980
2 12/31/1983
3 12/31/1986
4 12/31/1987
4 12/31/1988
5 04/30/1996
6 12/31/1997 |
I know we can use 'IN' or 'NOT EXIST' keyword, but because of SQL performance issue of the two keywords, some other better choice is wanted. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi Dejunzhu,
OLAP Functions can be used, but  still there would be performance issues, because db2 would have read the data first and then order it.
not tested
| Code: |
select c1,c2
from
(select c1,c2, desnse_rank() over(partition by c1,c2
order by c1) as RANK) AS table
where rank >= 2 |
May be you can try it with more data and let us know which method is best/faster.
Thanks,
Sushanth |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
You might try 2 queiies. One to find the max and the other to select everything less than the max. It might make a big difference if this date is an index. |
|
| Back to top |
|
|
|
|
