sql help

[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

[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.

[prasannakumarkk]

In which DB you are trying to acheive this. or you are trying to acheive in Datastage

[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

[adams06]

Oracle sql

[chulett]

As noted, you don't need to do this in SQL... why not just implement the logic in job as Ray suggested?

[priyadarshikunal]

You can do both way either as Ray suggested or the Query provided by Chandra, It will work for oracle as well.