How do i select a single record in mysql?

Columns in SQL don't have a defined 'order'. Database systems generally keep track of an order for display purposes, but it doesn't make sense to ask a database to select a column by number. You need to know the column's name in order to query its contents.

The same thing goes for the primary key (which, incidentally, may not be just a single column). You have to know which column it is, and what that column is named, in order to execute a query.

If you don't know these things, or need to figure them out dynamically, then

DESCRIBE tablename;

will tell you the names of each column, and whether it is part of the primary key or not. It will return a table that you can read, like any other result.


If you want to select a single row on the basis of primary key, use the WHERE clause. The syntax is as follows −

SELECT * FROM yourTableName WHERE yourPrimaryKeyColumnName = someValue;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table selectWithPrimaryKey
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(20),
   -> Age int,
   -> Marks int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.78 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into selectWithPrimaryKey(Name,Age,Marks) values('Larry',24,98);
Query OK, 1 row affected (0.15 sec)

mysql> insert into selectWithPrimaryKey(Name,Age,Marks) values('John',23,89);
Query OK, 1 row affected (0.21 sec)

mysql> insert into selectWithPrimaryKey(Name,Age,Marks) values('Mike',21,85);
Query OK, 1 row affected (0.18 sec)

mysql> insert into selectWithPrimaryKey(Name,Age,Marks) values('Sam',26,56);
Query OK, 1 row affected (0.18 sec)

mysql> insert into selectWithPrimaryKey(Name,Age,Marks) values('Carol',21,59);
Query OK, 1 row affected (0.18 sec)

mysql> insert into selectWithPrimaryKey(Name,Age,Marks) values('Bob',20,91);
Query OK, 1 row affected (0.21 sec)

mysql> insert into selectWithPrimaryKey(Name,Age,Marks) values('David',28,93);
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from selectWithPrimaryKey;

The following is the output −

+----+-------+------+-------+
| Id | Name  | Age  | Marks |
+----+-------+------+-------+
|  1 | Larry |   24 |    98 |
|  2 | John  |   23 |    89 |
|  3 | Mike  |   21 |    85 |
|  4 | Sam   |   26 |    56 |
|  5 | Carol |   21 |    59 |
|  6 | Bob   |   20 |    91 |
|  7 | David |   28 |    93 |
+----+-------+------+-------+
7 rows in set (0.00 sec)

The following is the query to select a single row from the table −

mysql> select *from selectWithPrimaryKey where Id = 6;

Here is the output −

+----+------+------+-------+
| Id | Name | Age  | Marks |
+----+------+------+-------+
| 6  | Bob  | 20   | 91    |
+----+------+------+-------+
1 row in set (0.00 sec)

How do i select a single record in mysql?

Updated on 30-Jul-2019 22:30:24

  • Related Questions & Answers
  • MySQL LIMIT to select a single row
  • Sum values of a single row in MySQL?
  • Selecting only a single field from MongoDB?
  • Update multiple columns of a single row MySQL?
  • Get only a single value from a specific MySQL row?
  • Get the Average of Average in a single MySQL row?
  • MySQL query to return all items in a single row
  • Selecting a value in custom order from another column in a MySQL table with a single query
  • Creating and Selecting a MySQL Database
  • Return only a single row from duplicate rows with MySQL
  • How can we delete a single row from a MySQL table?
  • Concatenate multiple rows and columns in a single row with MySQL
  • Comparing two columns in a single MySQL query to get one row?
  • Single-Row Keyboard in python
  • Selecting Random Result from MySQL?

To return only the first row that matches your SELECT query, you need to add the LIMIT clause to your SELECT statement.

The LIMIT clause is used to control the number of rows returned by your query. When you add LIMIT 1 to the SELECT statement, then only one row will be returned.

Here’s an example of a SELECT query with the LIMIT clause:

SELECT * FROM example_database LIMIT 1;

The LIMIT clause accepts two parameters:

  • The number of offsets before returning the row (optional)
  • The number of rows to return (required)

Because the offset is optional, you can pass only one argument to the clause to simply limit the returned row count.

For example, suppose you have a students table with the following data:

+----+---------+---------+-------+--------+
| id | name    | subject | score | gender |
+----+---------+---------+-------+--------+
|  1 | Mark    | Math    |     7 | male   |
|  2 | Natalia | Math    |     8 | female |
|  3 | Gary    | Math    |  NULL | male   |
|  4 | Joe     | English |     8 | male   |
|  5 | Sarah   | Math    |  NULL | female |
|  6 | Peter   | English |     6 | male   |
|  7 | Nathan  | English |     8 | male   |
+----+---------+---------+-------+--------+

To return only the first row, you need to execute the following SQL query:

SELECT * FROM students LIMIT 1;

The returned result set will be as shown below:

+----+------+---------+-------+--------+
| id | name | subject | score | gender |
+----+------+---------+-------+--------+
|  1 | Mark | Math    |     7 | male   |
+----+------+---------+-------+--------+
1 row in set (0.00 sec)

And that’s how you can SELECT only the first row using MySQL.

Next, let’s see how you can add a custom order to change the order of the rows.

MySQL select first row from a custom order

The first row returned by the SELECT statement will always follow the internal table order created by MySQL.

Usually, the rows in your table were ordered by the time they are inserted into the table.

You can change the order of the rows returned by the SELECT statement by adding the ORDER BY clause.

The ORDER BY clause allows you to sort the returned result set by the value of a column in an ascending (ASC) or descending (DESC) order.

For example, the following SQL statement will sort the result using the id column in descending order:

SELECT * FROM students ORDER BY id DESC;

Add the LIMIT clause after the ORDER BY clause to return only the first row as follows:

SELECT * FROM students ORDER BY id DESC LIMIT 1;
+----+--------+---------+-------+--------+
| id | name   | subject | score | gender |
+----+--------+---------+-------+--------+
|  7 | Nathan | English |     8 | male   |
+----+--------+---------+-------+--------+
1 row in set (0.00 sec)

And that’s how you can define a custom order and SELECT only the first row from that order using the LIMIT keyword.

You may change the ORDER BY clause to fit your requirements.

How do I select a specific record in MySQL?

MySQL SELECT statement is used to retrieve rows from one or more tables. The statement can also include UNION statements and subqueries. SELECT statement is used to fetch rows or records from one or more tables.

How do I select a specific record in SQL?

The SQL SELECT Statement.
SELECT column1, column2, ... FROM table_name;.
SELECT * FROM table_name;.
Example. SELECT CustomerName, City FROM Customers;.
Example. SELECT * FROM Customers;.

How do I select a single row?

In SQLJ, a single-row query can be executed and its result set data can be retrieved with a single statement: SELECT ... INTO