[askdba] Re: SQL Help
- From: "Anthony Molinaro" <amolinaro@xxxxxxxx>
- To: <askdba@xxxxxxxxxxxxx>
- Date: Tue, 2 Nov 2004 16:57:41 -0500
Rick,
there's a lot of ways to do this.=20
Here's one:
SQL> select job, hiredate from emp order by 1, 2 desc;
JOB HIREDATE
--------- ---------
ANALYST 09-DEC-82
ANALYST 03-DEC-81
CLERK 12-JAN-83
CLERK 23-JAN-82
CLERK 03-DEC-81
CLERK 17-DEC-80
MANAGER 09-JUN-81
MANAGER 01-MAY-81
MANAGER 02-APR-81
PRESIDENT 17-NOV-81
SALESMAN 28-SEP-81
SALESMAN 08-SEP-81
SALESMAN 22-FEB-81
SALESMAN 20-FEB-81
14 rows selected.
SQL> select x.job, x.max_date,
2 (select max(y.hiredate) from emp y where y.job=3Dx.job and
y.hiredate < x.max_date) next_date
3 from (
4 select job, max(hiredate) max_date
5 from emp
6 group by job
7 ) x;
JOB MAX_DATE NEXT_DATE
--------- --------- ---------
ANALYST 09-DEC-82 03-DEC-81
CLERK 12-JAN-83 23-JAN-82
MANAGER 09-JUN-81 01-MAY-81
PRESIDENT 17-NOV-81
SALESMAN 28-SEP-81 08-SEP-81
I'm grouping by one column, you will be grouping by two, but the idea is
the same.
I'm sure you'll get quite a few followups to this.
So, test them all out and see what's best for you.
Hope that helps,
- ant
-----Original Message-----
From: Rick_Cale@xxxxxxxxxxxxxx [mailto:Rick_Cale@xxxxxxxxxxxxxx]=20
Sent: Tuesday, November 02, 2004 4:44 PM
To: askdba@xxxxxxxxxxxxx
Subject: [askdba] SQL Help
Hi,
I have need some help on an sql statement.
I have a table(tab1) field1,field2,field3. Field3 is a date field.
There are more that one date for field1, field2 combination.
I want to be able to get the max(field3) for field1,field2 which is easy
enough. But I also want to get the next highest field3 date for the
same field1,field2
I would like the output to be
field1,field2,Max(field3),NextHighest(field3)
ex.
1, 2, 11/02/2004, 10/30,2004
4, 5, 09/01/2004, 08/13/2004
Sample table data for the above may be
1 2 11/02/2004
1 2 10/30/2004
1 2 09/02/2002
1 2 06/06/2001
4 5 09/01/2004
4 5 08/13/2004
4 5 02/02/1999
Any ideas how I can accomplish the above? Oracle 8i SE
Rick Cale
Database Administrator
Team Health, Inc.
865-293-5425
Rick_Cale@xxxxxxxxxxxxxx
Other related posts:
