help with finding oldest date in a query

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

Other SQL Server 2008 Topics

help with finding oldest date in a query

Author

Topic

kjk_kjp
Starting Member

18 Posts

Posted - 2009-01-06 : 16:11:48

I have the following information/data in a table (SQL SERVER 2008)

ID TERM DATE
11 1999FA 1999-08-15
11 1999SP 1999-01-05
11 1998FA 1998-08-14
11 1998SU 1998-05-30
11 1998SP 1998-01-06
14 2001FA 2001-08-16
14 2002FA 2002-08-17
14 2001SP 2001-01-03
18 2008SU 2008-06-03
18 2008FA 2008-08-14
18 2009SP 2009-01-05

The result I'm looking for is what in in red above. I need to return one row for each ID that contains the oldest date. Any idea how I can do this in one query?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-06 : 16:21:17

select ID,TERM,[DATE] from
(Select row_number() over(partition by ID order by date)as seq,* from table)t
where t.seq =1

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-07 : 07:33:22

u can use rank(),dense_rank() also
instead of row_number()

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 08:13:35

Dense_rank will only be useful if there are TWO or more oldest post for any given ID.

E 12°55'05.63"
N 56°04'39.26"

kjk_kjp
Starting Member

18 Posts

Posted - 2009-01-07 : 09:58:45

OK - here is what I have tried
SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE) AS SEQ,
ID, TERM, DATE
FROM dbo.STUDENT_TERMS
WHERE ACAD_LEVEL = 'UG'
and TERM not like 'CE%'
ORDER BY ID

This is returning everything that mets the criteria in the where clause and gives each ID a seq number (each new ID, starts a new seq). This is great, cause I can see all the data and then use this for testing against my final result below to make sure I'm getting the correct data.

Final Result query:
SELECT ID, TERM, [DATE] FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE) AS SEQ,* from dbo.STUDENT_TERMS) t
WHERE ACAD_LEVEL = 'UG'
and TERM not like 'CE%'
and t.SEQ = 1
ORDER BY ID

The Final Results query is missing about 2000 rows of data that should be in there - so something in the query must not be quite right? Any ideas?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-07 : 10:07:57

You need to read your query and place your WHERE at the correct place.
This would be what you need. But then again, only you know you business rules for this query.

SELECT		ID,
		TERM,
		[DATE]
FROM		(
			SELECT	ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE) AS SEQ,
				ID,
				TERM,
				[DATE]
			WHERE	ACAD_LEVEL = 'UG'
				AND TERM NOT LIKE 'CE%'
			FROM	dbo.STUDENT_TERMS
		) AS t
WHERE		SEQ = 1
ORDER BY	ID

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-07 : 10:25:29

also see this to understand uses of row_number()
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

kjk_kjp
Starting Member

18 Posts

Posted - 2009-01-07 : 10:53:53

Thanks - that worked!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources