Help with SQL Query - 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 2012 Forums

Transact-SQL (2012)

Help with SQL Query

Author

Topic

dimepop
Starting Member

33 Posts

Posted - 2013-07-11 : 10:08:17

Hi i have a table with columns
[name] ,[type],[ttl],[ip],[Timestamp] ,[office],[source],[outside],[ping],[status],[milliseconds]
There are 10 distinct names
I want to get a query that will display some columns of each name which have the latest timestamp (smalldatetime)
So my query will display the unique names with the latest timestamp and whatever is in the other columns.

I tried with this query but it shows jultiple names for each ....

SELECT name, ping, office, ip, status, milliseconds, max(Timestamp) as time
FROM table

group by name, ping, office, ip,status,milliseconds
order by name

What would be the best query?
Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-11 : 10:20:58

[code]SELECT name, ping, office, ip, status, milliseconds,TIME
FROM
(
SELECT name, ping, office, ip, status, milliseconds,Timestamp as TIME,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY TIMESTAMP DESC) AS RN
FROM TABLE
) s WHERE RN = 1
[/code]

dimepop
Starting Member

33 Posts

Posted - 2013-07-11 : 11:04:44

YES James K, you got it.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources