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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Date Difference
Next Topic: File permissions
Goto Forum:
  


Current Time: Fri Dec 09 07:54:00 CST 2016

Total time taken to generate the page: 0.25515 seconds