SQL to get minimum of two different fields

Question

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?

You could make it easier on yourself, and normalise your database so that you have all location/install data in the locationHistory table. — Oliver Charlesworth
– Oliver Charlesworth, Commented Jan 23, 2011 at 21:30
@oli Charlesworth: It's a performance choice to move historical records into another table, similar to how table partitioning works. — OMG Ponies
– OMG Ponies, Commented Jan 23, 2011 at 21:45

RichardTheKiwi · Accepted Answer · 2011-01-23 21:38:10Z

1

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

edited Jan 23, 2011 at 21:38

answered Jan 23, 2011 at 21:31

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Larry Lustig · Accepted Answer · 2011-01-23 21:38:11Z

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

Jonathan Leffler · Accepted Answer · 2011-01-23 23:38:38Z

0

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).

answered Jan 23, 2011 at 23:38

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

Collectives™ on Stack Overflow

SQL to get minimum of two different fields

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related