Path: utzoo!news-server.csri.toronto.edu!bonnie.concordia.ca!ccu.umanitoba.ca!herald.usask.ca!alberta!aunro!aupair.cs.athabascau.ca!atha!decwrl!pacbell.com!ucsd!qualcom.qualcomm.com!harvey.qualcomm.com!greg
From: greg@harvey.qualcomm.com (greg Noel)
Newsgroups: comp.databases
Subject: Optimal SQL query needed
Message-ID: <1991Mar13.224746.5293@qualcomm.com>
Date: 13 Mar 91 22:47:46 GMT
Sender: news@qualcomm.com
Organization: Qualcomm, Inc., San Diego, CA
Lines: 29
Originator: greg@harvey.qualcomm.com

Please forgive me if this is the wrong news group; I'm not normally embroiled
in SQL problems.  If this is not the right newsgroup, I'd appreciate being told
what the correct one is.

I have an Oracle data base table, with three columns, which I will call A, B,
and C.  There are multiple thousands of value A.  For each value A, there are
a handful of records, perhaps as many as a dozen.  Within each value A, value
B is almost always unique (it's really a time value when the record became
active).  If value B is not unique within A, the tie is broken by value C,
which is unique within each A/B combination (it's the timestamp of when the
record was modified).

Here's the problem:  I want to define a view on this table that only consists
of the most recent active record for each value of A.  How do I define this
view such that the lookup is optimal?  Intuitivly, what I would like to tell
the data base engine is to look up all records with the same value of A, riffle
through them, and select the one with the largest B.C value.

What I am working with right now is
	select * from TBL T1 where A = 'value' and B =
		(select MAX(B) from TBL T2 where T2.B = T1.B)
which works except for the rare case when there is more than one maximum value
of B, which I handle programmatically.  This query is also not optimal as it
requires two indexed lookups within TBL.

Is there a way to do do the query more optimally or to do the whole query in
one request, so that the execptions don't need to be handled by the program?

Tks,