Home » SQL & PL/SQL » SQL & PL/SQL » Problems reducing a selection of grouped records
| Problems reducing a selection of grouped records [message #145056] |
Mon, 31 October 2005 11:54  |
jlshipman
Messages: 8
Registered: October 2005
|
Junior Member |
|
|
Hi,
I am trying to get a list of top three scores in a particular event in a particular competition from a table that holds a list of this information.
The following code produces a complete list of SCORES ordered by TEAM, COMPETITION and EVENT (about 1200 records).
SELECT c.tname TEAM,
a.cname COMPETITION,
a.ename EVENT,
a.j11 SCORE
FROM attempt a,
competitor c
WHERE a.igan = c.igan
ORDER BY c.tname,
a.cname,
a.ename,
a.j11 DESC
I have tried the following code to narrow the selection down to the top three scores
at a competition by a team for an event
SELECT c.tname,
a.cname,
a.ename,
a.j11
FROM attempt a,
competitor c
WHERE a.igan = c.igan
AND ROWNUM < 4
ORDER BY c.tname,
a.cname,
a.ename,
a.j11 DESC
What I get is 3 records
TNAME CNAME ENAME J11
Spinners August Hbar 8.394
Spinners August Pbar 8.73
Spinners August Phorse 6.955
what I want is
TNAME CNAME ENAME J11
Spinners August Hbar 8.394
Spinners August Hbar 8.73
Spinners August Hbar 6.955
Spinners August Pbar 8.394
Spinners August Pbar 8.73
Spinners August Pbar 6.955
.........
(about 600 records)
|
|
|
|
| Re: Problems reducing a selection of grouped records [message #145057 is a reply to message #145056] |
Mon, 31 October 2005 12:09  |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
Example - 5 top salaries from emp:
SQL> select ename, sal from emp order by 2 desc;
ENAME SAL
---------- ----------
KING 2500
SCOTT 1500
FORD 1500
JONES 1487,5
BLAKE 1425
CLARK 1225
ALLEN 900
TURNER 750
WARD 725
MILLER 650
MARTIN 625
ENAME SAL
---------- ----------
JAMES 560
ADAMS 550
SMITH 500
14 rows selected.
SQL> select ename, sal from
2 (
3 select ename, sal, dense_rank() over(order by sal desc) rnk
4 from emp
5 )
6 where rnk <= 5;
ENAME SAL
---------- ----------
KING 2500
SCOTT 1500
FORD 1500
JONES 1487,5
BLAKE 1425
CLARK 1225
6 rows selected.
In your case:
SELECT TEAM, COMPETITION, EVENT, SCORE
FROM (
SELECT c.tname TEAM,
a.cname COMPETITION,
a.ename EVENT,
a.j11 SCORE,
dense_rank() over(order by a.j11 desc) rnk
FROM attempt a,
competitor c
WHERE a.igan = c.igan
)
WHERE rnk <= 3
Rgds.
|
|
|
|
| Re: Problems reducing a selection of grouped records [message #145091 is a reply to message #145057] |
Mon, 31 October 2005 19:26 |
jlshipman
Messages: 8
Registered: October 2005
|
Junior Member |
|
|
After viewing Dmitry Nikiforov response, I tested it out. It led the following result
TEAM COMPETITION EVENT SCORE
June Rings 9.994
Spinners May Floor 9.992
Ninjas June Rings 9.992
Hurricanes June Hbar 9.99
But after viewing the code, I tried the following:
SELECT TEAM, COMPETITION, EVENT, SCORE
FROM
(
SELECT rank() over (
partition by
c.tname,
a.cname,
a.ename
order by a.j11 desc
) rnk,
c.tname TEAM,
a.cname COMPETITION,
a.ename EVENT,
a.j11 SCORE
FROM attempt a,
competitor c
WHERE a.igan = c.igan
)
WHERE rnk <= 3;
this produced the desired results
TEAM COMPETITION EVENT SCORE
Hurricanes August Floor 7.999
Hurricanes August Floor 7.907
Hurricanes August Floor 7.527
Hurricanes August Hbar 9.822
Hurricanes August Hbar 9.653
Hurricanes August Hbar 9.216
Hurricanes August Pbar 9.909
Hurricanes August Pbar 9.439
Hurricanes August Pbar 9.304
.........
I am not real confident in the use of over and partition. Could someone give me a description of there usage and purpose
|
|
|
|
| Re: Problems reducing a selection of grouped records [message #145117 is a reply to message #145091] |
Mon, 31 October 2005 23:38 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I defy anyone to adequately explain analytic functions within the confines of a forum; they are pretty complex. Take a look at the Functions chapter of the Oracle SQL Reference manual, and go to the section on Analytic functions. It shows some examples that are helpful.
Having said that, I'll give it a go:
Aggregate functions (SUM, MAX, MIN, COUNT, AVG) - in combination with a GROUP BY clause - combine groups of rows into a single row for each group. Analytic functions similarly group rows, but they are not combined - ie. the number of rows returned is not affected by the anlaytic function. The purpose of the analytic function is to allow each row to look at its neighbors.
The OVER keyword simply tells Oracle that the preceding function(eg. RANK(), DENSE_RANK(), etc) is to be treated as an Analytic Function, and that what follows is the analytic clause of the Analytic function.
The Analytic clause has a few parts: PARTITION BY, ORDER BY, and ROWS/RANGE (windowing) clause.
- PARTITION BY tells Oracle to break up the result rows of the SQL into partitions. Every different combination of the PARTITION BY columns are treated separately by the analytic function. So, when the analytic function starts looking at neighnoring rows, it will not look beyond the boundaries of the partition.
- ORDER BY tells Oracle how to order the rows within each partition.
- The windowing clause allows you to apply the function over a reduced population of rows within the partition. So rather than using every row in the partition to derive the function value, only the immediate neighbors are used.
In your example, the SQL results are broken into separate partitions by TEAM, COMPETITION, and EVENT. Within each partition (ie. for each distinct combination of TEAM, COMPETITION, and EVENT, the rows are ordered by descending SCORE. The RANK then numbers the rows in each partition starting from 1 in order of descending SCORE. The WHERE clause then filters all but the top 3 results in each partition.
______________
Ross Leishman
|
|
|
|
| Re: Problems reducing a selection of grouped records [message #145128 is a reply to message #145091] |
Tue, 01 November 2005 00:41 |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
You should understand the difference between RANK() and DENSE_RANK() functions. If you talk about records having 3 greatest scores you should use dense_rank(), not rank(), if you have the same scores among data. See the difference below:
SQL> select ename, sal from
2 (
3 select ename, sal, dense_rank() over(order by sal desc) rnk
4 from emp
5 )
6 where rnk <= 5;
ENAME SAL
---------- ----------
KING 2500
SCOTT 1500
FORD 1500
JONES 1487,5
BLAKE 1425
CLARK 1225
6 rows selected.
SQL> select ename, sal from
2 (
3 select ename, sal, rank() over(order by sal desc) rnk
4 from emp
5 )
6 where rnk <= 5;
ENAME SAL
---------- ----------
KING 2500
SCOTT 1500
FORD 1500
JONES 1487,5
BLAKE 1425
Rgds.
|
|
|
|
| Re: Problems reducing a selection of grouped records [message #277346 is a reply to message #145117] |
Mon, 29 October 2007 12:33 |
marian21
Messages: 1
Registered: March 2005
Location: Goleta, California
|
Junior Member |
|
|
|
Just wanted to thank you for introducing me to analytic functions rank and dense_rank. Your explanation told me iummediately that that was the solution to our problem (I work for an educational institution and we need to number section offerings within each course in our web catalog). Thanks for sharing your expertise.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 01:12:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
