how to get subrecords one by one using sql

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

SQL Server 2005 Forums

Transact-SQL (2005)

how to get subrecords one by one using sql

Author

Topic

udaymahajan
Starting Member

17 Posts

Posted - 2008-10-24 : 08:09:56

my sql query as follows

declare @AdGroupID int
declare @LastRowNo int

set @AdGroupID=2
set @LastRowNo=0

SELECT ROW_NUMBER() OVER(ORDER BY [Advertisement].AdID) AS ROW,
[Advertisement].AdID,AdSectionID,AdName,AdType
FROM [Advertisement] LEFT JOIN [AdGroupDetails] ON [Advertisement].AdID = [AdGroupDetails].AdID
where [AdGroupDetails].AdGroupID = @AdGroupID

when i run records i getting records as follows
ROW AdID AdSectionID AdName AdType

1 6 1 food G
2 13 1 Jobs F
3 14 1 Educational F

now i want records as follows for ex
when i pass two parameters to above query as
@AdGroupID and @LastRowNo
having values @AdGroupID=2 and @LastRowNo=0
then i want display only first records like
ROW AdID AdSectionID AdName AdType

1 6 1 food G

also next when i pass two parameters to this query as
@AdGroupID and @LastRowNo
having values @AdGroupID=2 and @LastRowNo=1
then i want display only first records like

ROW AdID AdSectionID AdName AdType

2 13 1 Jobs F

also when when i pass two parameters to this query as
@AdGroupID and @LastRowNo
having values @AdGroupID=2 and @LastRowNo=2

then i want display only first records like

ROW AdID AdSectionID AdName AdType

3 14 1 Educational F

ie i want to show subrecords one by one as per requirement

so how can i design sql query to show records

uday

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-24 : 09:12:07

Put select into derived table then use normal WHERE statement to return the row you want

declare @AdGroupID int
declare @LastRowNo int

set @AdGroupID=2
set @LastRowNo=0

select *
from(
SELECT ROW_NUMBER() OVER(ORDER BY [Advertisement].AdID) AS ROW,
[Advertisement].AdID,AdSectionID,AdName,AdType
FROM [Advertisement] LEFT JOIN [AdGroupDetails] ON [Advertisement].AdID = [AdGroupDetails].AdID
where [AdGroupDetails].AdGroupID = @AdGroupID
) as a

where Row = @LastRowNo + 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources