Duplicates - works fine without group

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)

Duplicates - works fine without group

Author

Topic

tech
Starting Member

32 Posts

Posted - 2011-03-29 : 17:08:06

I'm trying to get this query to work but not quite working. I want the results the same as my last working query but problem is, I am doing pagination at the SQL End and prefer to keep it this way.

Not working query:

quote:

DECLARE @firstRow int
DECLARE @lastRow int
DECLARE @itemsPerPage int
SET @itemsPerPage = 1000
DECLARE @totalRecords int
DECLARE @pageNumber int
SET @pageNumber = 1

SELECT @firstRow = (@pageNumber - 1) * @itemsPerPage + 1,
@lastRow = (@pageNumber - 1) * @itemsPerPage + @itemsPerPage,
@totalRecords = (SELECT COUNT(DISTINCT(c.[CompanyID]))
FROM Company c
LEFT OUTER JOIN Users u ON
u.CompanyID = c.CompanyID
LEFT OUTER JOIN UserAttributes ua ON
ua.UserID = u.UserID
);

WITH ManyItems AS (
SELECT c.CompanyID, c.CompanyName, ISNULL(SUM(ua.TotalPoints), 0) AS CompanyPoints,
ROW_NUMBER() OVER (ORDER BY ua.TotalPoints) AS RowNumber
FROM Company c
LEFT OUTER JOIN Users u ON
u.CompanyID = c.CompanyID
LEFT OUTER JOIN UserAttributes ua ON
ua.UserID = u.UserID
GROUP BY c.CompanyID, c.CompanyName, ua.TotalPoints
)

SELECT CompanyID, CompanyName, CompanyPoints
FROM ManyItems
WHERE RowNumber BETWEEN @firstRow AND @lastRow
ORDER BY CompanyPoints DESC

Working Query

quote:

DECLARE @firstRow int
DECLARE @lastRow int
DECLARE @itemsPerPage int
SET @itemsPerPage = 1000
DECLARE @totalRecords int
DECLARE @pageNumber int
SET @pageNumber = 1

SELECT @firstRow = (@pageNumber - 1) * @itemsPerPage + 1,
@lastRow = (@pageNumber - 1) * @itemsPerPage + @itemsPerPage,
@totalRecords = (SELECT COUNT(DISTINCT(c.[CompanyID]))
FROM Company c
LEFT OUTER JOIN Users u ON
u.CompanyID = c.CompanyID
LEFT OUTER JOIN UserAttributes ua ON
ua.UserID = u.UserID
);

WITH ManyItems AS (
SELECT c.CompanyID, c.CompanyName, ISNULL(SUM(ua.TotalPoints), 0) AS CompanyPoints,
ROW_NUMBER() OVER (ORDER BY c.companyID) AS RowNumber
FROM Company c
LEFT OUTER JOIN Users u ON
u.CompanyID = c.CompanyID
LEFT OUTER JOIN UserAttributes ua ON
ua.UserID = u.UserID
GROUP BY c.CompanyID, c.CompanyName--, ua.TotalPoints
)

SELECT CompanyID, CompanyName, CompanyPoints
FROM ManyItems
WHERE RowNumber BETWEEN @firstRow AND @lastRow
ORDER BY CompanyPoints DESC

of course, problem is that the Total Points are not in DESC order... regardless of what page you are on. they do show it as decending on one page (or A page)...but not overall.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-30 : 02:49:24

what do you want to achieve with the 1st query ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources