# Thread: How to get the top 3??

1. Registered User
Join Date
Jul 2013
Posts
12

## Unanswered: How to get the top 3??

I have a table with two columns: id and amount.
Example:

ID Amount
A1 200
B2 300
C3 160
D4 210
E5 50
F6 760
G7 89
G7 200
D4 100

Now I want to select the top-3, meaning the three id’s with the largest summation of amount. My result will then be:

ID Amount
B2 300
D4 310
F6 760

How do I do that?

Thanks

2. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Two examples.

Example 2 showed less cost than Example 1.
(Tested on DB2 Express-C 9.7.5 on Windows)

Test data:
Code:
```------------------------------ Commands Entered ------------------------------
WITH
t( id , amount ) AS (
VALUES
( 'A1' , 200 )
, ( 'B2' , 300 )
, ( 'C3' , 160 )
, ( 'D4' , 210 )
, ( 'E5' ,  50 )
, ( 'F6' , 760 )
, ( 'G7' ,  89 )
, ( 'G7' , 200 )
, ( 'D4' , 100 )
)```
Example 1:
Code:
```/*
Total cost	0.06 timerons
CPU cost	89,579.16 instructions
I/O cost	0 I/Os
First row cost	0.06 timerons
Remote communication cost	0 timerons
Remote total cost	0 timerons
*/
SELECT id
, amount
FROM  (SELECT id
, SUM(amount) AS amount
FROM  t
GROUP BY
id
ORDER BY
amount DESC
FETCH FIRST 3 ROWS ONLY
)
ORDER BY
amount ASC
;
------------------------------------------------------------------------------

ID AMOUNT
-- -----------
B2         300
D4         310
F6         760

3 record(s) selected.```

Example 2:
Code:
```/*
Total cost	0.04 timerons
CPU cost	57,931.21 instructions
I/O cost	0 I/Os
First row cost	0.03 timerons
Remote communication cost	0 timerons
Remote total cost	0 timerons
*/
SELECT id
, amount
FROM  (SELECT id
, SUM(amount) AS amount
, ROW_NUMBER()
OVER(ORDER BY SUM(amount) ASC) AS rn
, COUNT(*)
OVER() AS cnt
FROM  t
GROUP BY
id
)
WHERE rn > cnt - 3
;
------------------------------------------------------------------------------

ID AMOUNT
-- -----------
B2         300
D4         310
F6         760

3 record(s) selected.```

3. Registered User
Join Date
Jul 2013
Posts
12
thnx very much

4. Registered User
Join Date
Mar 2003
Posts
280
Originally Posted by tonkuma
Two examples.

Example 2 showed less cost than Example 1.
(Tested on DB2 Express-C 9.7.5 on Windows)

Test data:
Code:
```------------------------------ Commands Entered ------------------------------
WITH
t( id , amount ) AS (
VALUES
( 'A1' , 200 )
, ( 'B2' , 300 )
, ( 'C3' , 160 )
, ( 'D4' , 210 )
, ( 'E5' ,  50 )
, ( 'F6' , 760 )
, ( 'G7' ,  89 )
, ( 'G7' , 200 )
, ( 'D4' , 100 )
)```
Example 1:
Code:
```/*
Total cost	0.06 timerons
CPU cost	89,579.16 instructions
I/O cost	0 I/Os
First row cost	0.06 timerons
Remote communication cost	0 timerons
Remote total cost	0 timerons
*/
SELECT id
, amount
FROM  (SELECT id
, SUM(amount) AS amount
FROM  t
GROUP BY
id
ORDER BY
amount DESC
FETCH FIRST 3 ROWS ONLY
)
ORDER BY
amount ASC
;
------------------------------------------------------------------------------

ID AMOUNT
-- -----------
B2         300
D4         310
F6         760

3 record(s) selected.```

Example 2:
Code:
```/*
Total cost	0.04 timerons
CPU cost	57,931.21 instructions
I/O cost	0 I/Os
First row cost	0.03 timerons
Remote communication cost	0 timerons
Remote total cost	0 timerons
*/
SELECT id
, amount
FROM  (SELECT id
, SUM(amount) AS amount
, ROW_NUMBER()
OVER(ORDER BY SUM(amount) ASC) AS rn
, COUNT(*)
OVER() AS cnt
FROM  t
GROUP BY
id
)
WHERE rn > cnt - 3
;
------------------------------------------------------------------------------

ID AMOUNT
-- -----------
B2         300
D4         310
F6         760

3 record(s) selected.```
Example 2 can be slightly simplified:

Code:
```) SELECT id
, amount
FROM  (SELECT id
, SUM(amount) AS amount
, ROW_NUMBER()
OVER(ORDER BY SUM(amount) desc) AS rn
FROM  t
GROUP BY
id
)
WHERE rn <= 3 order by rn desc```

5. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Lennart,

Fine!
Yours was not only simpler but also less cost(with same test data and environment) than mine.

