Compare two rows in SQL Server and return only one row

Question

I have a table (trips) that has response data with columns:

TripDate
Job
Address
DispatchDateTime
OnSceneDateTime
Vehicle

Often two vehicles will respond to the same address on the same date, and I need to find the one that was there first.

I've tried this:

SELECT 
  TripDate,
  Job,
  Vehicle,
  DispatchDateTime
  (SELECT min(OnSceneDateTime)
   FROM Trips AS FirstOnScene
   WHERE AllTrips.TripDate = FirstOnScene.TripDate
     AND AllTrips.Address = FirstOnScene.Address) AS FirstOnScene
FROM
  Trips AS AllTrips

But I still get both records returned, and both have the same FirstOnScene time.

How do I only get THE record, with it's DispatchDateTime and OnSceneDateTime, and not the row of the trip that was on scene second?

Here are a few example rows from the table:

2016-01-01 0169-a  150 Main St  2016-01-01 16:52  2016-01-01 16:59 Truck 1
2016-01-01 0171-a  150 Main St  2016-01-01 16:53  2016-01-01 17:05 Truck 2
2016-01-01 0190-a  29 Spring St 2016-01-01 17:19  2016-01-01 17:30 Truck 5
2016-01-02 0111-a  8 Fist St  2016-01-02 09:30  2016-01-02 09:40  Truck 1
2016-01-02 0112-a  8 Fist St  2016-01-02 09:32  2016-01-02 09:38  Truck 2

In the above examples I need to return the first, third, and last row of that data set.

It is hard to determine which approach is best for you here because you didn't provide much in the way of details. You might be able to use top 1. Or maybe you need to use ROW_NUMBER. — Sean Lange, Mar 11 '16 at 16:13
I think comparison and selection have to be done in your client program, not in SQL level. — jungyh0218, Mar 11 '16 at 16:14
How do you decide which row of the trip that was on scene second? Is there any ID column or so?? — Deepshikha, Mar 11 '16 at 16:21
Possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Tab Alleman, Mar 11 '16 at 16:34

Sean Lange · Answer 1 · 2016-03-11T17:00:02.423

2

Here is a total shot in the dark based on the sparse information provided. I don't really know what defines a given incident so you can adjust the partition accordingly.

with sortedValues as
(
    select TripDate
        , Job
        , Vehicle
        , OnSceneDateTime
        , ROW_NUMBER() over(partition by Address, DispatchDateTime order by OnSceneDateTime desc) as RowNum
    from Trips
)
select TripDate
    , Job
    , Vehicle
    , OnSceneDateTime
from sortedValues
where RowNum = 1

edited Mar 11 '16 at 17:00

answered Mar 11 '16 at 16:16

Sean Lange

33,028
3
25
40

Based on the question, the most likely partitioning columns are date and address. Otherwise, this is the best approach. – Gordon Linoff Mar 11 '16 at 16:32
You are probably right Gordon. I changed the partitioning columns but hard to say for sure. – Sean Lange Mar 11 '16 at 17:00

score 0 · Accepted Answer · answered Mar 11 '16 at 16:22

0

You can just filter the rows down by selecting only the MIN OnSceneDateTime like below:

SELECT TripDate, Job, Vehicle, DispatchDateTime,OnSceneDateTime FirstOnScene
FROM Trips as AllTrips
WHERE AllTrips.OnSceneDateTime = (SELECT MIN(OnSceneDateTime) 
      FROM Trips as FirstOnScene
      WHERE AllTrips.TripDate = FirstOnScene.TripDate 
            and AllTrips.Address = FirstOnScene.Address
     )

answered Mar 11 '16 at 16:22

Wyatt Shipman

1,669
1
10
22

This appears to have worked for my situation. Thank you! – Damian M Rickard Mar 14 '16 at 12:33

score -1 · Answer 3 · answered Mar 11 '16 at 16:21

-1

How about use an ORDER BY on the OnSceneDateTime and then Limit 1. A simplified version like this:

SELECT TripDate, Job, Vehicle, DispatchDateTime, OnSceneDateTime FROM trips ORDER BY OnSceneDateTime LIMIT 1

answered Mar 11 '16 at 16:21

OptimisticToaster

19
5

Compare two rows in SQL Server and return only one row

3 Answers3