| Return entire records matching aggregated condition? [message #439965] |
Wed, 20 January 2010 06:12  |
bambi
Messages: 8
Registered: February 2008
Location: Köpenhamn
|
Junior Member |
|
|
The problem sounds simple, but I can't figure out how to solve it in SQL:
For each id I want the record with the highest balance.
The table could look something like this (there is about 25 fields in each row):
row id name balance account
1 100 Peter $ 475 500125
2 103 Bob $ 0 500063
3 103 Bob $ 0 500498
4 103 Bob $ 25669 500125
5 103 Bob $ 24330 500017
6 117 John $ 1024 500244
Returning the highest balance for each id is trivial, but I want to return all the fields in the relevant records. In the example above it would be the entire rows 1, 4 and 6.
Only one record is to be returned for each id. If two or more records exist with the same id and balance, it doesn't matter which of the records is returned.
I have tried to play around with ROWID and DENSE_RANK, but have not succeeded in getting the results I need.
|
|
|
|
|
|
| Re: Return entire records matching aggregated condition? [message #439970 is a reply to message #439965] |
Wed, 20 January 2010 06:41  |
 |
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
For instance, get the employees with max salary in each department:
SQL> select deptno, empno, ename, sal, job
2 from ( select deptno, empno, ename, sal, job,
3 row_number() over (partition by deptno order by sal desc) rn
4 from emp )
5 where rn = 1
6 order by deptno
7 /
DEPTNO EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------- ---------
10 7839 KING 5000 PRESIDENT
20 7788 SCOTT 3000 ANALYST
30 7698 BLAKE 2850 MANAGER
3 rows selected.
SQL> select deptno, empno, ename, sal, job
2 from ( select deptno, empno, ename, sal, job,
3 rank() over (partition by deptno order by sal desc) rk
4 from emp )
5 where rk = 1
6 order by deptno
7 /
DEPTNO EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------- ---------
10 7839 KING 5000 PRESIDENT
20 7788 SCOTT 3000 ANALYST
20 7902 FORD 3000 ANALYST
30 7698 BLAKE 2850 MANAGER
4 rows selected.
Regards
Michel
[Updated on: Wed, 20 January 2010 06:42] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
