CODEWITHSUNDEEP
mysqlsql
user1651888
user1651888

Reputation: 463

My sql statement to fetch the rows if any particular column value has changed from previous entry

I have a table with date and table entries. I am using My sql database.

Table employees

updatedDate               req_count     error_count
14-03-2014 10:20:39           1             0 
15-03-2014 11:10:00           1             0 
15-03-2014  12:10:00          1             1 
15-03-2014  16:12:00          1             1 
16-03-2014  12:09:00          2             10

I would like to fetch the entries if anything has changed from previous entry. For example sql query should return only changed entries of req_count and error_count

updatedDate               req_count     error_count
14-03-2014 10:20:39           1             0 
15-03-2014  12:10:00          1             1 
16-03-2014  12:09:00          2             10

I am using 

SELECT updateDate,req_count,error_count FROM employees WHERE empId=10
    AND req_count > 0 OR error_count > 0

This returns me all 5 rows where as i want only 3 rows. What is the correct way to fetch the rows if any particular column value has changed.

Upvotes: 1

Views: 83

Answers (2)

Lupin
Lupin

Reputation: 1244

You can try this

SELECT a.*
FROM employees AS a
WHERE 
empId=10
AND
(
    (
        a.req_count <> (
            SELECT
                b.req_count
            FROM
                employees AS b
            WHERE
                a.id > b.id
            ORDER BY
                b.id DESC
            LIMIT 1
        )
    )
    OR (
        a.error_count <> (
            SELECT
                b.error_count
            FROM
                employees AS b
            WHERE
                a.id > b.id
            ORDER BY
                b.id DESC
            LIMIT 1
        )
    )
)

the query runs on the records and for each one check if either the req_count or the error_count has changed from previous record, where it gets the previous record by a field called id that i am not sure you have in your table.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269473

My suggestion. Get the previous updated date for each employee. Then join back to the table to get the previous record so you can do the comparison. The following is one way to get the previous updated date:

select e.*,
       (select max(e2.updatedDate)
        from employees e2
        where e2.empId = e.empId and e2.updatedDate < e.updatedDate
      ) as prev_updatedDate
from employees;

Then the full query is:

select e.*
from (select e.*,
             (select max(e2.updatedDate)
              from employees e2
              where e2.empId = e.empId and e2.updatedDate < e.updatedDate
            ) as prev_updatedDate
      from employees
     ) e left join
     employees prev_e
     on prev_e.empId = e.empId and prev_e.updatedDate = e.prev_updatedDate
where (prev_e.empId is null) or
      (prev_e.req_count <> e.req_count or prev_e.error_count <> e.error_count);

Upvotes: 2

Related Questions