To filter all the rows with most recent 5 dates

Kumar Ashok · New User Joined: 20 Jan 2007 Posts: 29 Location: KOLKATA

Hi All,

Let us consider I have 10 columns in a DB2 table. say COL1, COL2....COL10.
now let us say COL4 is a date field and it has 20 different values. And let us say there are 1000 rows in the table. Now the requirement is i want all the rows with most recent 5 dates. How will I able to do that.
Some condition like COL4 >= 'Some value' will not work as i don't know the most recent dates.
Please Help.

Thanks in advance

Balraj · New User Joined: 16 Aug 2007 Posts: 34 Location: Bangalore

hi

Use Group By Clause and Order By clause

Kumar Ashok · New User Joined: 20 Jan 2007 Posts: 29 Location: KOLKATA

By that we can have at the max sorted records. But the requirement is to display only the records that are related to most recent five dates.

Something like that we do through

FETCH FIRST 100 RECORD only

Means output should be like

FETCH RECORDS OF MOST RECENT 5 DATES ONLY

dbzTHEdinosauer · Posted: Fri Aug 24, 2007 3:08 pm

Kumar Ashok,

if you know the answer, why ask?

Balraj gave you the solution

GROUP BY
ORDER BY

and to only return 5 rows,

OPTIMIZE FOR 5 ROWS
FETCH FIRST 5 ROWS

suggest that you read about the GROUP BY statement.

there are other ways to do it, e.g. using sub select.

Kumar Ashok · New User Joined: 20 Jan 2007 Posts: 29 Location: KOLKATA

Seems I am not clear with my requiremnet. Let me elaborate.

COL1-COL2-COL3- COL4 -COL5-COL6-COL7-COL8-COL9-COL10
1 - 1 - 1 - 08242007
2 - 1 - 1 - 08232007
3 - 1 - 1 - 08222007
4 - 1 - 1 - 08212007
5 - 1 - 1 - 08202007
6 - 1 - 1 - 08202007
7 - 1 - 1 - 08192007
8 - 1 - 1 - 08192007
9 - 1 - 1 - 08192007
10 - 1 - 1 - 08192007
11 - 1 - 1 - 08192007
12 - 1 - 1 - 08192007
13 - 1 - 1 - 08192007
14 - 1 - 1 - 08192007
15 - 1 - 1 - 08242007

Then after executing the query the output will be
15 - 1 - 1 - 08242007
1 - 1 - 1 - 08242007
2 - 1 - 1 - 08232007
3 - 1 - 1 - 08222007
4 - 1 - 1 - 08212007
5 - 1 - 1 - 08202007
6 - 1 - 1 - 08202007

Could you please help me wih a sample query..Sorry to bother you guys but I am not getting you all.

dbzTHEdinosauer · Posted: Fri Aug 24, 2007 3:32 pm

What is your query? Post it and we may be able to help.

Alan Voss · Posted: Fri Aug 24, 2007 9:54 pm

The following works on UDB 8.1 (maybe not on mainframe):

dbzTHEdinosauer · Posted: Fri Aug 24, 2007 10:27 pm

Alan,

again, excellent SQL.

Alan Voss · Posted: Fri Aug 24, 2007 11:07 pm

Thank you. I just dug around in the manual.

dbzTHEdinosauer · Posted: Fri Aug 24, 2007 11:28 pm

Alan,

Trying to get the DISTINCT TOP 5 when I should have been trying to
get the TOP 5 'Groups', and that requires a RANKing methodology.

I was just reading and practicing RANKing and did not even attempt to employ it here, because, I misread the requirement.

hav'a good weekend!