Joins, Max, Over combination - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Joins, Max, Over combination

Author

Topic

cwolcott
Starting Member

4 Posts

Posted - 2012-04-23 : 12:08:26

There is more to this then what I am discussing here , but I will try to simplify it first.

The reason for the row_number is so I can perform SQL Paging. I had a query working just fine until someone asked to add one more column with a join. The original query is:


select ROW_NUMBER() OVER (ORDER BY ru.userid) AS Row,
       ru.userid, ru.LastNm, ru.firstnm
from RegisteredUser as ru
where IsActive = 1
order by ru.userid

I now want to join it with my FileDownloadLog table to show the last time a user downloaded a file.


select ROW_NUMBER() OVER (ORDER BY ru.userid) AS Row,
ru.userid, ru.LastNm, ru.firstnm,
MAX(fdl.downloaddate) as LastDownloadDate
from RegisteredUser as ru
left join FileDownLoadLog as fdl on ru.UserID = fdl.UserID
where IsActive = 1
group by ru.userid, ru.LastNm, ru.firstnm
order by ru.userid

RESULTS:


Row     Userid  LastNm  Firstnm LastDownloadDate
1       14      Smith   Bob     2008-05-14 09:29:00
2       20      Jones	Dave    NULL
3       39      Zzz     Aaa     2009-09-01 15:28:00

ISSUE:
Is there an easy way to sort on the LastDownloadDate column?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 12:11:25

yep..just replace ru.userid with LastDownloadDate in the ORDER BY of your select

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cwolcott
Starting Member

4 Posts

Posted - 2012-04-23 : 12:21:21

Agreed, but I did leave something out. The row number column as needs to be increasing so that the SQL Paging will continue to work. If I just change the Order by clause I get:

Results:


Row     Userid  LastNm  Firstnm LastDownloadDate
311     2289    Cade    Lee     2009-09-21 10:22:00
19      138     Abbot   Mark    2009-09-21 09:10:00
1451    4278    Mark    George  2009-09-21 08:39:00

But I need the result to be:


Row     Userid  LastNm  Firstnm LastDownloadDate
1       2289    Cade    Lee     2009-09-21 10:22:00
2       138     Abbot   Mark    2009-09-21 09:10:00
3       4278    Mark    George  2009-09-21 08:39:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 12:24:33

[code]
select ROW_NUMBER() OVER (ORDER BY COALESCE(LastDownloadDate,'19000101') DESC ) AS Row,
ru.userid, ru.LastNm, ru.firstnm,
MAX(fdl.downloaddate) as LastDownloadDate
from RegisteredUser as ru
left join FileDownLoadLog as fdl on ru.UserID = fdl.UserID
where IsActive = 1
group by ru.userid, ru.LastNm, ru.firstnm
order by Row[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cwolcott
Starting Member

4 Posts

Posted - 2012-04-23 : 18:25:15

I was so hopeful, but I get the following error:


Msg 207, Level 16, State 1, Line 1
Invalid column name 'LastDownloadDate'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'LastDownloadDate'.

This is the query:


select ROW_NUMBER() OVER (ORDER BY COALESCE(LastDownloadDate,'19000101') DESC, ru.userid ) AS Row,
	   ru.userid, ru.LastNm, ru.firstnm,
	   MAX(fdl.downloaddate) as LastDownloadDate
from RegisteredUser as ru
left join FileDownLoadLog as fdl on ru.UserID = fdl.UserID
where IsActive = 1
group by ru.userid, ru.LastNm, ru.firstnm
order by row

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 18:58:44

you cant forward reference aliases like this. it should be


select ROW_NUMBER() OVER (ORDER BY COALESCE(MAX(fdl.downloaddate),'19000101') DESC, ru.userid ) AS Row,
	   ru.userid, ru.LastNm, ru.firstnm,
	   MAX(fdl.downloaddate) as LastDownloadDate
from RegisteredUser as ru
left join FileDownLoadLog as fdl on ru.UserID = fdl.UserID
where IsActive = 1
group by ru.userid, ru.LastNm, ru.firstnm
order by row

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cwolcott
Starting Member

4 Posts

Posted - 2012-04-24 : 15:42:05

Thank you very much. Works perfectly. Not sure why it slipped my mind to add MAX(fdl.downloaddate) instead of lastdownloaddate to the OVER section of the query.

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 21:34:50

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources