-3

I want to make a selection in my MySQL database where I have stored numerical values in a specific column.

I have seen some websites that show 10 ten based on some criteria, and I've been wondering if it will be possible to make a selection based on numerical values.

I have my database like so:

| id | name      | pts |
+----+-----------+-----+
| 1  | Brad      |  3  |
| 2  | Jane      |  8  |
| 3  | Jones     |  10 |
| 4  | Paty      |  15 |
| 5  | Sammy     |  2  |

Now my question is, how do I make a query that selects only the top 3 users, based on the pts, such that it returns the result as:

1st Position = Paty => 15pts
2nd Position = Jones => 10pts
3rd Position = Jane => 8pts

?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Samuel Asor
  • 480
  • 8
  • 25
  • Have you tried any query? This has been asked multiple times around here and you can find an answer easily. What have you tried before posting? This is an example: https://stackoverflow.com/questions/5422488/select-first-10-distinct-rows-in-mysql, it has a little different purpose, but It does the same. – Paul Karam Jul 27 '17 at 12:47
  • Forgive me, but I couldn't find any. Can you send a link to duplicates? – Samuel Asor Jul 27 '17 at 12:48
  • Possible duplicate of [Mysql: Select top N max values?](https://stackoverflow.com/questions/19827388/mysql-select-top-n-max-values) – Paul Karam Jul 27 '17 at 12:51
  • Any introductory book or tutorial would be useful at this point. – Strawberry Jul 27 '17 at 12:57
  • what if people have the same score? – qwertzman Aug 22 '17 at 12:16
  • Then the query will return the results of the people with the same result. I haven't thought about this tho, but I believe something else will be used to determine the actual positions. – Samuel Asor Aug 23 '17 at 11:29

4 Answers4

3

try this :

SELECT * FROM tablename ORDER BY pts desc limit 3

Arun pandian M
  • 862
  • 10
  • 17
1

Your query should use LIMIT to get the top results:

SELECT id, name, points FROM table ORDER BY pts DESC LIMIT 3

shoul do the trick.

Order by will order the table from the highest to the lowest and limit will tell mysql to get only the first three results

You can find more on this topic here for example. And this is a question very close to your

Lelio Faieta
  • 6,457
  • 7
  • 40
  • 74
1

you can use this query 

SELECT CONCAT(`id` , 'Postion = ' , `name` , '=>' ,`pts` , 'pts' ) AS result FROM table ORDER BY pts DESC LIMIT 3
vjy tiwari
  • 843
  • 1
  • 5
  • 17
1

This Question I seen many places anyway the query is.

select top 3 * from tablename order by cols_name desc

This Query brings top 3 row with highest values based on your column.

vishal
  • 116
  • 10