SQL query: most recent in ea. 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

General SQL Server Forums

New to SQL Server Programming

SQL query: most recent in ea. group

Author

Topic

JustinTo
Starting Member

2 Posts

Posted - 2008-07-31 : 12:14:12

Hi, wondering if someone could help me out with an SQL query. I need to select the most recent row in every group (grouped by group#)

group#/name/date(rail's timestamp)
1/bob/2008-07-30 16:00:43 UTC
2/joe/2008-07-30 16:00:43 UTC
1/john/2008-07-31 16:00:43 UTC
2/ed/2008-07-31 16:00:43 UTC
3/bill/2008-07-31 16:00:43 UTC
3/tom/2008-07-31 18:00:43 UTC

So I need:
1/john/2008-07-31 16:00:43 UTC
2/ed/2008-07-31 16:00:43 UTC
3/tom/2008-07-31 18:00:43 UTC

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 12:46:49

[code]SELECT t.*
FROM YourTable t
INNER JOIN (
SELECT [group#],MAX(date) as MaxDate
FROM YourTable
GROUP BY [group#]
)t1
ON t1.[group#]=t.[group#]
AND t1.MaxDate=t.date[/code]

JustinTo
Starting Member

2 Posts

Posted - 2008-07-31 : 13:15:52

Thank you so much visakh, that works spectacularly!

I have another issue, though, not sure if you would know how to solve this,

I'm using this SQL in a function, which can accept an option 'search' term

function(search)
SQL
end

How would I add a WHERE column=search clause into the SQL you wrote? If you have any idea how to do this, that'd be great. (also, if the search term is ommitted, how would I default to search for every column?)

THANKS so much once again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 13:40:03

[code]CREATE PROCEDURE YourProcedureName
@SearchParam <datatype>=NULL--default value
AS
SELECT t.*
FROM YourTable t
INNER JOIN (
SELECT [group#],MAX(date) as MaxDate
FROM YourTable
GROUP BY [group#]
)t1
ON t1.[group#]=t.[group#]
AND t1.MaxDate=t.date
WHERE t.YourSearchField = @SearchParam
OR @SearchParam IS NULL
GO[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources