mysql value check with next row value

Question

i have mysql table called cm

CREATE TABLE `cm` (
  `id` int(10) NOT NULL,
  `chit_no` varchar(30) DEFAULT NULL,
  `pjt_no` varchar(20) DEFAULT NULL,
  `design` varchar(25) DEFAULT NULL,
  `item` varchar(25) DEFAULT NULL,
  `process` varchar(10) DEFAULT NULL,
  `times` int(1) DEFAULT NULL,
  `current_position` int(1) DEFAULT NULL,
  `current_stage` int(1) DEFAULT NULL,
  `qty` int(10) DEFAULT NULL,
  `dmg` int(2) DEFAULT NULL,
  `bbelt` varchar(10) DEFAULT NULL,
  `fdate` date DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  `shift` int(1) DEFAULT NULL,
  `date` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

table view

enter image description here

this table contain 100K records. i want to get only rows which has Process=Firing and last_record=1 AND qty>0 and also next record qty=0 for perticular date range

this code i have tried but there are no output.

 $sql =mysql_query("SELECT * 
                      FROM cm  
                     WHERE  (    process='Firing' 
                             AND qty>0 
                             AND last_record='1'
                            ) 
                        AND 1>(SELECT qty 
                                 FROM cm 
                                WHERE id=id+1)  
                        AND fdate BETWEEN '$from' AND '$to' 
                   ORDER BY cm.id,design, item,chit_no")
        or die ("err1");

I think this part "1>(SELECT qty FROM cm WHERE id=id+1)" should be changed to "0=(SELECT qty FROM cm WHERE id=id+1)" — Oskars Pakers
– Oskars Pakers, Commented Jun 11, 2014 at 6:32
It's very odd that you ORDER BY "cm.id,design, item,chit_no". Is id not UNIQUE/PRIMARY? — Strawberry
– Strawberry, Commented Jun 11, 2014 at 6:38

Sven Tore · Accepted Answer · 2014-06-11 06:46:07Z

First of all, it's important to note that i SQL servers there is never a "next record", unless you define it based on knowledge of the stored data.

MySQL has a tendency to server you unsorted records in the same order they where added, but you never really know.

So first, we have to define the "next record" based on your data in the image I would guess that want you want is the next record based on id. If you know, that id is increased by one each record, and that records are never deleted, then we can locate the "next" record quite easily.

select
  c.*
from
  cm c
where
  c.process='Firing'
and
  c.last_record=1
and
  c.qty>0 /*easy part done*/
and
  exists (select id from cm c2 where c2.id=c1.id+1 and c2.qty=0)

This will get you want to want, IF you can base your query on the fact that there is no gaps in the id sequence.

I don't really know what kind of application this is, but since you want to compare and select rows based on "next" rows, I guess records are grouped some how (design id?) and that "next record" is likely not based on id alone, but should also take e.g design into account.

If you have gaps in your id sequence, or need to group the records to define "next" then your query will be much slower, but you could do something like:

select
  c.*
from
  cm c
where
  c.process='Firing'
and
  c.last_record=1
and
  c.qty>0 /*easy part done*/
and
  exists (select id from cm c2 where c2.Id=(select min(id) from cm c3 where c3.id>c.id and c3.design=c1.design) and c2.qty=0)

If possible, please also re evaluate your data model / bis logic

Collectives™ on Stack Overflow

mysql value check with next row value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related