Order Query by Timestamp and Name from two Different Tables

Question

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.

Answer 1

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.