SQL Group By with an Order By

0 votes

There is a table which has tags and I want to get the maximum count of tags from the list.

Sample data looks like this

id (1) tag ('hello')
id (2) tag ('great')
id (3) tag ('hello')

using

SELECT COUNT(*), `Tag` from `images-tags`
GROUP BY `Tag`

This code is giving me the data I'm looking for but I want to organize it in such a way that the highest tag counts are first, and limit it to only the first 20

Feb 10 in Database by Neha
• 8,940 points
54 views

1 answer to this question.

0 votes

In any version of MySQL, you can use the aggregate in the SELECT list, and use ORDER BY and LIMIT the number of records :

SELECT COUNT(id) AS theCount, `Tag` from `images-tags`
GROUP BY `Tag`
ORDER BY theCount DESC
LIMIT 20

You were missing the ORDER BY statement which caused the error.

answered Feb 10 by Vaani
• 7,020 points

Related Questions In Database

0 votes
1 answer

SQL Server: PARTITION BY vs GROUP BY

To understand the differences between GROUP BY ...READ MORE

answered Feb 9 in Database by Neha
• 8,940 points
815 views
0 votes
0 answers

Execution sequence of Group By, Having and Where clause in SQL Server?

I'm just not sure how a SQL ...READ MORE

Aug 19 in Database by Kithuzzz
• 20,660 points
80 views
0 votes
0 answers

What is the purpose of Order By 1 in SQL select statement?

I discovered numerous views with an order ...READ MORE

Aug 19 in Database by Kithuzzz
• 20,660 points
68 views
0 votes
0 answers

How to group by month from Date field using sql

How can I only group by month ...READ MORE

Aug 20 in Database by Kithuzzz
• 20,660 points
294 views
0 votes
1 answer
0 votes
1 answer

How to change the default value and to alter a column in sql?

Hi, You can try this: ALTER TABLE foobar_data CHANGE ...READ MORE

answered Jun 24, 2019 in Big Data Hadoop by Gitika
• 65,890 points
545 views
0 votes
1 answer

What is an index in SQL?

An index is used to speed up ...READ MORE

answered Feb 3 in Database by Vaani
• 7,020 points
117 views
0 votes
1 answer

Ordering by the order of values in a SQL IN() clause

Use MySQL's FIELD() function: SELECT name, description, ... FROM ... WHERE id ...READ MORE

answered Feb 4 in Database by Neha
• 8,940 points
1,562 views
0 votes
1 answer

Number of rows affected by an UPDATE in PL/SQL

You can try using the following query: SELECT ...READ MORE

answered Feb 14 in Database by Vaani
• 7,020 points
358 views
0 votes
1 answer

two back slash in SQL procedure with Regular expression

It's the escape prefix in regular expressions: ...READ MORE

answered Feb 2 in Database by Vaani
• 7,020 points
742 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP