Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Get 1st N rows of every customer

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

New User


Joined: 15 Mar 2007
Posts: 13
Location: Pune

PostPosted: Wed Aug 24, 2011 11:52 am    Post subject: Get 1st N rows of every customer
Reply with quote

Hi,

I need one help

I am having table in following way(I have illustrated only 4 colms)

Code:
Customer No      Name          Date                   Code
111                     XYZ           05/Aug/2011          A
111                     XYZ           06/Sep/2011          B 
111                     XYZ           06/Jul/2011            C   
123                     XYW          05/Aug/2011          A
123                     XYW           06/Sep/2011          B 
123                     XYW           06/Jul/2011           C
333                     XYA           05/Aug/2011         A
333                     XYA           06/Sep/2011       B 
333                     XYA           06/Jul/2011        C
333                     XYA           06/Jun/2011       D

I am trying to quote the query to select all the data of table for N rows (e.g first 2 rows) for every customer based on descending date

e.g. If the value of N is 2, then I should get the o/p as below (latest 2 rows on desc date for that customer)

Code:
111                  XYZ        05/Aug/2011       A
111                  XYZ        06/Sep/2011       B     
123                  XYZ        05/Aug/2011       A
123                  XYZ        06/Sep/2011       B
333                  XYZ        05/Aug/2011       A
333                  XYZ        06/Sep/2011       B 


Please note neither Customer number is primary key, nor the Date.. Table is having composite key on Customer number and Date

Thanks
SRW
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


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

PostPosted: Wed Aug 24, 2011 12:09 pm    Post subject:
Reply with quote

what level of db2 are you using?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Aug 24, 2011 12:34 pm    Post subject:
Reply with quote

Hello,

Quote:
latest 2 rows on desc date for that customer
The sample output does not appear to be in descending date order icon_confused.gif
Back to top
View user's profile Send private message
sarangwagh7

New User


Joined: 15 Mar 2007
Posts: 13
Location: Pune

PostPosted: Wed Aug 24, 2011 12:58 pm    Post subject: Reply to: Get 1st N rows of every customer
Reply with quote

Sorry dick

the o/p should be
Code:
111 XYZ 06/Sep/2011 B
111 XYZ 05/Aug/2011 A
123 XYZ 06/Sep/2011 B
123 XYZ 05/Aug/2011 A
333 XYZ 06/Sep/2011 B
333 XYZ 05/Aug/2011 A


basically I need generic query which will fetch first N number of rows for all customers
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Aug 24, 2011 1:09 pm    Post subject:
Reply with quote

Now I should ask :
what have you tried ? how far did you get towards the result ?
do you know how to get the highest date for each customer ?
and of course dbzs question : DB2 v8 or higher ?
Back to top
View user's profile Send private message
rdr

New User


Joined: 26 May 2006
Posts: 35
Location: india

PostPosted: Tue Sep 27, 2011 12:11 pm    Post subject:
Reply with quote

It may be something like this--(not sure icon_sad.gif )

Select * from EMP where 2 = (Select count(*) from emp
where date1 <= date 2
Back to top
View user's profile Send private message
Eshwar CICS

New User


Joined: 18 May 2011
Posts: 47
Location: India

PostPosted: Tue Sep 27, 2011 7:11 pm    Post subject: Reply to: Get 1st N rows of every customer
Reply with quote

Explore Query mentioned in
"Figure 168, Select first "n" rows, or more if needed"
of "DB2 UDB V8.1 SQL Cookbook"

and try to complete your task.
Back to top
View user's profile Send private message
vadim vashchenko

New User


Joined: 21 Mar 2011
Posts: 13
Location: usa

PostPosted: Tue Sep 27, 2011 8:22 pm    Post subject:
Reply with quote

Code:

SELECT *
  FROM MYTABLE B     
 WHERE B.CUST_ID IN 
(SELECT A.CUST_ID   
   FROM MYTABLE A   
  GROUP BY A.CUST_ID
 HAVING COUNT(*) = 2)


The code above is definitely not an answer. I am trying to say that FETCH FIRST N ROWS is not relevant either... [/code]
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Sep 27, 2011 10:23 pm    Post subject:
Reply with quote

Sarang,
You can try below query.

Code:
select A.* from MYTABLE A
where
(select count(*) from MYTABLE B where
B.CUST_ID = A.CUST_ID and B.Date >= A.Date) <= 2;
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Sep 28, 2011 12:50 pm    Post subject:
Reply with quote

vadim vashchenko wrote:
I am trying to say that FETCH FIRST N ROWS is not relevant either... [/code]

This is how one should solve it with "fetch first N rows only".
I'm pretty sure this solution is faster than previous.
and also will return only 2 rows per customer even if duplicate dates exists.

Code:
select c.*
from       (select cust_id from MYTABLE B group by b.cust_id) A
   , table (select * from MYTABLE B where b.cust_id = a.cust_id order by date desc fetch first 2 rows only) C
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Sep 28, 2011 10:17 pm    Post subject:
Reply with quote

sarangwagh,

You can try something like this,
Code:
select * from
(
select customerno, name, date, code,
       row_number() over(partition by customerno, name
          order by customerno, name, code) as rowno
from custcode
) a
where rowno <= 2

Query is not tested, hope it works.

Thanks,
sushanth
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Sep 29, 2011 2:20 pm    Post subject:
Reply with quote

sushanth bobby wrote:
Code:
select * from
(
select customerno, name, date, code,
       row_number() over(partition by customerno, name
          order by customerno, name, code) as rowno
from custcode
) a
where rowno <= 2


Shouldn't there be an order by date desc somwhere?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Sep 29, 2011 3:27 pm    Post subject:
Reply with quote

GuyC,

In the above example, the date month is like Aug, Sept, Jul. So, i thought date doesn't matter.

Regards,
Sushanth
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Sep 29, 2011 4:40 pm    Post subject: Re: Get 1st N rows of every customer
Reply with quote

sarangwagh7 wrote:
I am trying to quote the query to select all the data of table for N rows (e.g first 2 rows) for every customer based on descending date
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Sep 30, 2011 11:20 am    Post subject:
Reply with quote

You are right, GuyC, I missed that.

Thanks,
Sushanth
Back to top
View user's profile Send private message
chandan.inst

Active User


Joined: 03 Nov 2005
Posts: 269
Location: Mumbai

PostPosted: Tue Oct 04, 2011 1:05 pm    Post subject:
Reply with quote

Hi,

As Customer number and Date is the composite key for table then assuming that there will no duplicate date for a Customer, you can try something like this also but it depends on which DB2 version you are using

For me its DB2V9

Code:
Select  A.Customerno
       ,A.Name
       ,A.Date
       ,A.Code
From TBL_A  A
Where (A.Customerno,A.Date )
     IN
      (Select  B.Customerno
              ,B.Date
          From TBL_A  B
       WHERE   A.Customerno = B.Customerno
        ORDER BY B.Date  DESC
      FETCH FIRST 2 ROWS ONLY)


Kindly let me know if I am missing anything here

Regards,
Chandan
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts Can we use OPTIMIZE FOR 1 ROWS for fe... cvijay784 DB2 1 Fri Aug 05, 2016 11:56 am
No new posts Column into multiple rows V S Amarendra Reddy SYNCSORT 2 Thu Mar 03, 2016 8:22 pm
No new posts Get rows based on amount entered from... Rohit Umarjikar DB2 10 Thu Feb 25, 2016 9:57 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us