asked on

Compare records of the same table

ClientID Trip_Date PickupTime

---------------------------------------------

10005 11/3/2011 07:00

10005 11/3/2011 12:00

10005 11/4/2011 07:00

10005 11/4/2011 12:00

10006 11/3/2011 08:00

10006 11/3/2011 11:00

I have above data in the table TestTable. I need to get the records highlighted in bold. If the same client has multiple trips for the same day I need to get only one of the records that has min(PickupTime) for that date.

I'm using Sql Server 2008.

Appreciate your help!!

Scott Pletcher

SELECT *
FROM (
SELECT ClientID, TripDate, PickupTime, ...,
ROW_NUMBER() OVER (PARTITION BY ClientID, Trip_Date ORDER BY PickupTime) AS row_num
FROM ...
) AS derived
WHERE row_num = 1

VipulKadia

Try this one(Considering datatype of your PickupTime column is DateTime):

SELECT ClientID, Trip_Date, MIN(PickupTime)
FROM TestTable
GROUP BY ClientID, Trip_Date

Lowfatspread

please tell us the data tpes of the column if the above dont work for you

ASKER CERTIFIED SOLUTION

shahjagat

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

shahjagat

ASKER

Thank you!!

Scott Pletcher

That's why I used the method I chose.

>> SELECT ClientID, TripDate, PickupTime, ..., <<

The ... means you can add any other columns you want :-) .

Did you even consider it? It wil run a lot faster than you method.