Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
To filter all the rows with most recent 5 dates

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Kumar Ashok

New User


Joined: 20 Jan 2007
Posts: 29
Location: KOLKATA

PostPosted: Fri Aug 24, 2007 11:57 am    Post subject: To filter all the rows with most recent 5 dates
Reply with quote

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
Back to top
View user's profile Send private message

Balraj

New User


Joined: 16 Aug 2007
Posts: 34
Location: Bangalore

PostPosted: Fri Aug 24, 2007 12:46 pm    Post subject:
Reply with quote

hi

Use Group By Clause and Order By clause
Back to top
View user's profile Send private message
Kumar Ashok

New User


Joined: 20 Jan 2007
Posts: 29
Location: KOLKATA

PostPosted: Fri Aug 24, 2007 2:31 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Aug 24, 2007 3:08 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Kumar Ashok

New User


Joined: 20 Jan 2007
Posts: 29
Location: KOLKATA

PostPosted: Fri Aug 24, 2007 3:18 pm    Post subject:
Reply with quote

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. icon_sad.gif
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Aug 24, 2007 3:32 pm    Post subject:
Reply with quote

What is your query? Post it and we may be able to help.
Back to top
View user's profile Send private message
Alan Voss

New User


Joined: 29 Nov 2006
Posts: 32
Location: Jacksonville, FL

PostPosted: Fri Aug 24, 2007 9:54 pm    Post subject:
Reply with quote

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

select A_ranked.*
  from (select date,
                    i,
                    x ,
                   dense_rank() over (order by date desc) as rank_date
             from abc) as A_ranked
  where A_ranked.rank_date <= 5
  order by A_ranked.rank_date
;


    DATE I X RANK_DATE
    ---------- ------ ------ --------------------
    2007-08-20 1 3 1
    2007-08-20 2 3 1
    2007-08-09 1 3 2
    2007-08-09 2 3 2
    2007-08-09 3 6 2
    2007-08-09 4 3 2
    2007-08-09 5 4 2
    2007-08-09 6 3 2
    2007-07-29 7 0 3
    2007-07-29 8 3 3
    2007-07-29 9 1 3
    2007-07-29 1 4 3
    2007-07-29 2 3 3
    2007-07-29 3 3 3
    2007-07-29 4 2 3
    2007-07-29 5 6 3
    2007-07-29 6 0 3
    2007-07-18 4 7 4
    2007-07-18 5 0 4
    2007-07-18 6 5 4
    2007-07-18 7 9 4
    2007-07-18 1 3 4
    2007-07-18 2 8 4
    2007-07-18 3 3 4
    2007-07-07 1 8 5
    2007-07-07 2 0 5
    2007-07-07 3 2 5
    2007-07-07 4 3 5
    2007-07-07 5 1 5
    2007-07-07 6 1 5
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Aug 24, 2007 10:27 pm    Post subject:
Reply with quote

Alan,

again, excellent SQL.
Back to top
View user's profile Send private message
Alan Voss

New User


Joined: 29 Nov 2006
Posts: 32
Location: Jacksonville, FL

PostPosted: Fri Aug 24, 2007 11:07 pm    Post subject:
Reply with quote

Thank you. I just dug around in the manual.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Aug 24, 2007 11:28 pm    Post subject:
Reply with quote

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!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts compare 2 rows within the same file ram_vizag SYNCSORT 7 Wed Jun 14, 2017 12:34 am
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us