How do i find a row in mysql?

3.3.4.2 Selecting Particular Rows

As shown in the preceding section, it is easy to retrieve an entire table. Just omit the WHERE clause from the SELECT statement. But typically you don't want to see the entire table, particularly when it becomes large. Instead, you're usually more interested in answering a particular question, in which case you specify some constraints on the information you want. Let's look at some selection queries in terms of questions about your pets that they answer.

You can select only particular rows from your table. For example, if you want to verify the change that you made to Bowser's birth date, select Bowser's record like this:

mysql> SELECT * FROM pet WHERE name = 'Bowser';
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+

The output confirms that the year is correctly recorded as 1989, not 1979.

String comparisons normally are case-insensitive, so you can specify the name as 'bowser', 'BOWSER', and so forth. The query result is the same.

You can specify conditions on any column, not just name. For example, if you want to know which animals were born during or after 1998, test the birth column:

mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+

You can combine conditions, for example, to locate female dogs:

mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

The preceding query uses the AND logical operator. There is also an OR operator:

mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+

AND and OR may be intermixed, although AND has higher precedence than OR. If you use both operators, it is a good idea to use parentheses to indicate explicitly how conditions should be grouped:

mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
       OR (species = 'dog' AND sex = 'f');
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

Ideally I need a query that is equivalent to

select * from customer where row_number() = 3

but that's illegal.

I can't use an auto incremented field.

row_number() is the row that needs to be selected.

How do I go about this?

EDIT: Well, I use iSql*plus to practice, and using limit and auto_increment is illegal for some reason. I ended up creating a sequence and a trigger and just upped the id by 1 every time there was an entry.

asked May 4, 2012 at 23:41

2

You can use LIMIT 2,1 instead of WHERE row_number() = 3.

As the documentation explains, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.

Keep in mind that it's an 0-based index. So, if you want the line number n, the first argument should be n-1. The second argument will always be 1, because you just want one row. For example, if you want the line number 56 of a table customer:

SELECT * FROM customer LIMIT 55,1

answered May 4, 2012 at 23:47

sp00msp00m

46.9k27 gold badges136 silver badges242 bronze badges

6

You cannot select a row like that. You have to specify a field whose values will be 3

Here is a query that will work, if the field you are comparing against is id

select * from customer where `id` = 3

answered May 4, 2012 at 23:48

StarxStarx

75.8k45 gold badges181 silver badges259 bronze badges

0

SET @customerID=0;
SELECT @customerID:=@customerID+1 AS customerID
FROM CUSTOMER ;

you can obtain the dataset from SQL like this and populate it into a java data structure (like a List) and then make the necessary sorting over there. (maybe with the help of a comparable interface)

answered May 4, 2012 at 23:54

SanathSanath

4,67710 gold badges48 silver badges77 bronze badges

SQL tables are not ordered by default, and asking for the n-th row from a non ordered set of rows has no meaning as it could potentially return a different row each time unless you specify an ORDER BY:

select * from customer order by id where row_number() = 3

(sometimes MySQL tables are shown with an internal order but you cannot rely on this behaviour). Then you can use LIMIT offset, row_count, with a 0-based offset so row number 3 becomes offset 2:

select * from customer order by id
limit 2, 1

or you can use LIMIT row_count OFFSET offset:

select * from customer order by id
limit 1 offset 2

answered Jun 22, 2017 at 5:00

fthiellafthiella

47k15 gold badges88 silver badges103 bronze badges

You can add an auto generated id field in the table and select by this id

SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = 3;

answered May 4, 2012 at 23:45

duncanportelliduncanportelli

3,1417 gold badges37 silver badges59 bronze badges

Your table will need to be created with a unique ID field that will ideally have the AUTO_INCREMENT attribute. example:

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
PRIMARY KEY (P_Id)
)

Then you can access the 3rd record in this table with:

SELECT * FROM Persons WHERE P_Id = 3

answered May 4, 2012 at 23:53

How do I find a specific row in SQL?

How do I find rows in a table?

How do I select a single row?

How do I find something in MySQL?