Here is my test result.
Code:
```/*
Total cost	0.03 timerons
CPU cost	48,602.84 instructions
I/O cost	0 I/Os
First row cost	0.03 timerons
Remote communication cost	0 timerons
Remote total cost	0 timerons
*/
SELECT id
, amount
FROM  (SELECT id
, SUM(amount) AS amount
, ROW_NUMBER()
OVER(ORDER BY SUM(amount) DESC) AS rn
FROM  t
GROUP BY
id
)
WHERE rn <= 3
ORDER BY
rn DESC
;
------------------------------------------------------------------------------

ID AMOUNT
-- -----------
B2         300
D4         310
F6         760

3 record(s) selected.```

6. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Summary of cost.

Code:
```+-------------------------------+---------------+------------------------+
| Query Example                 | Total cost    | CPU cost               |
+-------------------------------+---------------+------------------------+
| FETCH FIRST ...  (Example 1)  | 0.06 timerons | 89,579.16 instructions |
| COUNT(*) OVER()  (Example 2)  | 0.04 timerons | 57,931.21 instructions |
| ORDER BY rn DESC (by Lennart) | 0.03 timerons | 48,602.84 instructions |
+-------------------------------+---------------+------------------------+```

7. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Here is a little complicated but a slightly less cost example.
(I don't know that slight difference of costs have some practical meaning. But, I tried to reduce the cost, mainly for my fun.)

Example 3:
Code:
```/*
Total cost	0.03 timerons
CPU cost	48,230.23 instructions
I/O cost	0 I/Os
First row cost	0.03 timerons
Remote communication cost	0 timerons
Remote total cost	0 timerons
*/
SELECT id , amount
FROM  (SELECT id , amount
, ROW_NUMBER()
OVER(ORDER BY amount DESC) AS rn
FROM  (SELECT id
, SUM(amount)
OVER(PARTITION BY id) AS amount
, RANK()
OVER(PARTITION BY id
ORDER BY amount) AS rank_amount
FROM  t
)
WHERE rank_amount = 1
)
WHERE rn <= 3
ORDER BY
rn DESC
;
------------------------------------------------------------------------------

ID AMOUNT
-- -----------
B2         300
D4         310
F6         760

3 record(s) selected.```

Summary of cost.
Code:
```+-------------------------------+---------------+------------------------+
| Query Example                 | Total cost    | CPU cost               |
+-------------------------------+---------------+------------------------+
| FETCH FIRST ...  (Example 1)  | 0.06 timerons | 89,579.16 instructions |
| COUNT(*) OVER()  (Example 2)  | 0.04 timerons | 57,931.21 instructions |
| ORDER BY rn DESC (by Lennart) | 0.03 timerons | 48,602.84 instructions |
| RANK() OVER(...) (Example 3)  | 0.03 timerons | 48,230.23 instructions |
+-------------------------------+---------------+------------------------+```

8. Registered User
Join Date
Mar 2003
Posts
280
Originally Posted by tonkuma
Here is a little complicated but a slightly less cost example.
(I don't know that slight difference of costs have some practical meaning. But, I tried to reduce the cost, mainly for my fun.)
[...]
Summary of cost.
Code:
```+-------------------------------+---------------+------------------------+
| Query Example                 | Total cost    | CPU cost               |
+-------------------------------+---------------+------------------------+
| FETCH FIRST ...  (Example 1)  | 0.06 timerons | 89,579.16 instructions |
| COUNT(*) OVER()  (Example 2)  | 0.04 timerons | 57,931.21 instructions |
| ORDER BY rn DESC (by Lennart) | 0.03 timerons | 48,602.84 instructions |
| RANK() OVER(...) (Example 3)  | 0.03 timerons | 48,230.23 instructions |
+-------------------------------+---------------+------------------------+```
That's interesting, I would have guessed that the extra nesting and olap functions would have caused additional cost, but looking at the plan they appear to be very similar, but the last example is a teeny wheeny cheaper. Example 2:

Code:
```        Total Cost:             0.00746664
Query Degree:           1

Rows
RETURN
(   1)
Cost
I/O
|
3
TBSCAN
(   2)
0.00746664
0
|
3
SORT
(   3)
0.00711556
0
|
3
TBSCAN
(   4)
0.00551156
0
|
3
SORT
(   5)
0.00516048
0
|
9
GRPBY
(   6)
0.00324649
0
|
9
TBSCAN
(   7)
0.00285718
0
|
9
SORT
(   8)
0.00227583
0
|
9
TBSCAN
(   9)
5.52643e-05
0
|
9
TABFNC: SYSIBM
GENROW
Q1```

Last example:

Code:
```        Total Cost:             0.00740391
Query Degree:           1

Rows
RETURN
(   1)
Cost
I/O
|
0.36
TBSCAN
(   2)
0.00740391
0
|
0.36
SORT
(   3)
0.00715415
0
|
0.36
TBSCAN
(   4)
0.00603036
0
|
0.36
SORT
(   5)
0.0057806
0
|
0.36
FILTER
(   6)
0.00534572
0
|
9
TBSCAN
(   7)
0.00311995
0
|
9
SORT
(   8)
0.00253861
0
|
9
TBSCAN
(   9)
5.52643e-05
0
|
9
TABFNC: SYSIBM
GENROW
Q1```
For the fun of it

9. Registered User
Join Date
Nov 2011
Posts
334
hi,
I think maybe db2 optimizer underestimate the cost of last example.
I notice the filter step:
0.36
FILTER
( 6)
0.00534572

the estimated card 0.36 is not correct obviously.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•