Selecting newest records - 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 2005 Forums

Transact-SQL (2005)

Selecting newest records

Author

Topic

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-01-24 : 18:16:26

This query will return multiple statuses with multiple dates. i need it to only return statuses with the latest date. multiple statuses will have the latest date.

so an example result set might be:
ContainerId, TerminalId, StatusDateTime, ShipmentStatusCode
1, 1, 1/24/2007, 'abc'
1, 1, 1/24/2007, 'def'
1, 1, 1/24/2007, 'ghi'
1, 1, 12/22/2006, 'jkl'
1, 1, 12/22/2006, 'mno'

i only want it to return the latest dates (so in this example, it would be the top 3, but it's not a set number of statuses). this should be simple. any ideas?


		select	C.ContainerId, SS.TerminalId, StatusDateTime, ShipmentStatusCode
		from	dbo.EDIShipmentStatuses SS
				inner join dbo.EDIContainers C on C.ContainerCode = SS.ContainerId
		group by C.ContainerId, SS.TerminalId, StatusDateTime, ShipmentStatusCode

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-24 : 18:24:26

select distinct top 1 with ties distinct C.ContainerId, SS.TerminalId, StatusDateTime, ShipmentStatusCode
from dbo.EDIShipmentStatuses SS
inner join dbo.EDIContainers C on C.ContainerCode = SS.ContainerId
order by StatusDateTime desc

Peter Larsson
Helsingborg, Sweden

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-01-24 : 18:35:54

Didn't quite work. That will return the statuses with the latest date overall. i need the latest date per (container / terminal).

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-01-24 : 18:48:51

aaaaahh.. figured it out. unfortunately, i needed a sub query.


		select	C.ContainerId, SS.TerminalId, StatusDateTime, ShipmentStatusCode
		from	dbo.EDIShipmentStatuses SS
				inner join dbo.EDIContainers C on C.ContainerCode = SS.ContainerId
		where	StatusDateTime = 
			(
				select	max(StatusDateTime) 
				from	dbo.EDIShipmentStatuses SS2 
				where	SS2.ContainerId = SS.ContainerId
						and SS2.TerminalId = SS.TerminalId
			)
		group by C.ContainerId, SS.TerminalId, StatusDateTime, ShipmentStatusCode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources