× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.