Taking only the latest data from multiple groups

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

Taking only the latest data from multiple groups

Author

Topic

Tototo1
Starting Member

22 Posts

Posted - 2012-03-15 : 15:26:20

I am having trouble coming up with an idea of how to take a large group of data, group by a certain parameter and only take the most recent data. I will explain below.

I have a table with lmatter, linvoice and ldate. There are multiple lmatters and multiple linvoices that belong to seperate lmatters.

lmatter linvoice ldate
111 100 1992-05-06 00:00:00.000
111 101 1992-06-06 00:00:00.000
112 134 1992-04-15 00:00:00.000
112 102 1992-01-01 00:00:00.000
112 116 1992-07-19 00:00:00.000
113 198 1992-03-03 00:00:00.000
113 201 1993-01-01 00:00:00.000
113 125 1992-06-17 00:00:00.000

I would like to return the following:

111 101 1992-06-06 00:00:00.000
112 116 1992-07-19 00:00:00.000
113 201 1993-01-01 00:00:00.000

I have been poking around my little SQL book for ideas but I do not even know where to begin. I tried to use the TOP command but didn't have any luck. Any ideas that could help me on my way? Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 15:28:36

you gotta read some of the other posts

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=172519

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 15:31:24

use ROW_NUMBER


SELECT required columns...
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY lmatter  ORDER BY ldate DESC) AS Rn,*
FROM table
)t
WHERE rn=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 15:34:57

couple of other methods just FYI


using apply operator

SELECT t.*
FROM table t
CROSS APPLY (SELECT Max(ldate) AS latest
             FROM table
             WHERE lmatter = t.lmatter
            )t1
WHERE latest = ldate

using derived table (sql 2000)

SELECT
FROM table t
INNER JOIN (SELECT lmatter,MAX(ldate) AS latest
            FROM table
            GROUP BY lmatter
            )t1
ON t1.lmatter = t.lmatter
AND t1.latest = t.ldate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources