Selecting most recent (maximum) values for unique IDs 1

[Azathoth]

I have a temporary table with these values:

product_id status_datetime status_code
45 1/3/1999 12:30 P
45 9/9/2000 10:20 S
45 6/6/2002 11:10 S
50 3/3/2003 09:30 S
67 4/5/2003 04:40 S
67 8/8/2004 12:20 P

I want to pull only the most recent values from this table by product_id - in other words, from this list, I would want to return:

product_id status_datetime status_code
45 6/6/2002 11:10 S
50 3/3/2003 09:30 S
67 8/8/2004 12:20 P

I'm not exactly sure how to use max() and/or select top x over a unique index...any help greatly appreciated.

 

[NoCoolHandle]

Something like

selectd top 3 * from tablex order by datecolumn desc

HTH


Rob

 

[cLFlaVA]

NoCoolHandle-

That would return the records with the 3 most recent dates, not necessarily with different ID's.

What Azathoth may want to try is

SELECT product_id, MAX(status_datetime)
FROM temp_table_name
GROUP BY product_id
ORDER BY product_id

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[Azathoth]

cLFlaVA, the solution you posted works perfectly, except I now realize that my question wasn't quite specific enough.

The correct values are returned, but I failed to mention that there are other columns of the table I need to return as well - values associated with the product_id that aren't necessarily unique, such as status_code. So if I take your block of code and add this column to it:

SELECT product_id, status_code,  MAX(status_datetime)
FROM #result
GROUP BY product_id, status_code
ORDER BY product_id

I end up getting back all most recent dates with status_code both P and S when I only want the most recent date over the product_id...is there a way to remedy this while still returning status_code?

 

[cLFlaVA]

Well, if you're including status_code in the group by clause, then you'll get most recent dates based on product id AND status code. I'm not entirely sure what you want to do, but if you want to basically get one product id record, with the most recent date, as well as its status code, then use max() and get rid of the group by.

SELECT product_id,  MAX(status_datetime), MAX(status_code)
FROM #result
GROUP BY product_id
ORDER BY product_id

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[Azathoth]

There's still a slight problem with this, in that what will be returned with my previously posted test data is:

product_id status_datetime status_code
45 6/6/2002 11:10 S
50 3/3/2003 09:30 S
67 8/8/2004 12:20 S

Notice that with product_id 67, the value it SHOULD return for status_code is P, but it returns S, I suppose because max returns S due to it's position in the alphabet. Is there any way to tack on status_code without using max() or grouping it?

 

[ESquared]

SELECT
   FROM temp_table_name T
   INNER JOIN (
      SELECT
         product_id,
         dt = Max(status_datetime)
      FROM temp_table_name
      GROUP BY product_id
      ORDER BY product_id
   ) T2 ON t1.product_id = T2.product_id and T1.status_datetime = T2.dt

 

[Azathoth]

It ended up being a bit more complex since I wanted to use TOP on a dynamic value, but the syntax for the subquery you wrote was extremely helpful, thank you. In case anyone wants to see the ugly solution:

	declare @query varchar(1000)
	declare @id_count int

	select
		@id_count = count(distinct product_id)
	from
		#result

	select @query = '
	select
		r.product_id,
		r.product_code,
		r.tpc_description,
		r.tpsc_description,
		r.status_code,
		r.location_code,
		r.status_datetime,
		r.lab_tech_1
	from 
		#result r
			inner join (
				select top ' + convert(varchar, @id_count)  + '
					product_id,
					dt = max(status_datetime)
				from
					#result
				group by
					product_id
				order by
					product_id ) rr
				on
					r.product_id = rr.product_id
					and r.status_datetime = rr.dt'

	exec (@query)

 

[ESquared]

Why do you need to use the DISTINCT and TOP stuff? Won't you get only one row per product_id?

You can eliminate the ORDER BY clause in the derived table, it was a mistake for me to include it.

If you truly need to only return a certain number of records, what criteria would you use to select the ones you want?