Query

[oravijay]

My Tables has values like this.

CateID Name SubID
1 123 1
1 234 2
1 435 3
1 435 4
1 435 5
2 123 1
2 123 2
2 123 3
2 123 4
2 123 5
3 123 1
3 123 2
3 123 3
3 123 4

I want a query such that i want to select only 2 rows in each catid.
ie)My O/P should be expected like this.
CateID Name SubID
1 123 1
1 234 2
2 123 1
2 123 2
3 123 1
3 123 2

Is it possible by a Query.

[TimHall]

SELECT CateID, Name, SubID
FROM my_table
WHERE SubID <= 2
ORDER BY CateID, Name, SubID;

Is this what you mean?

Cheers

[denevge]

assume the name of your table is GDN.


SQL> select * from gdn;

CATID NAME SUBID
---------- -------------------- ----------
1 123 1
1 234 2
1 435 3
1 435 4
1 435 5
2 123 1
2 123 2
2 123 3
2 123 4
2 123 5
3 123 1

CATID NAME SUBID
---------- -------------------- ----------
3 123 2
3 123 3
3 123 4

14 rows selected.

SQL> get xx
1 select * from (
2 select catid,name,subid,rank() over ( partition by catid order by name,subid ) r
3 from gdn
4 ) tab
5* where tab.r < 3
SQL> /

CATID NAME SUBID R
---------- -------------------- ---------- ----------
1 123 1 1
1 234 2 2
2 123 1 1
2 123 2 2
3 123 1 1
3 123 2 2

6 rows selected.


Hope this helps
Gert

[oravijay]

Thanks mate.