#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    11
    Rep Power
    0

    Need Help on Query, search max value with corresponding date


    Hi,

    I am quite a noob but what I trying to do is complete a sql query by looking a the max value and matching it with the corresponding date.

    I was able to get the max value but it doesnt work as I want it when I try to add the date.

    Code:
    SELECT     dbo.meterDefineDPView.meterName, MAX(dbo.dailyMeterReading.pressure) AS [Max Pressure]
    FROM         dbo.meterDefineDPView INNER JOIN
                          dbo.dailyMeterReading ON dbo.meterDefineDPView.meterId = dbo.dailyMeterReading.meterId
    WHERE     (dbo.dailyMeterReading.dataQuality = '11' OR
                          dbo.dailyMeterReading.dataQuality = '10') AND (dbo.meterDefineDPView.meterName IN ('001640', '008776', '002536', '008603', '002526', '005071', 
                          '006240', '008620', '006305', '004720', '005064', '002703')) AND (dbo.dailyMeterReading.contractTime BETWEEN CONVERT(DATETIME, '2012-May-14', 
                          102) AND CONVERT(DATETIME, '2013-May-14', 102))
    GROUP BY dbo.meterDefineDPView.meterName
    I am already querying with the 'contractTime'

    This is the results I get back, but I would also like the corresponding date with it.

    Meter name Pressure
    001640 6897.4458
    002526 6909.6948
    002536 6913.7915
    002703 5434.4595
    004720 6003.3535
    005064 5285.4771
    005071 5701.0576
    006240 5833.3745
    006305 5813.6563
    008603 6574.1294
    008620 6563.9731
    008776 6732.4878

    Thank you for your guidance!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by geebra
    ...but it doesnt work as I want it when I try to add the date.
    but in your WHERE clause, you're already specifying which date you want!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by r937
    but in your WHERE clause, you're already specifying which date you want!
    Yes because I am searching within that period for the highest pressure. I can come up with the Meter Name with the highest pressure but I would also like it to show the date when the highest pressure happened.

    For example:
    Meter Name Pressure Date
    001640 7000 05/02/13
    002526 5000 06/30/12
    etc

    Thanks
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by geebra
    Yes because I am searching within that period for the highest pressure.
    but "within that period" is only one date!!

    what am i missing?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by r937
    but "within that period" is only one date!!

    what am i missing?
    The dailyMeterReading tables holds daily data for those meter names.

    Say I want to find the highest pressure that happened between may 2012 and may 2013.

    I am able to find the highest pressure but I also want to know what day it happened and have it display with the results.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by geebra
    ...between may 2012 and may 2013.
    my bad, i read those two dates as being the same
    Code:
    SELECT dbo.meterDefineDPView.meterName
         , maxes.[Max Pressure]
         , dbo.dailyMeterReading.contractTime
      FROM dbo.meterDefineDPView.meterName
    INNER
      JOIN ( SELECT meterId
                  , MAX(pressure) AS [Max Pressure]
               FROM dbo.dailyMeterReading 
              WHERE dataQuality IN ('10','11') 
                AND contractTime 
                    BETWEEN CONVERT(DATETIME, '2012-May-14', 102) 
                        AND CONVERT(DATETIME, '2013-May-14', 102))
             GROUP 
                 BY meterId ) AS maxes
        ON maxes.meterId = dbo.meterDefineDPView.meterId
    INNER
      JOIN dbo.dailyMeterReading 
        ON dbo.dailyMeterReading.meterId = dbo.meterDefineDPView.meterId
       AND dbo.dailyMeterReading.dataQuality IN ('10','11')  
       AND dbo.dailyMeterReading.pressure = maxes.[Max Pressure] 
     WHERE dbo.meterDefineDPView.meterName IN 
           ('001640', '008776', '002536', '008603', '002526', '005071'
           ,'006240', '008620', '006305', '004720', '005064', '002703')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by r937
    my bad, i read those two dates as being the same
    Code:
    SELECT dbo.meterDefineDPView.meterName
         , maxes.[Max Pressure]
         , dbo.dailyMeterReading.contractTime
      FROM dbo.meterDefineDPView.meterName
    INNER
      JOIN ( SELECT meterId
                  , MAX(pressure) AS [Max Pressure]
               FROM dbo.dailyMeterReading 
              WHERE dataQuality IN ('10','11') 
                AND contractTime 
                    BETWEEN CONVERT(DATETIME, '2012-May-14', 102) 
                        AND CONVERT(DATETIME, '2013-May-14', 102))
             GROUP 
                 BY meterId ) AS maxes
        ON maxes.meterId = dbo.meterDefineDPView.meterId
    INNER
      JOIN dbo.dailyMeterReading 
        ON dbo.dailyMeterReading.meterId = dbo.meterDefineDPView.meterId
       AND dbo.dailyMeterReading.dataQuality IN ('10','11')  
       AND dbo.dailyMeterReading.pressure = maxes.[Max Pressure] 
     WHERE dbo.meterDefineDPView.meterName IN 
           ('001640', '008776', '002536', '008603', '002526', '005071'
           ,'006240', '008620', '006305', '004720', '005064', '002703')
    Thanks for the quick reply, when I run this code I get a 'Incorrect syntax near the Keyword GROUP'
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,481
    Rep Power
    1752
    There is an extra close bracket in the sub-query, I think the one you want to drop is the one at the end of the
    Code:
                        AND CONVERT(DATETIME, '2013-May-14', 102))
    line
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by SimonJM
    There is an extra close bracket in the sub-query, I think the one you want to drop is the one at the end of the
    Code:
                        AND CONVERT(DATETIME, '2013-May-14', 102))
    line
    I deleted that close bracket and checked the sql check, it was good.

    Then when I ran it, it gave me this error
    Invalid object name 'dbo.meterDefineDPView.meterName'.


    I tried adding [] to dbo.meterDefineDPView.[meterName] but doesnt help.

    Thanks guys
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by geebra
    Invalid object name 'dbo.meterDefineDPView.meterName'
    aaargh, copy/paste error

    the FROM clause should reference the table, not a column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by r937
    aaargh, copy/paste error

    the FROM clause should reference the table, not a column
    I thought it would have been a simpler solution(which is why mine doesnt work) but it turns out not to be haha.

    Thanks for the help guys!!!!

  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by geebra
    I thought it would have been a simpler solution(which is why mine doesnt work) but it turns out not to be haha.

    Thanks for the help guys!!!!

    Can you guys also explain to me why we need to create a new table for this to work?

    Thanks,
    Brandon
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by geebra
    Can you guys also explain to me why we need to create a new table for this to work?
    what new table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    11
    Rep Power
    0
    Originally Posted by r937
    what new table?
    haha sorry for my noobness,

    Code:
    JOIN ( SELECT meterId
                  , MAX(pressure) AS [Max Pressure]
               FROM dbo.dailyMeterReading 
              WHERE dataQuality IN ('10','11') 
                AND contractTime 
                    BETWEEN CONVERT(DATETIME, '2012-May-14', 102) 
                        AND CONVERT(DATETIME, '2013-May-14', 102))
             GROUP 
                 BY meterId ) AS maxes
    I thought this was making a table maxes and then polling information from that.
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by geebra
    haha sorry for my noobness,
    not a problem

    i assure you, that condition will not last long

    technically, it is a table, since when mysql executes the query, it retrieves the data according to the subquery, and treats it exactly like a table

    that's why a subquery in the FROM clause like this is often called a derived table

    however, it does not exist after the query finishes execution, so in that sense it's different from a table that you would declare yourself

    another name for it is inline view, and that name should also describe how it works
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo