Group of Data Pulled by Last Date

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

Group of Data Pulled by Last Date

Author

Topic

SASNoob
Starting Member

2 Posts

Posted - 2012-09-04 : 16:42:36

Hello all, total new user here and total SQL Newb trying to create reports using SAS EG 4.3. Glad to see forums like this exist as I plan on frequenting this a lot and learning SQL/SAS for my job.

I have to pull group/role information from a list of users and am wondering how to pull a group of info based on the last datestamp.

E.G:

Name Group Role Datestamp
Joe Smith SAS LVL 4 GOV Role 23AUG2012
Joe Smith SAS LVL 4 GOV Role 23AUG2012
Joe Smith SAS MGMT 23AUG2012
Joe Smith SAS LVL 4 GOV Role 01SEP2012
Joe Smith SAS LVL 3 GOV Role 01SEP2012
Joe Smith SAS MGMT 01SEP2012

I need to be able to pull a report from the last datestamp of a user getting their group updated. I know how to get the last datestamp for one row, just not multiple rows.

Thanks in advance!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-04 : 20:33:37

I don't know what SAS EG 4.3 is, but assuming you are querying a SQL Server database, either of the following would work if you are on SQL 2005 or later:

-- 1.
SELECT
	[Name],
	[Group],
	[Role],
	[Datestamp]
FROM
(	
SELECT
	[Name],
	[Group],
	[Role],
	[Datestamp],
	ROW_NUMBER() OVER (PARTITION BY [Name],[Group] ORDER BY [Datestamp] DESC) AS RN
FROM
	YourTable
) s
WHERE RN = 1;

-- 2.
SELECT
	a.[Name],
	a.[Group],
	b.[Role],
	b.[Datestamp]
FROM
	YourTable a
	OUTER APPLY
	(
		SELECT TOP (1)
			c.[Role],
			c.[Datestamp]
		FROM
			YourTable c
		WHERE
			c.[Name] = a.[Name]
			AND c.[Group] = a.[Group]
	) b;

SASNoob
Starting Member

2 Posts

Posted - 2012-09-05 : 11:31:23

Hmm, I believe I may be on the wrong forum? SAS Enterprise Guide is the program I'm using, which allows visual-based/manual editing of SQL code. Not exactly sure how I would apply your examples as they look a bit different than what I'm used to, but I will make an attempt. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources