In MariaDB how do I select the top 10 rows from a table?

mysql sql select mariadb top-n
Caffeinated picture Caffeinated · Nov 25, 2014 · Viewed 36.9k times · Source

I just read online that MariaDB (which SQLZoo uses), is based on MySQL. So I thought that I can use ROW_NUMBER() function

However, when I try this function in SQLZoo :

SELECT * FROM ( 
  SELECT  * FROM route
) TEST7
WHERE ROW_NUMBER()  < 10

then I get this error :

Error: FUNCTION gisq.ROW_NUMBER does not exist

Answer

Mureinik picture Mureinik · Nov 25, 2014

You can use the limit clause:

SELECT * FROM route LIMIT 10

This can, of course, be used on a sorted query too:

SELECT * FROM route ORDER BY some_field LIMIT 10

Related questions

SQL query return data from multiple tables

I would like to know the following: how to get data from multiple tables in my database? what types of methods are there to do this? what are joins and unions and how are they different from one another? When …

Read More
MySQL SELECT only not null values

Is it possible to do a select statement that takes only NOT NULL values? Right now I am using this: SELECT * FROM table And then I have to filter out the null values with a php loop. Is there a …

Read More
MySQL Select Query - Get only first 10 characters of a value

Ok, so here is the issue. I have a table with some columns and 'subject' is one of the columns. I need to get the first 10 letters from the 'subject' field no matter the 'subject' field contains a string with 100 …

Read More