Windowed functions can only appear in the SELECT or ORDER BY clauses.
Interesting, I didn't know that. In that case, another level of nesting will be necessary:
SELECT UserId, LoginTime, ComputerName, ComputerLab
FROM (
SELECT *, CASE @sortExpression
WHEN 'userIdAsc' THEN ROW_NUMBER() OVER (ORDER BY UserId ASC)
WHEN 'userIdDesc' THEN ROW_NUMBER() OVER (ORDER BY UserId DESC)
END AS RankNum
FROM (
SELECT
UserId, LoginTime, ComputerName, ComputerLab,
ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginTime DESC) as RowNum
FROM LoginRecords L
WHERE L.ComputerLab = @computerLab
) AS LoginRecordsWithRowNumbers
WHERE RowNum = 1
) AS WithRankNumbers
WHERE RankNum > @startRowIndex
AND RankNum <= (@startRowIndex + @maximumRows)
Also, I became a bit suspicious of the structure of your CASE statements and whether it would really return expected results, so I re-wrote it another way.
That should do it...and PS, I drive an S2k too.
Thanks man, think I might be close. The results seem ok, with one little problem. No matter what @sortExpression I plug in as the input parameter, the sorting stays the same regardless. Btw, nice ride! ;) Mine is a red 03 with almost 190k miles on it,
gonna keep it until the engine falls out.
SELECT UserId, LoginTime, ComputerName, ComputerLab
FROM (
SELECT *, CASE @sortExpression
WHEN 'userIdAsc' THEN ROW_NUMBER() OVER (ORDER BY UserId ASC)
WHEN 'userIdDesc' THEN ROW_NUMBER() OVER (ORDER BY UserId DESC)
END AS RankNum
FROM (
SELECT
UserId, LoginTime, ComputerName, ComputerLab,
ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginTime DESC) as RowNum
FROM LoginRecords L
WHERE L.ComputerLab = @computerLab
) AS LoginRecordsWithRowNumbers
WHERE RowNum = 1
) AS WithRankNumbers
WHERE RankNum > @startRowIndex
AND RankNum <= (@startRowIndex + @maximumRows)
ORDER BY RankNum
Mine's a red '06. And like you, I'm driving it until it dies.
SELECT UserId, LoginTime, ComputerName, ComputerLab
FROM (
SELECT *, CASE @sortExpression
WHEN 'userIdAsc' THEN ROW_NUMBER() OVER (ORDER BY UserId ASC)
WHEN 'userIdDesc' THEN ROW_NUMBER() OVER (ORDER BY UserId DESC)
END AS RankNum
FROM (
SELECT
UserId, LoginTime, ComputerName, ComputerLab,
ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginTime DESC) as RowNum
FROM LoginRecords L
WHERE L.ComputerLab = @computerLab
) AS LoginRecordsWithRowNumbers
WHERE RowNum = 1
) AS WithRankNumbers
WHERE RankNum > @startRowIndex
AND RankNum <= (@startRowIndex + @maximumRows)
ORDER BY RankNum
Mine's a red '06. And like you, I'm driving it until it dies.
Hi Tab, I'll run some more test queries, but this seems to be work! One more thing. In addition to this stored procedure, I also run another one where it basically returns just the COUNT (number of records) of this exact query you just helped me with. I need this stored procedure as well to derive a total row count.
I stripped the sorting mechanism because it's not needed here obviously, and I also got rid of the specific field names in the SELECTs and replaced with the * since I don't care about field values. I tried to execute the below stored procedure, but get an error - "incorrect syntax near the keyword 'WHERE'" and "invalid column name 'RowNum'". If one of them is fixed, I'm sure will probably fix both errors. Do you have any ideas where I'm going wrong? And is my overall query correct to achieve the desired results (I want to return just the record count of the exact query we were working on)? Thanks man. After mine dies, will probably get a new motor in there. :)
ALTER PROCEDURE [dbo].[GetLoginRecordsCount] ( @computerLab varchar(50) ) AS SELECT COUNT(*) FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginTime DESC)asRowNum FROM LoginRecords L WHERE L.ComputerLab=@computerLab ) WHERE RowNum=1 )
Hai use DISTINCT for UserId and get MAX for LoginTime
try
SELECT DISTINCT(UserId), MAX(LoginTime), ComputerName, ComputerLab FROM ( SELECT L.UserId AS UserId, L.LoginTime AS LoginTime, L.ComputerName AS ComputerName, L.ComputerLab AS ComputerLab ROW_NUMBER() OVER (ORDER BY CASE @sortExpression WHEN 'userIdAsc' THEN L.UserId END, CASE @sortExpression WHEN 'userIdDesc' THEN
L.UserId END DESC) AS RowRank FROM LoginRecords L WHERE L.ComputerLab = @computerLab ) AS LoginRecordsWithRowNumbers WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)
ALTER PROCEDURE [dbo].[GetLoginRecordsCount](@computerLab varchar(50)) AS SELECT COUNT(*)
FROM ( SELECT
*, ROW_NUMBER()
OVER (PARTITION BY
UserId ORDER BY
LoginTime DESC)asRowNum FROM
LoginRecords L WHERE L.ComputerLab=@computerLab) WHERE
RowNum=1)
You need to give your derived table (the nested select) an alias. Even though you don't refer to it, it's a syntax error not to include one.
By the way, I think you could also get the desired results with this:
SELECT COUNT(DISTINCT UserId) FROM LoginRecords WHERE ComputerLab=@computerLab
Thanks again Tab for your help in this thread... regarding your last post, adding the aliases worked so I went with that method. The alternative method you mentioned using DISTINCT was the first thing that came to mind as well but I wanted the original
query and the corresponding count query to return identical number of records, every single time (it needs to, for the custom paging functionality to work properly), so I wanted the queries to be written the same way.
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: How to "DISTINCT" on a column yet return all column values ?
Jan 21, 2013 07:40 PM|LINK
If this were my problem, I'd throw away the existing query and start from scratch. My query would resemble this:
select UserID, MaxLoginTime, ComputerName, ComputerLab
from loginrecords r join
(
select UserId id, max(logintime) MaxLoginTime
from loginrecords
where ComputerLab = @computerLab
group by userid
) sq on userid = id and logintime =MaxLoginTime
where ComputerLab = @computerLab
S2kDriver
Member
23 Points
156 Posts
Re: How to "DISTINCT" on a column yet return all column values ?
Jan 22, 2013 06:11 AM|LINK
Thanks man, think I might be close. The results seem ok, with one little problem. No matter what @sortExpression I plug in as the input parameter, the sorting stays the same regardless. Btw, nice ride! ;) Mine is a red 03 with almost 190k miles on it, gonna keep it until the engine falls out.
S2kDriver
Member
23 Points
156 Posts
Re: How to "DISTINCT" on a column yet return all column values ?
Jan 22, 2013 06:12 AM|LINK
Thanks for your reply. Think I might be close with the way I have it now, if I'm stuck, I will give your idea a shot.
TabAlleman
All-Star
15557 Points
2698 Posts
Re: How to "DISTINCT" on a column yet return all column values ?
Jan 22, 2013 01:21 PM|LINK
Oh! duh, try this... : )
SELECT UserId, LoginTime, ComputerName, ComputerLab FROM ( SELECT *, CASE @sortExpression WHEN 'userIdAsc' THEN ROW_NUMBER() OVER (ORDER BY UserId ASC) WHEN 'userIdDesc' THEN ROW_NUMBER() OVER (ORDER BY UserId DESC) END AS RankNum FROM ( SELECT UserId, LoginTime, ComputerName, ComputerLab, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY LoginTime DESC) as RowNum FROM LoginRecords L WHERE L.ComputerLab = @computerLab ) AS LoginRecordsWithRowNumbers WHERE RowNum = 1 ) AS WithRankNumbers WHERE RankNum > @startRowIndex AND RankNum <= (@startRowIndex + @maximumRows) ORDER BY RankNumMine's a red '06. And like you, I'm driving it until it dies.
S2kDriver
Member
23 Points
156 Posts
Re: How to "DISTINCT" on a column yet return all column values ?
Jan 22, 2013 04:46 PM|LINK
Hi Tab, I'll run some more test queries, but this seems to be work! One more thing. In addition to this stored procedure, I also run another one where it basically returns just the COUNT (number of records) of this exact query you just helped me with. I need this stored procedure as well to derive a total row count.
I stripped the sorting mechanism because it's not needed here obviously, and I also got rid of the specific field names in the SELECTs and replaced with the * since I don't care about field values. I tried to execute the below stored procedure, but get an error - "incorrect syntax near the keyword 'WHERE'" and "invalid column name 'RowNum'". If one of them is fixed, I'm sure will probably fix both errors. Do you have any ideas where I'm going wrong? And is my overall query correct to achieve the desired results (I want to return just the record count of the exact query we were working on)? Thanks man. After mine dies, will probably get a new motor in there. :)
rsrvs214
Member
208 Points
81 Posts
Re: How to "DISTINCT" on a column yet return all column values ?
Jan 22, 2013 06:02 PM|LINK
Hai use DISTINCT for UserId and get MAX for LoginTime
try
SELECT DISTINCT(UserId), MAX(LoginTime), ComputerName, ComputerLab
FROM
(
SELECT L.UserId AS UserId, L.LoginTime AS LoginTime, L.ComputerName AS ComputerName, L.ComputerLab AS ComputerLab ROW_NUMBER() OVER (ORDER BY CASE @sortExpression WHEN 'userIdAsc' THEN L.UserId END, CASE @sortExpression WHEN 'userIdDesc' THEN L.UserId END DESC) AS RowRank
FROM LoginRecords L
WHERE L.ComputerLab = @computerLab
)
AS LoginRecordsWithRowNumbers
WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)
TabAlleman
All-Star
15557 Points
2698 Posts
Re: How to "DISTINCT" on a column yet return all column values ?
Jan 22, 2013 06:25 PM|LINK
You need to give your derived table (the nested select) an alias. Even though you don't refer to it, it's a syntax error not to include one.
By the way, I think you could also get the desired results with this:
SELECT COUNT(DISTINCT UserId) FROM LoginRecords WHERE ComputerLab=@computerLab
S2kDriver
Member
23 Points
156 Posts
Re: How to "DISTINCT" on a column yet return all column values ?
Jan 28, 2013 05:33 PM|LINK
Thanks again Tab for your help in this thread... regarding your last post, adding the aliases worked so I went with that method. The alternative method you mentioned using DISTINCT was the first thing that came to mind as well but I wanted the original query and the corresponding count query to return identical number of records, every single time (it needs to, for the custom paging functionality to work properly), so I wanted the queries to be written the same way.