CODEWITHSUNDEEP
phpmysql
Tharindu ucsc
Tharindu ucsc

Reputation: 641

mysql value check with next row value

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");

Upvotes: 1

Views: 1113

Answers (1)

Sven Tore
Sven Tore

Reputation: 987

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

Upvotes: 1

Related Questions