SQLTeam.com | Weblogs | Forums

Get latest value from 3 tables


#1

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:


#2

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

#3

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


#4

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

#5

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.