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

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.