December 2, 2013 at 4:40 am
Hello
Please can someone help me out.
This is an example of my data
Service Date
===== ==============
400 01/02/13 13:53:08
400 01/02/13 15:22:06
400 01/02/13 16:45:54
400 02/02/13 18:51:44
400 02/02/13 19:57:02
421 02/02/13 19:59:35
421 02/02/13 20:25:12
What I need to get is to apply a rank so that the earliest time of the of the record for the service on the day is rank = 1 etc.
Service Date Rank
===== ============== ===
400 01/02/13 13:53:08 1
400 01/02/13 15:22:06 2
400 01/02/13 16:45:54 3
400 02/02/13 18:51:44 1
400 02/02/13 19:57:02 2
421 02/02/13 19:59:35 1
421 02/02/13 20:25:12 2
I'd be grateful for any assistance.
Cheers
J
December 2, 2013 at 4:55 am
Hi,
How about;
select
Service,
Date,
row_number() over(Partition by Service, cast(Date as date) order by Date)
from <YourTable>
December 2, 2013 at 5:59 am
Hi,
an alternative solution would be
Select Service , DATE ,
dense_rank() over(Partition by Service, cast(date as date) order by Date ) srNo
From [TableName]
December 3, 2013 at 1:16 am
Thank you so much for the replies.
Both have resolved what I was trying to do 🙂
Thanks a million
J
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply