Menu
When you use DISTINCT for any query, the result is always ordered by the columns which you have used in the DISTINCT column. Well, that's it. That is the answer. If you use any other column in the ORDER BY close beside the one used in the SELECT statement, you will get an error in the query.
If you want to use a true DISTINCT only list out the column you want to receive distinct values of. If you have multiple columns then all those columns combined make up one distinct record. Note that without an ORDER BY this will return the first 10 records in no particular order.
Well, GROUP BY and DISTINCT have their own use. GROUP BY cannot replace DISTINCT in some situations and DISTINCT cannot take place of GROUP BY. It is as per your choice and situation how you are optimizing both of them and choosing where to use GROUP BY and DISTINCT.
DISTINCT is used to filter unique records out of all records in the table. It removes the duplicate rows. SELECT DISTINCT will always be the same, or faster than a GROUP BY.
I haven't tested it, so the syntax might not be perfect, but what about something like this :
select name, count(*) as frequency
from your_table
group by name
order by count(*) desc
Should give you unique names and the corresponding number of times each name appears in the table, ordered by that number.
You need to use a GROUP BY:
SELECT name, COUNT(*) as frequency
FROM name_table
GROUP BY name
ORDER BY COUNT(*) DESC;
This will GROUP BY name (any non-aggregate columns needs to be named in the GROUP BY clause) and then COUNT the frequency of each name.
If you want only the top 25, you can then proceed to add a LIMIT clause as such:
SELECT name, COUNT(*) as frequency
FROM name_table
GROUP BY name
ORDER BY COUNT(*) DESC
LIMIT 25;
More information about the GROUP BY clause is available in the MySQL Manual:
12.2.8 SELECT Syntax
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With