Selecting First Row in a Sorted Group

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

General SQL Server Forums

New to SQL Server Programming

Selecting First Row in a Sorted Group

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-06-12 : 13:02:34

I have a table that has employee addresses and the date each address came into effect. I only want to select the address associated with the most recent date, since that by definition is the current address. My EmployeeAddress table looks like:


EmployeeID|Address------------------|City---------|State|ChangeDate
1000------|2121 Fifth Ave-----------|New York-----|NY---|2012-08-20 
1000------|80 Main Plaza------------|San Francisco|CA---|2012-05-01 
1000------|3050 Rose Avenue---------|Tulsa--------|OK---|2012-01-14 
2000------|111 Jiffy Lane-----------|Chicago------|IL---|2011-07-31 
3000------|555 Tulip RD-------------|Indianapolis-|IN---|2011-09-19 
3000------|3333 Peach Tree Terrace--|Atlanta------|GA---|2011-03-17

I want the SELECT to show this:


EmployeeID|Address------------------|City---------|State|ChangeDate
1000------|2121 Fifth Ave-----------|New York-----|NY---|2012-08-20 
2000------|111 Jiffy Lane-----------|Chicago------|IL---|2011-07-31 
3000------|555 Tulip RD-------------|Indianapolis-|IN---|2011-09-19

The query to get the first result is:

SELECT [EmployeeID]
,[Address]
,[City]
,[State]
,[ChangeDate]
FROM [Northwind].[dbo].[EmployeeAddress]
ORDER BY EmployeeID, ChangeDate DESC

The script to create the table is:


USE [Northwind]
GO
/****** Object:  Table [dbo].[EmployeeAddress]    Script Date: 06/12/2012 09:52:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeAddress](
	[EmployeeID] [nvarchar](10) NULL,
	[Address] [nvarchar](30) NULL,
	[City] [nvarchar](20) NULL,
	[State] [nvarchar](2) NULL,
	[ChangeDate] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[EmployeeAddress] ([EmployeeID], [Address], [City], [State], [ChangeDate]) VALUES (N'1000', N'3050 Rose Avenue', N'Tulsa', N'OK', CAST(0x00009FD800000000 AS DateTime))
INSERT [dbo].[EmployeeAddress] ([EmployeeID], [Address], [City], [State], [ChangeDate]) VALUES (N'1000', N'2121 Fifth Ave', N'New York', N'NY', CAST(0x0000A0B300000000 AS DateTime))
INSERT [dbo].[EmployeeAddress] ([EmployeeID], [Address], [City], [State], [ChangeDate]) VALUES (N'1000', N'80 Main Plaza', N'San Francisco', N'CA', CAST(0x0000A04400000000 AS DateTime))
INSERT [dbo].[EmployeeAddress] ([EmployeeID], [Address], [City], [State], [ChangeDate]) VALUES (N'2000', N'111 Jiffy Lane', N'Chicago', N'IL', CAST(0x00009F3100000000 AS DateTime))
INSERT [dbo].[EmployeeAddress] ([EmployeeID], [Address], [City], [State], [ChangeDate]) VALUES (N'3000', N'3333 Peach Tree Terrace', N'Atlanta', N'GA', CAST(0x00009EA900000000 AS DateTime))
INSERT [dbo].[EmployeeAddress] ([EmployeeID], [Address], [City], [State], [ChangeDate]) VALUES (N'3000', N'555 Tulip RD', N'Indianapolis', N'IN', CAST(0x00009F6300000000 AS DateTime))

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-12 : 13:08:27

select *
from
(
select *, seq = row_number() over (partition by EmployeeID order by ChangeDate desc) from EmployeeAddress
) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

BobRoberts
Posting Yak Master

109 Posts

Posted - 2012-06-12 : 14:44:56

Your solution is very elegant compared to some others I saw, so I'll go with yours.

Thanks.

quote:
Originally posted by nigelrivett

select *
from
(
select *, seq = row_number() over (partition by EmployeeID order by ChangeDate desc) from EmployeeAddress
) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources