Can I avoid using rank? - 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)

Can I avoid using rank?

Author

Topic

happyslug
Starting Member

5 Posts

Posted - 2013-07-25 : 06:33:27

I have tables to represent client and course

client
------

id int
clientname varchar(50)

course
------

id int
coursename varchar(50)
coursedate datetime

Each client may attend one or more course - so I have the following table

clientcourse
------------

id int
clientid int
courseid int

I want a select statement to give me the last course based on course date for each client

so if we had

client
------

id clientname
1 Tom
2 Dick
3 Harry

course
------
id coursename coursedate
1 English 1-jan-2013
2 Science 1-mar-2013
3 Maths 1-feb-2013

Clientcourse
------------

id clientid courseid
1 1 1
2 1 3
3 2 1
4 3 2
5 3 3

I want to return

clientid courseid
1 3
2 1
3 2

I think I could work this out myself using rank, but is there an easier way?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-25 : 06:44:37

[code]
SELECT clientid,courseid
FROM
(
SELECT cl.clientid,cr.courseid,ROW_NUMBER() OVER (PARTITION BY clientid ORDER BY cr.coursedate DESC) AS Seq
FROM client cl
INNER JOIN Clientcourse cc
ON cc.clientid = cl.clientid
INNER JOIN course cr
ON cr.id = cc.courseid
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

happyslug
Starting Member

5 Posts

Posted - 2013-07-30 : 04:57:42

Thanks visakh16

This is similar to how I would do it with rank - I guess ROW_NUMBER is similar.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-30 : 05:10:38

Not exactly. Rank will return same value for all the rows with same value of coursedate. RowNumber will just randomly number them as 1,2,3 etc even if value is same.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

happyslug
Starting Member

5 Posts

Posted - 2013-08-01 : 07:14:47

OK - thanks again.

I've just used this again on a different project.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 07:38:16

cool
glad that I could be of help to you

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources