CODEWITHSUNDEEP
sqlsql-server
Damian M Rickard
Damian M Rickard

Reputation: 21

Compare two rows in SQL Server and return only one row

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

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.

Upvotes: 1

Views: 93

Answers (3)

Sean Lange
Sean Lange

Reputation: 33581

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

Upvotes: 2

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

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
     )

Upvotes: 0

OptimisticToaster
OptimisticToaster

Reputation: 19

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

Upvotes: -1

Related Questions