SQL Max value in a series of ranges

--amadeus--

Hi folks,

I have a problem that *should* be simple but I am drawing a total blank...

I have a data set that contains a series of order numbers. Each order number can have multiple releases and each order / release can have a series of transactions. All of these are of course seperate data entries and every data entry has a lead time value.

So for example I could have the following data set:

ORDER--RELEASE--TRANSACTION--LEAD
123---1--010--10
123---1---020--20
123---2---010---10
123---2---030---5
124---1---010---3

And I would want to return the following results:
ORDER--RELEASE--TRANSACTION--LEAD
123---1---020--20
123---2---030---5
124---1---010---3

This is a unique entry for every Order / Release combo but only selecting the highest Transaction number for each of these combos. I could write some VB to select the values but it would be dog slow (aprox 5000 records per month top process) and I am sure that some combo of MAX and DISTINCT will do it but I'm dammed if I can figure it out,

Thanks!

seamus

SELECT order, release, MAX(transaction) GROUP BY order, release

Maybe?

--amadeus--

That's what I've been trying, more specifically:

SELECT Packaging.month, Packaging.YEAR, Packaging.WRKNO, Packaging.RELNO,max( Packaging.TRSEQ), Packaging.Lead_time
FROM Packaging
WHERE (((Packaging.month)=11) AND ((Packaging.YEAR)=2010))
group by Packaging.month, Packaging.YEAR, Packaging.WRKNO, Packaging.RELNO, Packaging.Lead_time;

I need it to return the Lead Time value as that is the field that I will be performing calculations on. But when the value in the LT fied is different for different transaction numbers (TRSEQ) then I get a returned value for both

FSL

What you want is the Packaging.Lead_time where the Packaging.TRSEQ = the max( Packaging.TRSEQ) but not sure how to incorporate that into your select statement.

E39MSport

ordered analytical: -

SEL
ORDER, RELEASE, LEAD, TRANSACTION
FROM
YOURTABLE
QUALIFY ROW_NUMBER() OVER (
PARTITION BY ORDER, RELEASE, LEAD
ORDER BY TRANSACTION DESC) = 1;

Works in Teradata V13. Might be something similar on the platform you're working with.

seamus

You need to take Packaging.Lead_time out of the GROUP BY section, but the problem here is that you'll then get the first value for Lead_time instead of the correct value, where there are multiple rows.

How I've resolved similar stuff before is by constructing a key which can be used as part of a subquery.

So your subquery is something like

SELECT CONCAT(month, year, workno, relno, MAX(tsreq)) as 'pri_key' FROM table GROUP BY (month, year, workno, relno, tsreq)

This gives you a list of "keys" relating to the latest tsreqs.

Then incorporate this in your main query, eg.

SELECT 
		CONCAT(t.month, t.year, t.workno, t.relno, t.tsreq) as 'pri_key', 
		t.month, t.year, t.workno, t.relno, t.tsreq, lead_time 
FROM 
	table1 t
JOIN (
		SELECT 
			CONCAT(month, year, workno, relno, MAX(tsreq)) as 'pri_key' 
			FROM table1
		GROUP BY (month, year, workno, relno, tsreq)) as 'keys'
	ON 'keys'.'pri_key' = t.'pri_key'

Pseudocode, may or may not work...

--amadeus--

Excellent, so it wasn't simple and I wasn't being dim

Thanks for teh suggestions; Seamus that looks like it will work, much obliged!

seamus

Sorry, just realised my subquery is a little bit wrong, should be:

SELECT
CONCAT(month, year, workno, relno, MAX(tsreq)) as 'pri_key'
FROM table1
GROUP BY (month, year, workno, relno)

having tsreq in the GROUP BY will return all rows instead of the MAX(tsreq)