|
View previous topic :: View next topic
|
| Author |
Message |
sarangwagh7
New User
Joined: 15 Mar 2007
Posts: 13
Location: Pune
|
|
|
|
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 |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| what level of db2 are you using? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| latest 2 rows on desc date for that customer |
The sample output does not appear to be in descending date order  |
|
| Back to top |
|
|
sarangwagh7
New User
Joined: 15 Mar 2007
Posts: 13
Location: Pune
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
rdr
New User
Joined: 26 May 2006
Posts: 35
Location: india
|
|
|
|
It may be something like this--(not sure  )
Select * from EMP where 2 = (Select count(*) from emp
where date1 <= date 2 |
|
| Back to top |
|
|
Eshwar CICS
New User
Joined: 18 May 2011
Posts: 47
Location: India
|
|
|
|
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 |
|
|
vadim vashchenko
New User
Joined: 21 Mar 2011
Posts: 13
Location: usa
|
|
|
|
| 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 |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| 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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
You are right, GuyC, I missed that.
Thanks,
Sushanth |
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
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 |
|
|
|
|
