CODEWITHSUNDEEP
mysqlsqlsql-server
senjizu
senjizu

Reputation: 103

select top 5 group by and order by

I have a table like this:

CITY                            QNT EXP RATE
LONDON                          60  6   900
LONDON                          35  8   337
LONDON                          24  6   300
LONDON                          22  6   266
BIRMINGHAM                      22  6   266
NEWYORK                         69  19  263
LONDON                          21  6   250
ROME                            24  7   242
BIRMINGHAM                      24  7   242
BIRMINGHAM                      24  7   242
LONDON                          20  6   233
BIRMINGHAM                      23  7   228
STUTTGART                       29  9   222
LONDON                          19  6   216
STUTTGART                       25  8   212
PARIS                           31  10  210
STUTTGART                       34  11  209
STUTTGART                       34  11  209
BIRMINGHAM                      18  6   200
BIRMINGHAM                      18  6   200
NEWYORK                         18  6   200
BIRMINGHAM                      17  6   183
LONDON                          19  7   171
MUNICH                          16  6   166
PARIS                           21  8   162
STUTTGART                       39  15  160
BARCELONA                       18  7   157
LONDON                          18  7   157
ROME                            33  13  153
BARCELONA                       15  6   150
PARIS                           25  10  150
ROME                            20  8   150
PARIS                           25  10  150
ROME                            20  8   150
LONDON                          15  6   150
MUNICH                          15  6   150
BIRMINGHAM                      15  6   150
NEWYORK                         15  6   150
LONDON                          17  7   142
MUNICH                          17  7   142

Here is my sql command:

select CITY, QNT, EXP, (QNT-EXP)*100/EXP as RATE
from tbl_city
order by RATE desc

I want to group by city these results. But I couldn't do it. I want the top5 line to change most.

Result should be like that:

LONDON                          60  6   900
BIRMINGHAM                      22  6   266
NEWYORK                         69  19  263
ROME                            24  7   242
STUTTGART                       29  9   222

Upvotes: 2

Views: 1348

Answers (3)

gengencera
gengencera

Reputation: 426

This is from MySQL 5.6.

select CITY, QNT, EXP, (QNT-EXP)*100/EXP as RATE
from tbl_city GROUP BY CITY
order by RATE desc LIMIT 5

Tested in this link: http://www.sqlfiddle.com/#!9/3f1ea1/1

This is from MS SQL Server 2017

select TOP 5 f.CITY, f.QNT, f.EXP, x.RATE
from (
select  CITY, MAX((QNT-EXP)*100/EXP) as RATE
from tbl_city GROUP BY CITY
) as x inner join tbl_city as f on f.CITY = x.CITY 
and ((f.QNT-f.EXP)*100/f.EXP) = x.RATE
ORDER BY RATE DESC;

Tested in this link: http://www.sqlfiddle.com/#!18/7b8da/61

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

I don't know what is logic behind the specified formula but this does what you want

select top 5 * from (
    select top(1) with ties city, qnt, exp, (qnt-exp)*100/exp as rate 
    from tbl_city 
    order by row_number() over (partition by city order by (qnt-exp)*100/exp desc)
)t
order by rate desc

However, top(1) with ties with analytical functions are available in SQL Server.

Upvotes: 0

James Z
James Z

Reputation: 12318

Maybe you want something like this?

select top 5 CITY, QNT, EXP, RATE 
from (
    select *, row_number() over (partition by CITY order by RATE desc) AS RN
    from (
        select CITY, QNT, EXP, (QNT-EXP)*100/EXP as RATE
        from tbl_city

    ) X
) Y
where RN = 1
order by RATE desc

I didn't test this, but it should take first the row for the city with biggest rate, and then take top 5 rows so that that the same city is not duplicated

Upvotes: 2

Related Questions