Select / Group by question - 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 2005 Forums

Transact-SQL (2005)

Select / Group by question

Author

Topic

ampo
Starting Member

6 Posts

Posted - 2009-12-06 : 07:58:26

Hello.

I have the following table:

ID | SubId | Param1 | param2 | Time
------------------------------------------------------
1 | 1 | A | 2 | 2009-12-06 11:20:05.000
1 | 1 | A | 3 | 2009-12-06 13:11:30.000
1 | 2 | B | 1 | 2009-12-06 13:06:56.000

The output I need is row for each SubId with the max time:
From the above example its the second and third rows.
In the output I need all the data from the row.

Thank you very much for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-06 : 08:31:46

[code]
select *
from
(
select *, row_no = row_number() over (partition by SubId order by Time desc)
from yourtable
) a
where row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-06 : 08:56:51

Hi

Another way..



CREATE TABLE #TMP (ID INT, SUBID INT, PARAM1 CHAR(1), PARAM2 INT, TIMES DATETIME)

INSERT INTO #TMP
SELECT 1 , 1 , 'A' , 2 , '2009-12-06 11:20:05.000' UNION ALL
SELECT 1 , 1 , 'A' , 3 , '2009-12-06 13:11:30.000' UNION ALL
SELECT 1 , 2 , 'B' , 1 , '2009-12-06 13:06:56.000'



SELECT  T.SUBID,
	  K.PARAM1,
	  K.PARAM2,
	  K.TIMES
FROM	  #TMP K
INNER JOIN 
(SELECT SUBID,MAX(TIMES)AS DATE FROM #TMP  GROUP BY SUBID)T 
ON T.DATE = K.TIMES AND T.SUBID = K.SUBID
ORDER BY K.TIMES DESC

-------------------------
R...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources