We help IT Professionals succeed at work.

Get the Top record based on time

Sha1395
Sha1395 used Ask the Experts™
on
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,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Engineer
Commented:
try
select No,   ServiceId,   MessageID,   DateCreated
from
(
  select No,   ServiceId,   MessageID,   DateCreated,
         row_number() over (partition by ServiceID order by DateCreated desc) rn
  from table1
) A
where A.rn = 1

Open in new window

Top Expert 2010
Commented:
Another way:


SELECT t1.No, t1.ServiceId, t1.MessageID, t1.DateCreated
FROM SomeTable t1 INNER JOIN
    (SELECT t2.ServiceId, MAX(t2.DateCreated) AS DateCreated
    FROM SomeTable t2
    GROUP BY t2.ServiceId) z ON t1.ServiceId = z.ServiceId AND t1.DateCreated = z.DateCreated

Open in new window

Author

Commented:
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