Re: query question for sql experts

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/01/22
Message-ID: <34c78efb.1089686@www.sigov.si>#1/1

On 21 Jan 1998 21:28:23 GMT, lesliet_at_u.washington.edu (L. Tseng) wrote:

>Sorry, experts. I gave a bad example which may cause some confusion.
>One requirement to achieve this is to use only a pure SQL statement.
>Thanks.
>The following is a new example:
>
>TableA contains:
>
> EmpID GroupID CrDate
>
> 22 4 1/20/98
> 20 3 1/19/98
> 25 4 1/18/98
> 25 6 1/11/98
> 20 5 1/16/98
> 22 3 1/15/98
> ...
> ...
> ...
>Expected query output:
>(Group by EmpID and order by lastest crdate first
>within each EmpID)
> EmpID GroupID CrDate
> 22 4 1/20/98 <- latest in crdate for a emp
> 22 3 1/15/98
> 20 3 1/19/98 <- second latest
> 20 5 1/16/98
> 25 4 1/18/98 <- 3rd latest
> 25 6 1/11/98
> 12 1/17/98 <- 4th latest
> 12 1/14/98
> 30 1/14/98 <- 5th latest
> 30 1/13/98
> 30 1/12/98

This one will do:

SELECT b.EmpID, a.GroupID, a.CrDate
FROM TableA a, (SELECT EmpID, MAX(CrDate) CrDate FROM TableA GROUP BY EmpID) b
WHERE a.EmpID = b.EmpID
ORDER BY b.CrDate DESC, a.CrDate DESC;

This will work if you are on Oracle 7.2 or higher. Until 7.2 Oracle did not suport "inline views" (the SELECT statement in the FROM clause).

If you are using lower release of Oracle Server, you must explicitly create a view and then use it in your select:

CREATE WIEW max_date_by_empid AS
(SELECT EmpID, MAX(CrDate) CrDate FROM TableA GROUP BY EmpID);

SELECT b.EmpID, a.GroupID, a.CrDate
FROM TableA a, max_date_by_empid b
WHERE a.EmpID = b.EmpID
ORDER BY b.CrDate DESC, a.CrDate DESC;

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Jan 22 1998 - 00:00:00 CST