Link to home
Start Free TrialLog in
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,
ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sha1395
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