2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1811001
  • 23
  • 3

3 Answers3

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.

ChaseMedallion
  • 20,860
  • 17
  • 88
  • 152
  • Exactly what I needed! Thank you so much. I knew it was something fairly simple, just needed the right formatting. Thanks again to all that helped! – user1811001 Feb 18 '13 at 15:24
1

You need to join the two tables then order by the COLLECT_TS column:

Select TB2.PROD_NAME , TB1.COLLECT_TS  From [Table_1] as TB1
Join [Table_2] as TB2 on TB1.PROD_ID = TB2.TB1.PROD_ID 
Order by TB1.COLLECT_TS desc
Waqar
  • 758
  • 4
  • 15
0
SELECT MAX(COLLECT_TS), MAX(PROD_NAME)
FROM dbo.SG_DATA a
inner join dbo.PRODUCT b on a.prod_id = b.prod_id
GROUP by PROD_ID
Techmonk
  • 1,459
  • 12
  • 20