Re: Simple(?) sql question...

Jonathan Ball wrote:

cg_news wrote:
"cg_news" <cag_ng@xxxxxxxxxxxxxxx> a ?crit dans le message de
news:mL%Mc.2553$AY5.1525@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Hello,

I have what I hope is a simple sql (DB2/AS400) question.
Assume 2 files exist. One is a transaction file with two fields, the
transaction ID and the transaction name. File two is keyed to file 1
through the transaction id and contains a transaction date. Since
transactions can be processed more than once, the transaction id can
appear in the second file any number of times, but at least once.

Now for my question. I need to be able to select all records in file 1
while joining to file 2 and selecting the most recent date. Am i making
any sense?

In other words, I need to see the transaction id, name, and most recent
date from file 2, but a simple join will return multiple instances of
the transaction ID where I want to only see each ID once?

Any help appreciated



I found it, with a little help from comp.databases.ibm-db2

In case anyone is interested, the relevant sql:

select distinct a.ID, a.NAME, b.DATE from MASTER a
, DETAIL b where a.ID = b.ID and
b.DATE = (select max(DATE) from DETAIL c where c.ID = a.ID)


You don't need DISTINCT (for what you're trying to do),
and in fact it will make your query perform worse.

You did, however, hit on the basic correct approach:
you have to determine the maximum transaction date in
the detail file. Here are two ways you might have
written the query; the first is pretty close to what
you did come up with. I changed the name of the date
field to tran_date, as DATE is a SQL reserved word.

select a.id, a.name, b.tran_date
from master a join detail b
on a.id = b.id
where b.tran_date =
(select max(tran_date)
from detail
where id = a.id)


Alternatively (and functionally equivalent):

select a.id, a.name, b.tran_date
from master join
(select id,max(tran_date) tran_date
from detail
group by id) b
on a.id = b.id


The reason you don't need the DISTINCT keyword is that
the ID column already is unique in table MASTER, and by
selecting only the maximum value for TRAN_DATE (for any
given ID) from the DETAIL table, you will have a
distinct value of ID from table DETAIL as well.
However, the query analyzer won't "know" that, and it
will go through some extra - and relatively costly -
steps to try to make the rows distinct, when they
already are.


Very Nice.
Thanks for the detailed and helpful explanation.
.

Relevant Pages

  • Re: Update and Append Query
    ... UPDATE Master RIGHT JOIN [Transaction] ... "Tom Lake" wrote: ... One query will do both. ...
    (microsoft.public.access.queries)
  • Re: Simply adding two fields
    ... I'm reading a transaction file of widgets delivered and I want to update the ... quantity in the master record, so I do a query joining the master and ... Just redo the calculation whenever you need it, ...
    (microsoft.public.access.queries)
  • Re: VB 2005 Insert records into multiple tables
    ... Maybe I wan't clear in my query. ... use transaction scope (but somehow I am unable to do multiple commands in ... use Adon.net transaction ... VB 2005 Prof, SQL server 2005, Windows application ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: DB API 2.0 and transactions
    ... CURRENT_TIMESTAMP within a transaction should be the same. ... manadate that CURRENT_TIMESTAMP in only evaluated once in each SQL ... transaction-initiating SQL statement takes place. ... src = self.__cnx.source ...
    (comp.lang.python)