SQL Select Statement

Hai,
You can use the following select for the latest updated records.
-----
select CustCode,ProjCode,ExtCode,max(Notes),max(DateNoteEntered)
from table1
group by CustCode,ProjCode,ExtCode
------

Regards:
Vikas G

"salih" <salih.ayan@UKAutomation.com> wrote:
>
>Dear ALL
>I have one table called Table1 and I have got 5 fields in it
>like Custcode ProjCode ExtCode DateNoteEntered Notes
> AN 01 01 12/12/01 Description About Job1
> AN 01 02 02/02/01 Description About Job2
> AZ 03 01 01/01/01 Description About Job
> .. .. .. ...... ........................
>and So on
>I want to write an SQL Statement
>to return all the CustCode,ProjCode,ExtCode,Notes,DateNoteEntered
>which is only the last entered ones for each CustCode,ProjCode and ExtCode
>
>Many Thanks in Advance
>salih
>

Hi Hoe, Vikas G, Hai.

Vikas Said:
>> Hai,
>> You can use the following select for the latest updated records.
>> -----
>> select CustCode,ProjCode,ExtCode,max(Notes),max(DateNoteEntered)
>> from table1
>> group by CustCode,ProjCode,ExtCode
>> ------
That *doesn't* work. See below.

Joe said:
>What makes you think max(Notes) and max(DateNoteEntered) will
>always, *always* come from the same record? If max() works
>the way you seem to think, why wouldn't it return the date of
>the "largest" note instead?

There are several ways to get what you're after. The SQL way is to issue
a query like this...

SELECT o.CustCode,o.ProjCode,o.ExtCode,o.Notes,o.DateNoteEntered
FROM table1 o
WHERE o.DateNoteEntered =
(SELECT MAX(i.DateNoteEntered)
FROM table1 i
WHERE i.custCode=o.CustCode AND i.ProjCode = o.ProjCode
AND i.extCode=o.extCode)

(note the difference; we are choosing the Notes that "came in the same record"
as the highest DateNotEntered...)...or like this...

SELECT o.CustCode,o.ProjCode,o.ExtCode,o.Notes,o.DateNoteEntered
FROM table1 o
WHERE NOT EXISTS
(SELECT i.DateNoteEntered
FROM table1 i
WHERE i.custCode=o.CustCode AND i.ProjCode = o.ProjCode
AND i.extCode=o.extCode AND i.DateNoteEntered > o.DateNoteEntered)

However, depending on your database vendor either or both of those might
not work. It's standard SQL but correlated subqueries aren't always supported
properly. Also bear in mind that if there are multiple records with the
*same* values for all of CustCode, ProjCode, ExtCode, DateNoteEntered it
*will* return multiple answers with the same (CustCode,ProjCode,ExtCode).
Them's the breaks.

Lots of people will tell you the second query I gave above will be faster.
Possibly But I think the first one is clearer. Take your pick. There
are faster ways to get what you want (particularly if the table doesn't have
an index on (custCode, projCode, extCode, dateNoteEntered)... but they require
PL/SQL or filtering the results of a...

SELECT o.CustCode,o.ProjCode,o.ExtCode,o.Notes,o.DateNoteEntered
FROM table1 o
ORDER BY o.CustCode,o.ProjCode,o.ExtCode,o.DateNoteEntered

...query with additional non-SQL code.

Hope the above helps,
Seeya,
James