Select the last row from each 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

SQL Server 2000 Forums

SQL Server Development (2000)

Select the last row from each group

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-07 : 09:05:57

Chris writes "My problem is simple -- I want to return the last row from each group of an ordered set of data.

For example, I want to return the marked rows from the following data:


doc_id  done    status_date     sort    code
2000034 0       02.02.21        20      cm
2000034 1       02.02.02        07      prd	/*select*/
2000035 0       01.09.09        12      pr
2000035 0       02.02.02        07      prd
2000035 0       02.02.02        14      pit
2000035 0       02.09.02        20      cm
2000035 1       02.02.02        08      eng	/*select*/
2000056 1       01.08.14        17      weba
2000056 1       01.08.15        09      tloc
2000056 1       01.09.04        11      floc
2000056 1       01.09.04        16      frs
2000056 1       01.09.04        19      arc	/*select*/

I am hoping for simple solution -- I have an ugly solution using max() and joining subqueries but I'm thinking there must be more elegant method.

Thanks"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-03-07 : 10:51:19

Don't think theres any really easy way to do this.

select *
from tbl t1
where not exists
(
select * from tbl t2
where t1.doc_id = t2.doc_id
and
(
t2.done > t1.done
or (t2.done = t1.done and t2.sort > t1.sort)
)
)

==========================================
Cursors are useful if you don't know sql.
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