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