SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Can I avoid using rank?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

happyslug
Starting Member

5 Posts

Posted - 07/25/2013 :  06:33:27  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/25/2013 :  06:44:37  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

happyslug
Starting Member

5 Posts

Posted - 07/30/2013 :  04:57:42  Show Profile  Reply with Quote
Thanks visakh16

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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/30/2013 :  05:10:38  Show Profile  Reply with Quote
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
Go to Top of Page

happyslug
Starting Member

5 Posts

Posted - 08/01/2013 :  07:14:47  Show Profile  Reply with Quote
OK - thanks again.

I've just used this again on a different project.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/01/2013 :  07:38:16  Show Profile  Reply with Quote
cool
glad that I could be of help to you

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.55 seconds. Powered By: Snitz Forums 2000