0

I want to retrieve value from a specific row of MySQL table,for example : If my table is having 5 rows I want to retrieve value of second row. What's the query for doing that.

Luna
  • 956
  • 4
  • 15
  • 28

2 Answers2

3

"I want to retrieve value of second row."

This question does not make sense to a relational database. In a relational database, there is no intrinsic concept of row order, and therefor there is no such thing as "the first row" or "the 4th row" or whatever.

In a relational database, tables should have one or more keys. A key is a column or combination thereof that are not NULL-able, and that have at most one row for any distinct combination of values. You can then use the key to retrieve rows.

It might be possible that it makes sense to impose a particular order on the rows, after which you could refer to the rows by ordinal position. Ordering is done with ORDER BY, followed by a list of expressions that determine the order, and then a LIMIT clause to specify which "slice" of the result you want to retrieve.

The other answers mention LIMIT, but leave out the ORDER BY clause. This means that you have zero gurantees that whatever is now returned as row #x will be returned next time when you execute that exact same query.

Instead of thinking of rows as having a position, you should think of rows having a key, and use that to retrieve a particular row, like this:

SELECT ...
FROM   table
WHERE  column1 = value

or if you have a composite key (multiple columns in the key), like so:

SELECT ...
FROM   table
WHERE  column1 = value1
AND    column2 = value2
...
AND    columnN = valueN

If you really still want to use positions, be sure to use the ORDER BY to impose an order:

SELECT ...
FROM   table
ORDER BY column1, ..., columnN
LIMIT <offset>, <count>

Where is an integer literal that specifies the ordinal position, and is an integer literal that specifies how many rows to fetch starting from the position.

Roland Bouman
  • 31,125
  • 6
  • 66
  • 67
  • ok..Thanks for the explanation..but I've a jTable and the values displayed in the jTable is the same ordered value as such from database table,suppose first value of jTable will be the first row value of database table,if i put a specific key I can very easily retrieve the corresponding value.But there's no specific key in that context,so I want a solution for selecting value in this way.. – Luna Jul 06 '12 at 10:57
  • If there is no key, than that's what you should fix first. Like I said, there is zero guarantees concerning the order of the rows, unless you explicitly impose one. see also http://stackoverflow.com/questions/2040724/how-does-sql-server-sort-your-data/2040833#2040833 – Roland Bouman Jul 06 '12 at 11:09
0

You can use a LIMIT clause (MySQL docu) for that like in the following code, but be aware, that unless you apply an ordering of some sort (ORDER BY col), the actual row, that is outputted might vary as there is no intrinsic ordering in SQL-tables.

SELECT *
  FROM yourTable
  LIMIT 1,1

A better solution would be to add an additional column id to the table and then select by that id. Or at least use the id column for ordering as described before.

Sirko
  • 72,589
  • 19
  • 149
  • 183
  • thank you..it's giving me value of 3rd row..not second row.actually in limit 2,1 what does 2 and 1 stand for? – Luna Jul 06 '12 at 10:50
  • @Tickua My bad. `LIMIT` is zero based - corrected that one. From the docu you can see, that the first value is the offset (the rownumber you want to start at) and the second is the amount of rows you want to get in total. – Sirko Jul 06 '12 at 10:55
  • ok..does table row index starts from 0?if i have 5 rows it'l be from 0 to 4? – Luna Jul 06 '12 at 11:00
  • @Tickua Exactly. That's what i meant by saying it's zero based. – Sirko Jul 06 '12 at 11:01
  • Without an ORDER BY, there is no guarantee that whatever currently happens to be row at position x will remain the same the next time you execute the exact same query. – Roland Bouman Jul 06 '12 at 11:11