IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Get 1st N rows of every customer


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
Reply with quote

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

Moderator Emeritus


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

PostPosted: Wed Aug 24, 2011 12:34 pm
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
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: 1281
Location: Belgium

PostPosted: Wed Aug 24, 2011 1:09 pm
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
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
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
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
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: 1281
Location: Belgium

PostPosted: Wed Sep 28, 2011 12:50 pm
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: 1020
Location: India

PostPosted: Wed Sep 28, 2011 10:17 pm
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: 1281
Location: Belgium

PostPosted: Thu Sep 29, 2011 2:20 pm
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: 1020
Location: India

PostPosted: Thu Sep 29, 2011 3:27 pm
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: 1281
Location: Belgium

PostPosted: Thu Sep 29, 2011 4:40 pm
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: 1020
Location: India

PostPosted: Fri Sep 30, 2011 11:20 am
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: 275
Location: Mumbai

PostPosted: Tue Oct 04, 2011 1:05 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Reflection 3270 screen shows only 23 ... Compuware & Other Tools 6
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
No new posts Convert single row multi cols to sing... DFSORT/ICETOOL 6
No new posts Compare latest 2 rows of a table usin... DB2 1
Search our Forums:

Back to Top