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.
Author |
Topic |
IDILIO49
Starting Member
1 Post |
Posted - 2012-12-30 : 00:48:43
|
I have 3 tables and need to do a join with the 3 but with a limit for one of them.Lets say:Table PersonsTable CarsTable ChildsI want to see a line for every combination Person / Child / Car (but only for the last 4 cars)This can be the query for all combinationsselect p.name, c.name, r.name from Persons p, Childs c, Cars r where p.id=c.parentid and p.id=r.owneridbut now the question is how can I limit this to show only combinations for the most recent 4 Cars.date for that Person? |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-30 : 10:01:05
|
[code]Select p.name, c.name, r.namefrom(Select p.name, c.name, r.name,ROW_NUMBER() OVER (PARTITION BY P.Name Order by r.date desc) as Seqfrom Persons pinner join Childs c on p.id = c.parentid inner join Cars r on p.id = r.ownerid)TWhere T.Seq <=4[/code] |
|
|
|
|
|