selecting previous id with where condition in mysql

Question

i am using the following query to select the previous stock based on some between dates, but the following query fails if the auto incremented id is not in order (while delete some record, it has gap),

 SELECT in_stock, price 
 FROM $tablename  
 WHERE id=(SELECT (id-1) 
           FROM $tablename 
           WHERE sale_date BETWEEN '$from_date' AND '$to_date' 
           ORDER BY entered_date ASC 
           LIMIT 1)

help me to select the previous in_stock

How about putting a counter field, which is incremented at creation of the record, than you may use counter - 1. Did you read here? http://stackoverflow.com/questions/1446821/how-to-get-next-previous-record-in-mysql — toesslab, Apr 19 '14 at 08:09
good idea, but there is already more number of records in table — arun, Apr 19 '14 at 09:23

fthiella · Answer 1 · 2014-04-19T08:15:05.403

1

If IDs are not in order, you should use something like this:

SELECT in_stock, price 
FROM $tablename  
WHERE entered_date=(
        SELECT MAX(entered_date) as max_date
        FROM   $tablename
        WHERE
          entered_date<(SELECT MIN(entered_date) as min_date
                        FROM $tablename 
                        WHERE
                          sale_date BETWEEN '$from_date' AND '$to_date')
      )

edited Apr 19 '14 at 08:15

answered Apr 19 '14 at 08:07

fthiella

48,073
15
90
106

im fresher, scared of this long query.... and this works, SELECT in_stock, price FROM $tablename WHERE id < (SELECT min(id) FROM $tablename WHERE sale_date BETWEEN '$from_date' AND '$to_date') ORDER BY id DESC LIMIT 1 – arun Apr 19 '14 at 09:22
@arun if you are sure that your ids are ordered and you just have some gaps you could use your query but if they are not you need to order by entered_date – fthiella Apr 19 '14 at 09:52

score 1 · Answer 2 · answered Apr 19 '14 at 09:33

1

This works,

SELECT in_stock, price 
 FROM $tablename 
 WHERE id < (SELECT min(id) 
              FROM $tablename 
              WHERE sale_date BETWEEN '$from_date' AND '$to_date') ORDER BY id DESC LIMIT 1

answered Apr 19 '14 at 09:33

arun

4,595
5
19
39

score 0 · Answer 3 · answered Apr 19 '14 at 09:05

Try this

SELECT in_stock, price 
 FROM $tablename  
 WHERE id=(SELECT (SELECT MAX(id)
                    FROM $tablename
                    WHERE id < ( SELECT MAX(id)
                    FROM $tablename )) 
           FROM $tablename 
           WHERE sale_date BETWEEN '$from_date' AND '$to_date' 
           ORDER BY entered_date ASC 
           LIMIT 1)

score -1 · Answer 4 · answered Apr 19 '14 at 08:54

-1

SELECT in_stock,price
 FROM $tablename  
 WHERE id=(SELECT max(id) > 1
           FROM $tablename
           WHERE sale_date BETWEEN '$from_date' AND '$to_date' 
           ORDER BY entered_date ASC 
           LIMIT 1)

answered Apr 19 '14 at 08:54

VIGNESH

9
4

selecting previous id with where condition in mysql

4 Answers4