CODEWITHSUNDEEP
sqlselect
Jonathan
Jonathan

Reputation: 3534

SQL to get minimum of two different fields

I have two different tables to track location of equipment. The "equipment" table tracks the current location and when it was installed there. If the equipment was previously at a different location, that information is kept in the "locationHistory" table. There is one row per equip_id in the equipment table. There can be 0 or more entries for each equip_id in the locationHistory table. 

equipment
    equip_id
    current_location
    install_date_at_location

locationHistory
    equip_id
    location
    install_date
    pickup_date

I want an SQL query that gets the date of the FIRST install_date for each piece of eqipment...

Example:

equipment
=========
equip_id | current_location | install_date_at_location
     123   location1          1/23/2011

locationHistory
===============
equip_id | location  | install_date | pickup_date
     123   location2   1/1/2011       1/5/2011
     123   location3   1/7/2011       1/20/2011

Should return: 123, 1/1/2011

Thoughts?

Upvotes: 1

Views: 1652

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 753950

A simple way to do it is:

SELECT U.Equip_ID, MIN(U.Install_Date)
  FROM (SELECT E.Equip_ID, E.Install_Date_At_Location AS Install_Date
          FROM Equipment AS E
        UNION
        SELECT L.Equip_ID, L.Install_Date
          FROM LocationHistory AS L
       ) AS U
 GROUP BY U.Equip_ID

This could generate a lot of rows from the LocationHistory table, but it isn't clear that it is worth 'optimizing' it by trying to apply a GROUP BY and MIN to the second half of the UNION (because you'd immediately redo the grouping with the result from the information in the equipment table).

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50970

Well, the critical piece of information is whether the install_at_location_date in equipment can ever be less than what I assume is the historical information in locationHistory. If that's not possible, you can do:

SELECT * FROM locationHistory L INNER JOIN
   (SELECT equip_id, MIN(install_date) AS firstDate FROM locationHistory)
     AS firstInstalls F
    ON L.equip_id = F.equip_id AND L.install_date = F.firstDate

But if you have to worry about both tables, you need to create view that normalizes the tables for you, and then apply the query against the view:

CREATE VIEW normalLocations (equip_id, location, install_date) AS
   SELECT equip_id, location, install_date_at_location FROM equipment
   UNION ALL
   SELECT equip_id, location, install_date FROM equipment;

SELECT * FROM normalLocations L INNER JOIN
   (SELECT equip_id, MIN(install_date) AS firstDate FROM normalLocations)
      AS firstInstalls F
   ON L.equip_id = F.equip_id AND L.install_date = F.firstDate

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

You will want to union the queries that each look at one field, then use a MIN against it. Or you can use the CASE and MIN for the same effect

select e.equip_id, MIN(CASE WHEN h.install_date < e.install_date_at_location
     THEN h.install_date
     ELSE e.install_date_at_location
 END)  as first_install_date
from equipment e 
left join locationHistory h on h.equip_id = e.equip_id
group by e.equip_id

Upvotes: 1

Related Questions