How to find the top 5 values using max() in sql

Question

I want the top 5 products from a table

If i use this

SELECT MAX(ProductCode) AS ProductName 
FROM OrderDetails

I get only one result I want the top 5 result

Please provide sample data, desired results, and a definition of what "most ordered" means. I have removed the incompatible database tags. — Gordon Linoff, Feb 07 '19 at 11:46
I don't understand how a column named "ProductCode" is any indication on how often that product was ordered. — a_horse_with_no_name, Feb 07 '19 at 11:49
Perhaps it should be top 5 ordered by `count(distinct productCode)`, but without more information, any attempt to answer this question is nothing but a guess. — Zohar Peled, Feb 07 '19 at 11:50
Please read the first paragraph of the [sql tag info](https://stackoverflow.com/tags/sql/info) and [edit] your question accordingly. — Zohar Peled, Feb 07 '19 at 11:52

score 2 · Answer 1 · edited Feb 07 '19 at 12:16

2

Try this for MySQl

SELECT ProductCode FROM OrderDetails
ORDER BY ProductCode Desc 
LIMIT 5

edited Feb 07 '19 at 12:16

PranshuKhandal

661
6
18

answered Feb 07 '19 at 11:46

Derviş Kayımbaşıoğlu

26,360
3
47
64

my product code is varchar by using this query may i get the exact reult that i want? – Sooraj Thekkepatt Feb 07 '19 at 11:52
in this query you seperated OrderDetails by a space, so i joined them – PranshuKhandal Feb 07 '19 at 11:53
its not what i wanted.. from my table i want to figure out the top 5 products that orderd by the customers.. its orderdetail table its contain products orderd by diffrent customers only i can figure out by the largest product from the list by its count – Sooraj Thekkepatt Feb 07 '19 at 12:04
I gave you solution according to your question. But you are asking something else, However I still couldn't undestand what you need. Please provider sample data and expected output. – Derviş Kayımbaşıoğlu Feb 07 '19 at 12:20

score 1 · Accepted Answer · answered Feb 07 '19 at 12:45

1

solved..by using this query

SELECT TOP 5 ProductName, COUNT(ProductName) AS value_occurrence FROM OrderDetails GROUP BY ProductName ORDER BY value_occurrence DESC

answered Feb 07 '19 at 12:45

Sooraj Thekkepatt

53
8

score 0 · Answer 3 · answered Feb 07 '19 at 12:06

it depends on what exactly you need top 5 based on .. it can be sorting by what it can be like numbers first and then the characters one ..
as you said it is varchar then please have a look at this question and limit them with how many u need.

How do I sort a VARCHAR column in SQL server that contains numbers?

Hope this helps :)

How to find the top 5 values using max() in sql

3 Answers3