Getting last record - 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)

Getting last record

Author

Topic

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2012-05-07 : 02:05:00

If I have the following scenario how do I find query to find the last records (based on last project stage) from these 2 tables that are joined

Table = Project

ID Project Name
1 Project 1
2 Project 2
3 Project 4

Table = Project_Status

ID Project_ID Start_Date Status
1 1 1-1-12 A
2 1 2-2-12 B
3 2 1-1-12 A
4 3 1-1-12 A
5 3 2-2-12 B
6 3 3-3-12 C

So my results will be

Project Name Project Stage Start Date

Project 1 B 2-2-12
Project 2 A 1-1-12
Project 3 C 3-3-12

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-07 : 02:17:34

[code]
select *
from
(
select p.ProjectName, s.Stage, s.StartDate, rn = row_number() over (partition by p.ID order by s.Status desc)
from Project p inner join Project_Status s
on p.ID = s.Project_ID
) t
where t.rn = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2012-05-07 : 02:55:43

cheers for that - it works a treat

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-08 : 08:39:30

This is another way:


Select b.Project_Name, a.Status, a.Start_Date From 
	(Select *,
		(Case When Project_ID = 1 AND Start_Date IN (Select MAX(Start_Date) From Project_Status Where Project_ID = 1) then Start_Date
			  When Project_ID = 2 AND Start_Date IN (Select MAX(Start_Date) From Project_Status Where Project_ID = 2) then Start_Date
			  When Project_ID = 3 AND Start_Date IN (Select MAX(Start_Date) From Project_Status Where Project_ID = 3) then Start_Date
			  Else NULL
		 End) As MaxDate
	 From Project_Status) As a
JOIN Project As b ON a.Project_ID = b.ID
	Where a.MaxDate IS NOT NULL

N 28° 33' 11.93148"
E 77° 14' 33.66384"

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-08 : 08:44:47

Khtan, you're query is better than the one I gave. But, since you had already figured it out....I was just exercising my brain cells a little to find another way.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources