Re: sql max help -- MIDRANGE-L

On 26-Nov-2013 11:01 -0800, Smith, Mike wrote:

I have a table TAPEINV00 with the following
TISYSTEM TILABEL TILOCATE TISEQ TITAPEDATE
RG FRIDAY VAULT 1 20,130,614
RG FRIDAY VAULT 2 20,130,524
RG FRIDAY VAULT 3 20,130,531
RG FRIDAY VAULT 4 20,130,607
RG MONDAY VAULT 1 20,130,614
RG MONDAY VAULT 2 20,130,524
RG MONDAY VAULT 3 20,130,531
RG MONDAY VAULT 4 20,130,607

I am trying to get the value of TISEQ for the record with the
highest date for a specific system, label, location

So I have the following query and it works fairly well, except I
seem to get two records back

SELECT TISEQ
FROM tapeinv00
WHERE titapedate =
( select max(titapedate)
from tapeinv00
where tisystem = 'RG'
and TIlabel = 'FRIDAY'
and tilocate = 'VAULT'
)

What I would hope to get for this query is 1
I get two records with 1 returned and I'm not sure why.
Ideas?
Better way to do this query?

Charles responded:

SELECT TISEQ
FROM tapeinv00
WHERE titapedate =
( select max(titapedate)
from tapeinv00
where tisystem = 'RG'
and TIlabel = 'FRIDAY'
and tilocate = 'VAULT'
)
and tisystem = 'RG'
and TIlabel = 'FRIDAY'
and tilocate = 'VAULT'

But the constants in the predicates are duplicated. For creation and maintenance, there may be some value in using a correlated subquery. Further comments, later.

Dan responded with a row-value-expression and IN predicate:

SELECT TISEQ
FROM tapeinv00
WHERE (titapedate, tisystem, TILabel, tilocate) IN
( select max(titapedate), tisystem, TILabel, tilocate
from tapeinv00
where tisystem = 'RG'
and TIlabel = 'FRIDAY'
and tilocate = 'VAULT'
)

Doesn't that subquery require a GROUP BY clause? And given there can be only one [summary] row that matches, the equal basic-predicate vs the IN predicate [acting as an equal SOME or equal ANY quantified-predicate] should be valid.? Note: I have no ability to test any of the queries with row-value-expressions.

SELECT TISEQ
FROM tapeinv00
WHERE (titapedate, tisystem, TILabel, tilocate) =
( select max(titapedate), tisystem, TILabel, tilocate
from tapeinv00
where tisystem = 'RG'
and TIlabel = 'FRIDAY'
and tilocate = 'VAULT'
group by tisystem, TILabel, tilocate
)

The subquery could be rewritten more generically, using a correlated subquery for selection, and then the outer query would be used to select the "specific system, label, location", with the string constants being specified only once in predicates. It may make no difference for performance of the specific query, but the intent and effects may be more obvious to some, including the optimization if the compared constants are converted to parameter markers or are host variables.?:

SELECT TISEQ
FROM tapeinv00 as Q
WHERE tisystem = 'RG'
and TIlabel = 'FRIDAY'
and tilocate = 'VAULT'
and (titapedate, tisystem, TILabel, tilocate) =
( select max(titapedate), tisystem, TILabel, tilocate
from tapeinv00 as S
where s.tisystem = q.tisystem
and s.TIlabel = q.TIlabel
and s.tilocate = q.tilocate
)

And more generically, if desirable, that query could be encapsulated in a VIEW to re-represent all columns of the data but with only the most recent row [by the date] for each group, and the specific selection dropped. The selection of the "specific system, label, location" is left to a query of that VIEW; from which, the tiSeq value will always be the one associated with the [row with the] most recent date:

create view tapeinvSeq as
SELECT Q.*
FROM tapeinv00 as Q
WHERE (titapedate, tisystem, TILabel, tilocate) =
( select max(titapedate), tisystem, TILabel, tilocate
from tapeinv00 as S
where s.tisystem = q.tisystem
and s.TIlabel = q.TIlabel
and s.tilocate = q.tilocate
group by tisystem, TILabel, tilocate
)

Then the query becomes just:

select tiseq
from tapeinvSeq
where tisystem = 'RG'
and TIlabel = 'FRIDAY'
and tilocate = 'VAULT'

Some other variations:

The query from Charles rewritten with a correlated subquery; the [string literals] constants appear only once:

SELECT TISEQ
FROM tapeinv00 as Q
WHERE tisystem = 'RG'
and TIlabel = 'FRIDAY'
and tilocate = 'VAULT'
and titapedate =
( select max(titapedate)
from tapeinv00 as S
where s.tisystem = q.tisystem
and s.TIlabel = q.TIlabel
and s.tilocate = q.tilocate
)

The above query generically written, including all of the columns, dropping the specific selection, and encapsulated in a VIEW:

create view tapeinvSeq as
SELECT Q.*
FROM tapeinv00 as Q
WHERE titapedate =
( select max(titapedate)
from tapeinv00 as S
where s.tisystem = q.tisystem
and s.TIlabel = q.TIlabel
and s.tilocate = q.tilocate
)

Then the query becomes just:

select tiseq
from tapeinvSeq
where tisystem = 'RG'
and TIlabel = 'FRIDAY'
and tilocate = 'VAULT'

Rewritten instead as an explicit JOIN and using predicates in the join ON vs the WHERE:

SELECT q.TISEQ
FROM tapeinv00 as Q
INNER JOIN lateral /* or: table */
( select max(titapedate) as mxdt
from tapeinv00 as S
where s.tisystem = q.tisystem
and s.TIlabel = q.TIlabel
and s.tilocate = q.tilocate
) as r
ON q.tisystem = 'RG'
and q.TIlabel = 'FRIDAY'
and q.tilocate = 'VAULT'
and q.titapedate = r.mxdt

Rewritten more generically as that same JOIN, including all columns, but using predicates in the WHERE clause:

SELECT q.*
FROM tapeinv00 as Q
INNER JOIN lateral /* or: table */
( select max(titapedate) as mxdt
from tapeinv00 as S
where s.tisystem = q.tisystem
and s.TIlabel = q.TIlabel
and s.tilocate = q.tilocate
) as r
ON q.titapedate = r.mxdt
WHERE q.tisystem = 'RG'
and q.TIlabel = 'FRIDAY'
and q.tilocate = 'VAULT'

Rewritten generically as above, but created as a VIEW without the specific selection. The selection of the "specific system, label, location" is deferred to whomever queries that VIEW:

create view tapinvseq as
SELECT q.*
FROM tapeinv00 as Q
INNER JOIN lateral /* or: table */
( select max(titapedate) as mxdt
from tapeinv00 as S
where s.tisystem = q.tisystem
and s.TIlabel = q.TIlabel
and s.tilocate = q.tilocate
) as r
ON q.titapedate = r.mxdt

Then the query becomes just:

select tiseq
from tapeinvSeq
where tisystem = 'RG'
and TIlabel = 'FRIDAY'
and tilocate = 'VAULT'