How to reterive the rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to reterive the rows

Author

Topic

stpn
Starting Member

8 Posts

Posted - 2014-03-26 : 06:50:57

Hi,

I have 2 tables "PRODUCTS" and "CATEGORIES".
Table: Products
ProID ProName
1 A
2 B
3 C
4 D
5 E

Table : Categories
ProID CatID CatName
1 1 AA
1 2 BB
1 3 CC
1 4 DD
2 3 EE
2 4 FF
3 1 GG
4 1 HH
4 3 II
4 4 JJ
5 1 KK
5 2 LL

I want to write a query, which returns only the starting 2 categories, when productID is Passed as a parameter.

If ProID=1
Output Example
ProID CatID CatName
1 1 AA
1 2 BB
If ProID=4
Output Example
ProID CatID CatName
4 1 HH
4 3 II

I tired with the below query, but i know it gives only the details of CatID 1 and 2,
Where as all the products does not have CatID 1 and 2 so it will not work out for me.

Create Proc SP_CatDetails
@ProID int
as
Begin
Select P.ProID,C.CatID,C.CatName from Products P
INNER JOIN Categories C
ON P.ProID=C.ProID
Where P.ProID=@ProID
and C.CatID Between 1 and 2
order by C.CatID
End

Thanks in advance for your assistance

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2014-03-26 : 07:57:58

try this:

Select
P.ProID,
dt.CatID,
dt.CatName
from Products P
INNER JOIN
(select row_number() over (partion by ProID oder by CatID) as rn, * from Categories) as dt
ON P.ProID=dt.ProID and dt.rn < 3
Where P.ProID=@ProID

Too old to Rock'n'Roll too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-03-28 : 05:05:39

Also refer http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources