How get TOP from query?

Question

SELECT
    user_id,
    count(*) total,
    sum(case when type = 'yes' then 1 else 0 end) as type_1,
    sum(case when type = 'no' then 1 else 0 end) as type_2
FROM history
GROUP by user_id

How get TOP 100 rows from this query which have max count type = 'yes' ?

In MySql they called it Limit. e.g. SELECT * FROM table LIMIT 100; — derloopkat, Apr 01 '16 at 13:49

rharvey · Answer 1 · 2016-04-01T14:36:31.713

1

You can use LIMIT to limit the number of results and use an ORDER BY to order it so the results are in descending order of total.

SELECT
    user_id,
    count(*) total,
    sum(case when type = 'yes' then 1 else 0 end) as type_1,
    sum(case when type = 'no' then 1 else 0 end) as type_2
FROM history
GROUP by user_id
ORDER BY type_1 DESC
LIMIT 100

edited Apr 01 '16 at 14:36

answered Apr 01 '16 at 13:49

rharvey

1,987
1
28
23

Not sure why this was upvoted as type_2 would always be 0 with that `WHERE` clause. And you are ordering by the wrong column. – Kilgore Apr 01 '16 at 14:12
I've updated my answer – rharvey Apr 01 '16 at 14:37

score 0 · Answer 2 · answered Apr 01 '16 at 13:53

You should add WHERE statement to your query to add conditional statement Also, I can see that you are using mysql, so that means you should order your data by argument descending and limit result to 100

    SELECT
        user_id,
        count(*) total,
        sum(case when type = 'yes' then 1 else 0 end) as type_1,
        sum(case when type = 'no' then 1 else 0 end) as type_2
    FROM history
    WHERE type='yes'
    GROUP by user_id
    ORDER BY total DESC
    LIMIT 100

Sorry, I did not see that answer :( – Alexander Capone Apr 01 '16 at 13:56 — Alexander Capone, Apr 01 '16 at 13:56

score 0 · Answer 3 · answered Apr 01 '16 at 14:06

0

SELECT
    user_id,
    count(*) total,
    sum(case when type = 'yes' then 1 else 0 end) as type_1,
    sum(case when type = 'no' then 1 else 0 end) as type_2
FROM history
GROUP by user_id
ORDER by type_1 DESC
LIMIT 100

answered Apr 01 '16 at 14:06

user6129461

33
5

While this code may answer the question, providing additional context regarding _why_ and/or _how_ it answers the question would significantly improve its long-term value. Please [edit] your answer to add some explanation. – Toby Speight Apr 01 '16 at 15:06
@TobySpeight im not understand, tell me please what some explanation need add? – user6129461 Apr 02 '16 at 08:01

How get TOP from query?

3 Answers3