Select TOP 1 Query Help - 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 2008 Forums

Transact-SQL (2008)

Select TOP 1 Query Help

Author

Topic

jbrown7232
Starting Member

22 Posts

Posted - 2011-05-10 : 11:55:54

Hello

I have a table called KYH_Xmit_Counsel_Status_History and
a client can have multiple ID with multiple status and Status_Time.
I would like to get the most recent Status and Status Time for each ID. I tried to do this with the query below but it just gives me one record and thats it. I need all ID's just the top 1 for each.

select top 1 KSH.Status, KSH.Status_time from dbo.KYH_Xmit_Counsel_Status_History KSH
where KSH.xmit_status_id = KSh.xmit_status_id
order by ksh.Xmit_Status_History_Id desc

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-10 : 12:35:50

SELECT *
FROM

(select xmit_status_id,status,status_time
,[row] = row_Number() over(partition by clientid order by status_time desc)
) t
WHERE t.row = 1

Your description is really vague, so this may not be what you want, but it should be enough to get you started.

Jim

Everyday I learn something that somebody else already knew

jbrown7232
Starting Member

22 Posts

Posted - 2011-05-10 : 12:50:03

Jim where can I tell this query the table name KYH_Xmit_Counsel_Status_History? I am getting the following:

The multi-part identifier "KSH.xmit_status_id" could not be bound.

We are almost there. Thanks for your help!!!!

quote:
Originally posted by jimf

SELECT *
FROM

(select xmit_status_id,status,status_time
,[row] = row_Number() over(partition by clientid order by status_time desc)
) t
WHERE t.row = 1

Your description is really vague, so this may not be what you want, but it should be enough to get you started.

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-10 : 13:04:23

SELECT *
FROM

(select xmit_status_id,status,status_time
,[row] = row_Number() over(partition by clientid order by status_time desc)
from KSH.xmit_status_id
) t
WHERE t.row = 1

Everyday I learn something that somebody else already knew

jbrown7232
Starting Member

22 Posts

Posted - 2011-05-10 : 13:39:34

Got it thanks sooooo MUch you are the MAN!!!!

SELECT *
FROM
(select KSH.xmit_status_id,KSH.Status,KSH.Status_time
,[row] = row_Number() over(partition by KSh.xmit_status_id order by KSH.Status_time desc)
from KYH_Xmit_Counsel_Status_History KSH
) t
WHERE t.row = 1

quote:
Originally posted by jimf

SELECT *
FROM

(select xmit_status_id,status,status_time
,[row] = row_Number() over(partition by clientid order by status_time desc)
from KSH.xmit_status_id
) t
WHERE t.row = 1

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-10 : 13:41:31

You're Welcome!

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources