Thread: Help with query

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

    Join Date
    Mar 2013
    Posts
    4
    Rep Power
    0

    Help with query


    I have the following table contents:

    Date,Field1,Field2,Field3
    20130101,a,b,1:12
    20130201,a,d,1:13
    20130301,b,b,0:13
    20130501,a,d,1:10

    I want as output:
    20130101,a,b,1:12
    20130301,b,b,0:13
    20130501,a,d,1:10
    The output is for every unique field1 and field2 combination the minimum value of field3.

    I use the following query which does not work:
    SELECT date,field1,field2,min(field3) FROM table WHERE published='1' GROUP BY field1,field2;

    What am I doing wrong here ?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by pollewops
    The output is for every unique field1 and field2 combination the minimum value of field3.

    What am I doing wrong here ?
    nothing

    the only problem i see is that you might not be getting the correct date that accompanies the minimum value of field3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    nothing

    the only problem i see is that you might not be getting the correct date that accompanies the minimum value of field3
    Yes you are right....I do get the wrong dates now...how can i fix that ? Any idea ?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by pollewops
    ...how can i fix that ? Any idea ?
    i can walk you through it

    first of all, can you write the query without reference to the date column? in other words, change the query to return the MIN without the date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by r937
    i can walk you through it

    first of all, can you write the query without reference to the date column? in other words, change the query to return the MIN without the date
    Thanks for helping me out. This is the link to the query i want to change. pollewops.nl/index.php?option=com_zwemmen&Itemid=124
    When you select ALLE from the drop down you see all records. When selecting LIMIETEN the fastest tims for all AFST/SLAG should be visible.
    I use the following query now for that:
    PHP Code:
          $db->setQuery("SELECT datum,titel,afstand,slag,min(tijd) AS MinTijd,zwembad,plaats,omschrijving FROM jos_zwemmen WHERE published='1' AND state>=0 GROUP BY concat(afstand,slag) ORDER BY afstand,slag ASC;"); 
    But that one gives wrong dates (DATUM) back to the results.
    e.g. `Masters 100 vrijeslag 01:04.50` should give a date back in 1968.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Code:
    SELECT jos_zwemmen.datum
         , jos_zwemmen.titel
         , jos_zwemmen.afstand
         , jos_zwemmen.slag
         , jos_zwemmen.tijd
         , jos_zwemmen.zwembad
         , jos_zwemmen.plaats
         , jos_zwemmen.omschrijving
      FROM ( SELECT afstand
                  , slag
                  , MIN(tijd) AS MinTijd
               FROM jos_zwemmen
              WHERE published = '1'
                AND state >= 0
             GROUP
                 BY afstand
                  , slag     ) AS maxq
    INNER
      JOIN jos_zwemmen
        ON jos_zwemmen.afstand = maxq.afstand
       AND jos_zwemmen.slag    = maxq.slag
       AND jos_zwemmen.tijd    = maxq.MinTijd
    ORDER
        BY jos_zwemmen.afstand
         , jos_zwemmen.slag
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    4
    Rep Power
    0

    Smile


    Originally Posted by r937
    Code:
    SELECT jos_zwemmen.datum
         , jos_zwemmen.titel
         , jos_zwemmen.afstand
         , jos_zwemmen.slag
         , jos_zwemmen.tijd
         , jos_zwemmen.zwembad
         , jos_zwemmen.plaats
         , jos_zwemmen.omschrijving
      FROM ( SELECT afstand
                  , slag
                  , MIN(tijd) AS MinTijd
               FROM jos_zwemmen
              WHERE published = '1'
                AND state >= 0
             GROUP
                 BY afstand
                  , slag     ) AS maxq
    INNER
      JOIN jos_zwemmen
        ON jos_zwemmen.afstand = maxq.afstand
       AND jos_zwemmen.slag    = maxq.slag
       AND jos_zwemmen.tijd    = maxq.MinTijd
    ORDER
        BY jos_zwemmen.afstand
         , jos_zwemmen.slag
    :-)))) it works now ! THANKS !

IMN logo majestic logo threadwatch logo seochat tools logo