SQLTeam.com | Weblogs | Forums

Get latest value from 3 tables

I pick some data from three tables TEST_DATA, TEST_PARAM and TEST_TEST. When I run the question I get more rows with the same information, (serial number and attribute), except from the date/time column.

How does a SQL question look like to get only the latest value for each pt_param_attr based on the date column?
Please Help!

I use Sybase, SQL Anywhere 12

My query:
SELECT pt_test_man_order, pt_test_serial, pt_data_var, pt_param_attr, pt_data_date
FROM test_data,
test_param,
test_test
WHERE pt_test_id = pt_data_test_id
AND pt_data_param_id = pt_param_id
AND ((pt_test_man_order='8775'))
ORDER BY pt_test_serial

I get this answer:

You would best be served by using ROW_NUMBER. I've included the option to search for the more recent 3 of each pt_param_attr as well. I hope this helps!

SELECT 
    info.pt_test_man_order
    ,info.pt_test_serial
    ,info.pt_data_var
    ,info.pt_param_attr
    ,info.pt_data_date
FROM (
    SELECT 
        pt_test_man_order
        ,pt_test_serial
        ,pt_data_var
        ,pt_param_attr
        ,pt_data_date
        ,ROW_NUMBER() OVER(PARTITION BY pt_param_attr ORDER BY pt_data_date DESC) AS rn
    FROM 
        test_data,
        test_param,
        test_test
    WHERE (1=1)
        AND pt_test_id = pt_data_test_id
        AND pt_data_param_id = pt_param_id 
        AND ((pt_test_man_order='8775'))
) info
WHERE (1=1)
    AND (info.rn = 1)
    --AND (info.rn IN (1,2,3))
GROUP BY 
    info.pt_test_man_order
    ,info.pt_test_serial
    ,info.pt_data_var
    ,info.pt_param_attr
    ,info.pt_data_date
    ,info.rn
ORDER BY 
    info.pt_test_serial
1 Like

Thank you for the answer, it works fine.
In my frustration i forgot to mention that I can have several serialno. in each man_order as you can see in the new Picture. I would like to have the latest values for each serialno. (exactly that you send me but, for each serialno.)

I'm glad that worked out for you. If you were looking to have two different reports, one that shows the most recent pt_param_attr and the other showing the most recent pt_test_serial, you could simply modify the ROW_NUMBER() line in the query above. Here's what I would do:

SELECT 
    info.pt_test_man_order
    ,info.pt_test_serial
    ,info.pt_data_var
    ,info.pt_param_attr
    ,info.pt_data_date
FROM (
    SELECT 
        pt_test_man_order
        ,pt_test_serial
        ,pt_data_var
        ,pt_param_attr
        ,pt_data_date
        ,ROW_NUMBER() OVER(PARTITION BY pt_test_serial ORDER BY pt_data_date DESC) AS rn
    FROM 
        test_data,
        test_param,
        test_test
    WHERE (1=1)
        AND pt_test_id = pt_data_test_id
        AND pt_data_param_id = pt_param_id 
        AND ((pt_test_man_order='8775'))
) info
WHERE (1=1)
    AND (info.rn = 1)
    --AND (info.rn IN (1,2,3))
GROUP BY 
    info.pt_test_man_order
    ,info.pt_test_serial
    ,info.pt_data_var
    ,info.pt_param_attr
    ,info.pt_data_date
    ,info.rn
ORDER BY 
    info.pt_test_serial

I dont want to have two reports. I would like to have one report, as you can see in my last Picture but I dont want to have the same serialno. with the same attr. For examle 8775-001 has two attr '4' and four attr '22'. I only want to have the latest for each attr and each serial.