If there are duplicate data, how to select a specific one in mysql

Question

I have a program_data table in a mysql database.

  _id     value
  1        0 
  2        1
  3        3
  4        1
  5        1
  6        1
  7        6
  8        1
  9        2

Now I want to select one row whose value is 1. But the twist is what I wanted to select the third part from the bottom of the value column.

Result:

_id = 5  value = 1

I wrote a query like:

 SELECT distinct * FROM program_data  WHERE value= 1 ORDER BY value desc                
  limit  3;

But in this case a get the last 3 rows, but I only want one row, the third value of which is 1 from the bottom.

+3

Arvindo June 26. 15 at 10:46

2 answers

Gordon linoff · Answer 1 · 2015-06-26T10:50:00+0000

If you lost the third 1 from the "bottom", considering that _id

determines the order:

select d.*
from data d
where value = 1
order by _id desc
limit 2, 1;

This uses sentence bias limit

.

Ameya deshpande · Answer 2 · 2015-06-26T10:54:59+0000

you can do it using this query. Here is the SQLFIDDLE Third value

SELECT * 
FROM (
 SELECT _id,value
 FROM data
 where value = 1 
 ORDER BY _id ASC
 LIMIT 3
) AS tbl
ORDER BY _id DESC
LIMIT 1