We help IT Professionals succeed at work.

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!!

Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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
Try this one(Considering datatype of your PickupTime column is DateTime):

SELECT ClientID, Trip_Date, MIN(PickupTime)
FROM TestTable
GROUP BY ClientID, Trip_Date
CERTIFIED EXPERT
Top Expert 2011

Commented:
please tell us the data tpes of the column if the above dont work for you
I have few other fields that I needed to retrieve from other tables along with the mentioned 3 fields. Thus Group by  didnt work.

I solved this by getting data into a datatable ordered by date , ClientID and pickupTime and deleted the rows that repeated for the clientID and date.

Thank you all for your suggestions

Author

Commented:
Thank you!!
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.