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'
As an Amazon Associate we earn from qualifying purchases.