Note: I am a little new to SQL Server, so this should be an easy question, and I apologize for mistakes in formatting this question.
This is SQL Server 2008 R2.
I have two tables.
Table 1: DATA
has key ID
and columns PROD_ID, COLLECT_TS
Table 2: PRODUCT
has key PROD_ID
and column PROD_NAME
I want my query to list every PROD_NAME
by the latest COLLECT_TS
Here is what I have thus far:
SELECT MAX(COLLECT_TS), PROD_ID
FROM dbo.SG_DATA
GROUP by PROD_ID
This successfully returns all the latest COLLECT_TS
with a PROD_ID
, but the PROD_ID
is just a number and doesn't mean anything to the user.
Thanks in advance for your answers.
Select max(p.prod_name) as prod_name, max(d.collect_ts) as collect_ts
From product p
Join data d -- left join if you want products with no data
On d.prod_id = p.prod_id
Group by p.prod_id
The idea is to join products and data so we get one row for each data entry with the extra information from products attached. Then we group by product id and take the max collect ts from each group. We also take the max name, but that's ok because name will be constant across the group, so we just need some aggregation to select a single value.