multiple inner joins - with max date

[TMEAGLE2]

Hi.. I'm running the select query below without any problems. Issue is it give me all the locations (POSHIST.LATITUDE) table. Positions in this table are reported every 15 minutes, so I need to just get the most recent position. In less complex queries (without this combination of joins) I'm able to use this code to get the most recent position:

>>>>inner join lynx.poshist poshist on poshist.device = mobile_device.device_id where poshist.PosDate = (select max(PosDate) FROM lynx.poshist WHERE device = mobile_device.device_Id )<<<<<

Can someone tell me how I can get this into the code below to show the latest position? Thanks for help!!!

SELECT TLORDER.BILL_NUMBER, SVCLEVEL.CODE, TLORDER.PICK_UP_BY, ITRIPTLO.CURRENTLY_ASSIGNED, ITRIPTLO.TRIP_NUMBER, PUNIT.UNIT_ID, MOBILE_DEVICE.DEVICE_ID, WEB_CLIENT_GROUPINGS.GROUP_NAME, WEB_CLIENT_GROUPINGS.CLIENT_ID, WEB_USER_GROUPS.GROUP_NAME, WEB_USERS.EMAIL, POSHIST.LATITUDE
FROM (((LYNX.TLORDER TLORDER INNER JOIN LYNX.ITRIPTLO ITRIPTLO ON TLORDER.BILL_NUMBER=ITRIPTLO.BILL_NUMBER) INNER JOIN ((LYNX.WEB_USER_GROUPS WEB_USER_GROUPS INNER JOIN LYNX.WEB_CLIENT_GROUPINGS WEB_CLIENT_GROUPINGS ON WEB_USER_GROUPS.GROUP_NAME=WEB_CLIENT_GROUPINGS.GROUP_NAME) INNER JOIN LYNX.WEB_USERS WEB_USERS ON WEB_USER_GROUPS.USER_ID=WEB_USERS.USER_ID) ON TLORDER.BILL_TO_CODE=WEB_CLIENT_GROUPINGS.CLIENT_ID) INNER JOIN LYNX.SVCLEVEL SVCLEVEL ON TLORDER.SERVICE_LEVEL=SVCLEVEL.CODE) INNER JOIN LYNX.PUNIT PUNIT ON MOBILE_DEVICE.ASSIGNED_TO=PUNIT.UNIT_ID) ON ITRIPTLO.TRIP_NUMBER=PUNIT.CURRENT_TRIP INNER JOIN ((LYNX.POSHIST POSHIST INNER JOIN LYNX.MOBILE_DEVICE MOBILE_DEVICE ON POSHIST.DEVICE=MOBILE_DEVICE.DEVICE_ID)
WHERE TLORDER.PICK_UP_BY>={ts '2010-05-21 00:00:00'}

 

[philhege]

Are you missing a parenthesis in this query?

-------++NO CARRIER++-------

 

[TMEAGLE2]

Maybe.. but I just tested it as (cut and paste) and it works! My real issue is how to tie in the "inner join lynx.poshist poshist on poshist.device = mobile_device.device_id where poshist.PosDate = (select max(PosDate) FROM lynx.poshist WHERE device = mobile_device.device_Id" Any idea on that? Thanks for the response!

 

[philhege]

You can turn the WHERE clause into a JOIN condition.

-------++NO CARRIER++-------

 

[AndyInNC]

I see you've got several tables here in this query and they're all using INNER JOIN which restricts each table to return only where their fields match. Hopefully, that will make my suggestion a little easier to read, 'cuz I'm not gonna reformat your SQL.

In this scenario (and it comes up quite often), you want to return several values from the table, but only for a given record in a table -- in your case, the Max(date).

Here are the steps:

Write a SELECT query that returns only that value and its "grouper".

SELECT   Max(PosDate) AS MaxOf_PosDate, Device
FROM     PosHist
GROUP BY Latitude

This is your derived table. Join this with the PosHist table to return all the PosHist data, but only for the most recent record for each Latitude. Then join PosHist on both Device and PosDate to the derived table.

SELECT   ph.*
FROM     PosHist ph
         INNER JOIN (SELECT   Max(PosDate) AS MaxOf_PosDate, Device
                     FROM     PosHist
                     GROUP BY Latitude) phMax
                ON ph.Device = phMax.Device
                AND ph.PosDate = phMax.MaxOf_PosDate

You can add the other tables below with other joins. My suggestion is [red]if you use this over and over, turn the above into a view and just bring that into each query.[/red]