SELECT statement where rows are omitted based on another table

Question

Table with orders has another table with positions. I want the orders table to show but then only have the most up to-date position on it. Below is a picture of the 3 rows I want showing. Omit the rest.

enter image description here

SELECT DispatchTable.ordernumber, DispatchTable.truck,
       DispatchTable.driver, DispatchTable.actualpickup, 
       DispatchTable.actualdropoff, orders.pickupdateandtime, 
       orders.dropoffdateandtime, Truck002.lastposition, 
       Truck002.lastdateandtime
FROM DispatchTable 
INNER JOIN orders ON DispatchTable.ordernumber = orders.id 
INNER JOIN Truck002 ON DispatchTable.truck = Truck002.name
WHERE (orders.status = 'onRoute')

My project uses C#, sorry about that. Clearly this question does not. First time posting on this site. Thank you for the reply. — Strider489, Apr 17 '17 at 01:45

score 1 · Accepted Answer · answered Apr 17 '17 at 01:41

Assuming that you want the row having the latest lastdateandtime for the truck name, this should work:

SELECT DispatchTable.ordernumber,
       DispatchTable.truck,
       DispatchTable.driver,
       DispatchTable.actualpickup,
       DispatchTable.actualdropoff,
       orders.pickupdateandtime,
       orders.dropoffdateandtime,
       TruckLatest.lastposition,
       TruckLatest.lastdateandtime
  FROM DispatchTable
 INNER JOIN orders ON DispatchTable.ordernumber = orders.id
 INNER JOIN (SELECT name,
                    lastposition,
                    lastdateandtime
               FROM Truck002 Truck1
              WHERE lastdateandtime =
                    (SELECT MAX(lastdateandtime)
                       FROM Truck002 Truck2
                      WHERE Truck2.name = Truck1.name)) TruckLatest
         ON DispatchTable.truck = TruckLatest.name
 WHERE (orders.status = 'onRoute')

score 0 · Answer 2 · answered Apr 17 '17 at 00:10

If I understand correctly, you can get the most recent record for a truck using ROW_NUMBER():

SELECT dt.ordernumber, dt.truck,
       dt.driver, dt.actualpickup, 
       dt.actualdropoff, o.pickupdateandtime, 
       o.dropoffdateandtime, t.lastposition, 
       t.lastdateandtime
FROM DispatchTable dt INNER JOIN
     orders o
     ON dt.ordernumber = o.id INNER JOIN
     (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY t.name ORDER BY t.lastdateandtime DESC) as seqnum
      FROM Truck002 t
     ) t
     ON dt.truck = t.name
WHERE o.status = 'onRoute' AND seqnum = 1;

This worked brilliantly. Very sorry for the confusing Table name. — Strider489, Apr 17 '17 at 01:44

score 0 · Answer 3 · answered Apr 17 '17 at 00:56

Firstly, why are you using Truck002's name field rather than its id field as the link to DispacthTable? This is considered a less efficient way of doing it than using id (which is either a numerical field or a shorter string than name).

Secondly, you should mention in your Question that each Order can have many DispatchTable's and that each DispacthTable can have many Truck002's, otherwise many people will start by assuming that it is the other way round between DispatchTable and Truck002.

Thirdly, please try...

SELECT DispatchTable.ordernumber,
       DispatchTable.truck,
       DispatchTable.driver,
       DispatchTable.actualpickup,
       DispatchTable.actualdropoff,
       orders.pickupdateandtime,
       orders.dropoffdateandtime,
       Truck002.lastposition, 
       Truck002.lastdateandtime
FROM DispatchTable 
INNER JOIN orders ON DispatchTable.ordernumber = orders.id 
INNER JOIN Truck002 ON DispatchTable.truck = Truck002.name
WHERE (orders.status = 'onRoute')
GROUP BY ordernumber
HAVING lastdateandtime = MAX( lastdateandtime )

If you have any questions or comments, then please feel free to post a Comment accordingly.

Further Reading

https://msdn.microsoft.com/en-us/library/bb177906(v=office.12).aspx (on HAVING)

https://www.w3schools.com/sql/sql_having.asp (on HAVING)

https://msdn.microsoft.com/en-us/library/bb177905(v=office.12).aspx (on GROUP BY)

https://www.w3schools.com/sql/sql_groupby.asp (on GROUP BY)

Yes, i apologize, Truck002 is a terrible table name. I was going to make a new table for each Truck. But right now I am using Truck002 for all trucks. I will change this table name ASAP once I figure out this SQL statement. Thank you very much for the links. — Strider489, Apr 17 '17 at 01:43

SELECT statement where rows are omitted based on another table

3 Answers3