SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Required one query which give me the correct date and time as per the Max value.

    Hi All,
    I am very new in Mysql. Please Help me.

    Following two select queries:-
    1) Will give me Max response time value but date and time not coming as per the Max value.

    select l.name,d.datetime,MAX(d.value) from performance_labels l,performance_data d where l.id=d.performance_label and d.performance_label='134' and d.datetime >'2008-12-22 01:00:00' and d.datetime < '2008-12-22 02:00:00' Group by l.name;

    2) Will give me the Response time value for one hours.

    select l.name,d.datetime,d.value from performance_labels l,performance_data d where l.id=d.performance_label and d.performance_label='134' and d.datetime >'2008-12-22 01:00:00' and d.datetime < '2008-12-22 02:00:00'


    Required one query which give me the correct date and time as per the Max value.

    Thanks,
    Kalpesh

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT l.name
         , d.`datetime`
         , d.value 
      FROM performance_labels AS l
    INNER
      JOIN performance_data AS d 
        ON d.performance_label = l.id
       AND d.`datetime` > '2008-12-22 01:00:00' 
       AND d.`datetime` < '2008-12-22 02:00:00' 
       AND d.value = 
           ( SELECT MAX(value)
               FROM performance_data
              WHERE performance_label = l.id
                AND `datetime` > '2008-12-22 01:00:00' 
                AND `datetime` < '2008-12-22 02:00:00' ) 
     WHERE l.id = 134
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Many Many thanks for your quick response. its working fine.

    Thanks once again.
    Kalpesh

  4. #4
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937,

    can we get MAX MIN and AVG value in one queries with the respective data and time.

    Thanks & Regards,
    Kalpesh

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    yes, the same way, with a subquery for each

    you might run into trouble with ORs, though, so post your query if you run into trouble
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937,

    When i am using following queries there is no output.

    SELECT l.name
    , d.`datetime`
    , d.value
    FROM performance_labels AS l
    INNER
    JOIN performance_data AS d
    ON d.performance_label = l.id
    AND d.`datetime` > '2008-12-22 01:00:00'
    AND d.`datetime` < '2008-12-22 02:00:00'
    AND d.value =
    ( SELECT MAX(value)
    FROM performance_data
    WHERE performance_label = l.id
    AND `datetime` > '2008-12-22 01:00:00'
    AND `datetime` < '2008-12-22 02:00:00' )
    AND d.value =
    ( SELECT MIN(value)
    FROM performance_data
    WHERE performance_label = l.id
    AND `datetime` > '2008-12-22 01:00:00'
    AND `datetime` < '2008-12-22 02:00:00' )
    WHERE l.id = 134;

    Please help me how to get the MIN MAX and AVG value in one queries.
    I want to use this out put in BIRT for reporting purpose.

    Thanks & Regards,
    Kalpesh K. Pandya

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    as i suspected, you've written this --

    AND d.value = ( SELECT MAX(value) ...
    AND d.value = ( SELECT MIN(value) ...

    the d.value cannot be equal to both the minimun and the maximum at the same time (unless there's only one value)

    i mentioned you'd need an OR, and here is how you would do it, with the necessary parentheses --
    Code:
    SELECT l.name
         , d.`datetime`
         , d.value 
      FROM performance_labels AS l
    INNER
      JOIN performance_data AS d 
        ON d.performance_label = l.id
       AND d.`datetime` > '2008-12-22 01:00:00' 
       AND d.`datetime` < '2008-12-22 02:00:00' 
       AND (
           d.value = 
              ( SELECT MAX(value)
              FROM performance_data
              WHERE performance_label = l.id
              AND `datetime` > '2008-12-22 01:00:00' 
              AND `datetime` < '2008-12-22 02:00:00' ) 
        OR d.value = 
              ( SELECT MIN(value)
              FROM performance_data
              WHERE performance_label = l.id
              AND `datetime` > '2008-12-22 01:00:00' 
              AND `datetime` < '2008-12-22 02:00:00' ) 
           )
     WHERE l.id = 134;
    does that make sense?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937,
    Many Many thanks for your help.

    Thanks once again.

    Thanks & Regards,
    Kalpesh Pandya


Bookmarks

Posting Permissions

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