get last row - 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 2000 Forums

SQL Server Development (2000)

get last row

Author

Topic

irene casper
Starting Member

4 Posts

Posted - 2006-12-13 : 19:58:01

table1:
id, process, date(yyyymmdd)
==================
alice, aaa, 20061201
alice, aaa, 20061203
alice, aaa, 20061211
brandon, bbb, 20061205
brandon, bbb, 20061210
casper, ccc, 20061213

Is there any way to get the result of the following using a single query?
id, process, date(yyyymmdd)
==================
alice, aaa, 20061211
brandon, bbb, 20061210
casper, ccc, 20061213

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-13 : 20:02:58

SELECT id, process, MAX(date)
FROM table1
GROUP BY id, process

Tara Kizer

irene casper
Starting Member

4 Posts

Posted - 2006-12-13 : 20:36:11

let's hv a slight change of table1:
id, process, date(yyyymmdd)
==================
alice, aaa, 20061201
alice, aaa, 20061203
alice, aaa, 20061211
brandon, aaa, 20061201
brandon, aaa, 20061212
casper, aaa, 20061213

alice, bbb, 20061211
brandon, bbb, 20061213

here, i 2 process: 'aaa' 'bbb'
after i get the max(date) from each id in each process,i get:
alice, aaa, 20061211
brandon, aaa, 20061212
casper, aaa, 20061213

alice, bbb, 20061211
brandon, bbb, 20061213

and now i wanna find those id that exist in both process, which mean my result would be:
alice
brandon

how can it be done?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-13 : 20:47:33

Try this:


SELECT id
FROM
(
	SELECT id, process--, MAX(date)
	FROM table1
	GROUP BY id, process
) t
GROUP BY id
HAVING COUNT(*) > 1

Or this:


SELECT id
FROM
(
	SELECT DISTINCT id, process
	FROM table1
) t
GROUP BY id
HAVING COUNT(*) > 1

Tara Kizer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-14 : 06:54:42

or
SELECT id, process
FROM table1
GROUP BY id, process
Having count(*)>1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources