Home » SQL & PL/SQL » SQL & PL/SQL » Return entire records matching aggregated condition?
Return entire records matching aggregated condition? [message #439965] Wed, 20 January 2010 06:12 Go to next message
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 #439969 is a reply to message #439965] Wed, 20 January 2010 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have tried to play around with ROWID and DENSE_RANK, but have not succeeded in getting the results I need.

1/ ROWID is useless here
2/ Post what you tried and we will tell you what is wrong (by the way any of the RANK, DENSE_RANK or ROW_NUMBER can be used, depending if you want to output (for each id) all rows that have the MAX value or only 1).

Post a working Test case: create table and insert statements along with the result you want with these data and we will work with your table and data.

Regards
Michel
Re: Return entire records matching aggregated condition? [message #439970 is a reply to message #439965] Wed, 20 January 2010 06:41 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: Return entire records matching aggregated condition? [message #440057 is a reply to message #439970] Wed, 20 January 2010 14:45 Go to previous message
bambi
Messages: 8
Registered: February 2008
Location: Köpenhamn
Junior Member
The first example is just what I needed to get going. Thank you very much! And of cource it is ROW_NUMBER and not ROWID I should use. Embarassed
Previous Topic: everytime an update is done.. how can i insert?
Next Topic: How to control the loop
Goto Forum:
  


Current Time: Sun Sep 25 02:36:44 CDT 2016

Total time taken to generate the page: 0.09035 seconds