Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013
    Posts
    4

    Red face Unanswered: Retrieving Values based on Condition

    Hello All,

    I am in a grave problem. First, following is my table description called csd

    Code:
       Column Name        Data Type
       cust_id                bigint
       reading                bigint
       read_date            date
    Some data are as follows

    Code:
          cust_id           reading             read_date
          111                1505                2013-05-05
          111                1510                2013-05-12
          124                1620                2013-05-05
          124                1700                2013-05-06
    From this data i want to retrieve only those whose date matches the current date. If for any cust_id, current date is not there it should retrieve the immediate previous date. In essence, the query will retrieve data for current date if it is there, otherwise it will retrieve data for previous date.

    I tried the following query, but it retrieve both the current date and previous date data for all cust ids.

    Code:
    select distinct t.cust_id,t.reading,t.read_date from csd t where t.read_date=curdate() or exists(select read_date from csd where read_date<curdate());
    Please help me in solving this query

    Regards

    alimdyusuf
    Last edited by alimdyusuf; 05-12-13 at 10:10.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try using this code snippet instead. This is untested "air code", but it should return exactly what you want.
    Code:
    SELECT t.meterid, t.reading, t.dbreceivingdate
       FROM csd AS t
       WHERE  t.dbreceivingdate = (SELECT Max(z.dbreceivingdate)
          FROM csd AS z
          WHERE  z.cust_id = t.cust_id);
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What you need to do here is to first get per customer id the maximum date and then join this back to the original tables to get all the fields:

    Code:
    SELECT csd.cust_id, csd.reading, csd.read_date
    FROM csd
    JOIN (SELECT cust_id, max(read_date) latest_read_date FROM csd GROUP BY cust_id) latest
    WHERE csd.cust_id = latest.cust_id
    AND csd.read_date = latest.latest_read_date;
    Running this gives us

    Code:
    mysql> select * from csd;
    +---------+---------+------------+
    | cust_id | reading | read_date  |
    +---------+---------+------------+
    |     111 |    1505 | 2013-05-05 | 
    |     111 |    1510 | 2013-05-12 | 
    |     124 |    1620 | 2013-05-05 | 
    |     124 |    1700 | 2013-05-06 | 
    +---------+---------+------------+
    4 rows in set (0.00 sec)
    
    mysql> SELECT csd.cust_id, csd.reading, csd.read_date
        -> FROM csd
        -> JOIN (SELECT cust_id, max(read_date) latest_read_date FROM csd GROUP BY cust_id) latest
        -> WHERE csd.cust_id = latest.cust_id
        -> AND csd.read_date = latest.latest_read_date;
    +---------+---------+------------+
    | cust_id | reading | read_date  |
    +---------+---------+------------+
    |     111 |    1510 | 2013-05-12 | 
    |     124 |    1700 | 2013-05-06 | 
    +---------+---------+------------+
    2 rows in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    May 2013
    Posts
    4
    I am a newbie to query. Many many thanks to both Pat Phelan and Ronan Cashell. Both of yours query are working. Thanks once again

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •