Selecting a record only once with the paging

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

Selecting a record only once with the paging

Author

Topic

shakthiA
Starting Member

12 Posts

Posted - 2009-11-05 : 16:35:58

Hi

I am trying to get the members who published into my subsite. I have mainly 3 tables where I store my site members in JMembers and published content in PubShelf and JPubs

PubShelf has following cols:

Pub_Id Contributor Type
1234 aaaa DOC
2345 aaaa PDF
3554 cccc TXT
3454 aaaa DOC
1234 bbbb PPT

The JPubs table will look like:

Jid PubId

2dd605 1234
2dd605 3454
2dd605 3554
678605 4564
456605 1234

The JMembers looks like

Jid Username RoleName Activated Invited ..
2dd605 aaaa reader True
2dd605 cccc reader True
2dd605 bbbb owner True
2dd605 dddd reader True
456605 cccc reader True
456605 aaaa owner True

I am trying to get the following result:

Jid Username Role
2dd605 aaaa reader
2dd605 cccc reader
2dd605 bbbb reader

I tried with the follwoing code but I want to do paging as well which is not working for paging:

SELECT * FROM
(
SELECT DISTINCT Row_Number() OVER (PARTITION BY Username ORDER BY Username asc) AS Row,
JM.Jid, Username, Role
FROM JMembers JM
INNER JOIN JPubs JP ON JM.Jid = JP.Jid
INNER JOIN PubShelf S ON JP.PubId = S.Pub_Id
WHERE JM.Jid = '2dd605'
)J
WHERE
Row BETWEEN 1 AND 10

I am getting the below result:

Row Jid Username Role
1 2dd605 aaaa reader
1 2dd605 cccc reader
1 2dd605 bbbb reader
2 2dd605 aaaa reader
2 2dd605 cccc reader
2 2dd605 bbbb reader

I want each username only once to be selected and the paging canot be done with this query. Could any one please help me.

Thanks.

sql-programmers
Posting Yak Master

190 Posts

Posted - 2009-11-13 : 12:50:12

This is something that I have run into before. Try this query:

SELECT * FROM
(
SELECT DISTINCT Row_Number() OVER (ORDER BY Username asc) AS Row,
JM.Jid, Username, Role
FROM JMembers JM
INNER JOIN JPubs JP ON JM.Jid = JP.Jid
INNER JOIN PubShelf S ON JP.PubId = S.Pub_Id
WHERE JM.Jid = '2dd605'
)J
WHERE
Row BETWEEN 1 AND 10

The full details of how the row_number function works can be found in the article below.
http://www.sql-programmers.com/SQLResources/Queries/RowNumberFunction.aspx

SQL Server Programmers and Consultants
http://www.sql-programmers.com/