Page 1 of 1
sql help
Posted: Thu Apr 04, 2013 2:50 pm
by adams06
select count(*),BookNbr
from tablename
where BookNbr like '0%'
group by BookNbr asc
;
0012
0013
0014
0015
0112
0113
0114
0115
0212
0213
0214
0215
How can i get the top 3 records from each group .depending on first 2 positions of BookNbr
Thanks in advance
Posted: Thu Apr 04, 2013 7:06 pm
by ray.wurlod
Generate a counter within each group and pass only those rows for which the counter value is less than or equal to 3.
Posted: Thu Apr 04, 2013 11:43 pm
by prasannakumarkk
In which DB you are trying to acheive this. or you are trying to acheive in Datastage
Posted: Thu Apr 04, 2013 11:56 pm
by chandra.shekhar@tcs.com
If Its DB2 then below query should work
Code: Select all
SELECT
BOOKNBR
FROM
(
SELECT
BOOKNBR,
RANK() OVER (PARTITION BY BOOKNBR ORDER BY BOOKNBR) RNK
FROM
TABLENAME
WHERE
BOOKNBR LIKE '0%')
WHERE
RNK <=3
Posted: Fri Apr 05, 2013 7:41 am
by adams06
Oracle sql
Posted: Fri Apr 05, 2013 7:50 am
by chulett
As noted, you don't need to do this in SQL... why not just implement the logic in job as Ray suggested?
Posted: Mon Apr 08, 2013 9:49 am
by priyadarshikunal
You can do both way either as Ray suggested or the Query provided by Chandra, It will work for oracle as well.