Avatar of Sha1395
Sha1395
 asked on

Get the Top record based on time

Hello All,

i have quite weird scenario.

Here is my table structure


No   ServiceId   MessageID   DateCreated
1    Q85M       12345       2011-08-05 10:13:05.813
2    Q85M       12346       2011-08-05 10:14:05.813
3    Q85M       12347       2011-08-05 10:15:05.813
4    Q95M       12348       2011-08-05 10:16:05.813
5    Q95M       12349       2011-08-05 10:17:05.813
6    Q95M       12343       2011-08-05 10:18:05.813
7    Q75M       12341       2011-08-05 10:18:05.813

I want to select the top(1) on service Id based on Date Created.

My query should retrun value like

3    Q85M       12347       2011-08-05 10:15:05.813
6    Q95M       12343       2011-08-05 10:18:05.813
7    Q75M       12341       2011-08-05 10:18:05.813

is this any one have any idea how can i do this,
Microsoft SQL Server 2005Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Sha1395

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Ephraim Wangoya

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sha1395

ASKER
you guys freak me out the amount of knowledge you have it

i never seen or used Partiion in my store proc before.

either way i came up with this query

SELECT a.MessageIdentifier, a.batchServiceId, a.DateCreated
FROM tblBatchRecords a, (SELECT batchServiceId, max(DateCreated) AS max_date FROM tblBatchRecords GROUP BY batchServiceId) b
WHERE a.batchServiceId = b.batchServiceId AND a.DateCreated = b.max_date

But i wil go with ewangoya query . just modified little bit here

      select MessageIdentifier,   batchServiceId,Sender,MsgType,  DateCreated
     from
      (
  select batchServiceId,   MessageIdentifier,Sender,MsgType, DateCreated,
         row_number() over (partition by batchServiceId order by DateCreated desc) rn
         from tblBatchRecords
       ) A
   where A.rn = 1 and DATEDIFF (HOUR,datecreated,GETDATE())<1

Thanks ewangoya and :matthewspatrick
Your help has saved me hundreds of hours of internet surfing.
fblack61