Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161

    Unanswered: Sorting field within GROUP BY

    Code:
    SELECT * FROM `tbl1` GROUP BY `field1`
    I also have a field called `Entered` as datetime.

    Problem is, there are many same values of `field1` - and I want the one that has `Entered` in the begining - a GROUP BY is picking the last one entered in the database or something like that.

    How do I sort within a GROUP BY ?

    Thanks
    MySQL 5.1

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do a search on the mysql com site for "GROUP BY with hidden fields"

    in particular, you should never use GROUP BY column together with the dreaded, evil "SELECT star"

    but in this case you don't need GROUP BY at all
    Code:
    select Field1
         , Field2
         , Field3
         , Entered
      from tbl1 as T
     where Entered
         = ( select max(Entered)
               from tbl1
              where Field1 = T.Field1 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    I tried to do a similar thing mentioned here, and this forced me to cool reboot.
    Code:
    SELECT article, dealer, price, `Entered`
    FROM   shop s1
    WHERE UNIX_TIMESTAMP(s1.`Entered`) = (SELECT MAX(UNIX_TIMESTAMP(s2.`Entered`))
                  FROM shop s2
                  WHERE s1.article = s2.article);
    Will max(Entered) work directly ? `Entered` is of DATETIME format.

    Before I execute it, will it crash for a table having around, 50,000 rows and just 4 fields ?

    EDIT : r937 - I executed your code with LIMIT 0,100 but I forgot to mention that I needed it 'grouped by' not `Entered` but field2.
    Last edited by anjanesh; 06-30-06 at 11:11.
    MySQL 5.1

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you do not have to convert a DATE column to unix timestamp in order to use MAX on it

    please explain more what you mean by "I needed it 'grouped by' not `Entered` but field2"

    please show sample rows to illustrate what you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Once again this was taking forever :
    Code:
    SELECT `field1`, `field2`, `Entered` from `tbl1` AS T
    WHERE `Entered` =
    (SELECT MAX(`Entered`) FROM `tbl1` WHERE `Entered` = T.`Entered`)
    GROUP BY `field2`
    LIMIT 0, 100
    Had to restart PC.

    I want to display all rows with `field2` being distinct - and since there are many same `field2` values, I want the one shown which was entered the last.
    MySQL 5.1

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please do not use field1, field2, use your real names

    please show your query exactly as you ran it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Code:
    SELECT `ArticleID`, `SiteID`, `Entry` from `Articles` SA
    WHERE `Entry` =
    (SELECT MAX(`Entry`) FROM `Articles` WHERE `Entry` = SA.`Entry`)
    GROUP BY `SiteID`
    LIMIT 0, 100
    MySQL 5.1

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, like i said, in this case you don't need GROUP BY

    now have a look at this and you will understand why it is dangerous (not for me, but for you!) to use phony names like Field1 and Field2

    can you tell where the key difference is?
    Code:
    SELECT ArticleID
         , SiteID
         , Entry 
      from Articles SA
     WHERE Entry =
          ( SELECT MAX(Entry) 
              FROM Articles 
             WHERE SiteID = SA.SiteID )
    ORDER
        BY ArticleID DESC LIMIT 0, 100
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    r937 - I dont want it to show more than 1 SiteID values - thats why I had done GROUP BY SiteID before - but I wanted it to show the SiteID row having the maximum Entry.
    Code:
    +-----------+--------+---------------------+
    | ArticleID | SiteID | Entry               |
    +-----------+--------+---------------------+
    |      7395 |     75 | 2006-05-07 00:00:00 |
    |      7228 |     74 | 2006-05-04 00:00:00 |
    |      7227 |     74 | 2006-05-04 00:00:00 |
    |      7226 |     74 | 2006-05-04 00:00:00 |
    |      7225 |     74 | 2006-05-04 00:00:00 |
    |      7181 |     57 | 2006-06-07 20:14:11 |
    .
    .
    .
    |      6777 |     17 | 2006-01-01 00:00:00 |
    +-----------+--------+---------------------+
    100 rows in set (8 min 14.41 sec)
    MySQL 5.1

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    those results look okay -- can you see why there are 4 rows for site 74?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    I had to create a separate table with same structure and exported some 300
    records - it was taking forever on the table containing 50k records.
    All I changed was ORDER BY ArticleID ASC LIMIT 0, 100; to ORDER BY SiteID ASC LIMIT 0, 100; and the former took around 9 mins, the latter was just taking forever.
    Code:
    mysql> SELECT ArticleID
        ->      , SiteID
        ->      , Entry
        ->   from Articles SA
        ->  WHERE Entry =
        ->       ( SELECT MAX(Entry)
        ->           FROM Articles
        ->          WHERE SiteID = SA.SiteID )
        -> ORDER
        ->     BY SiteID ASC LIMIT 0, 100;
    +-----------+--------+---------------------+
    | ArticleID | SiteID | Entry               |
    +-----------+--------+---------------------+
    |       163 |      1 | 2006-06-27 21:20:31 |
    |      1201 |      2 | 2006-06-27 21:22:05 |
    |      7142 |      3 | 2006-06-27 21:24:11 |
    |      3631 |      6 | 2006-06-27 21:20:37 |
    |       705 |      8 | 2006-06-27 21:21:42 |
    |      7071 |      9 | 2006-06-27 21:19:56 |
    |      5453 |     10 | 2006-06-27 21:20:22 |
    |       215 |     14 | 2006-06-27 21:21:13 |
    |       317 |     15 | 2006-06-27 21:20:55 |
    |      5043 |     20 | 2006-06-27 21:21:21 |
    |       177 |     31 | 2006-06-27 21:18:25 |
    |      3525 |     35 | 2006-06-27 21:20:26 |
    |      5522 |     37 | 2006-06-27 21:22:31 |
    |      2001 |     38 | 2006-06-27 21:18:44 |
    |       149 |     45 | 2006-06-27 21:19:04 |
    |      6156 |     47 | 2006-06-27 23:16:59 |
    |      4252 |     49 | 2006-06-27 21:19:31 |
    |      4640 |     50 | 2006-06-27 21:19:50 |
    |      2767 |     52 | 2006-06-07 20:09:41 |
    |      5248 |     53 | 2006-06-07 20:09:50 |
    |      3078 |     54 | 2006-06-07 20:10:13 |
    |       128 |     55 | 2006-06-07 20:13:56 |
    |      7181 |     57 | 2006-06-07 20:14:11 |
    |      5221 |     58 | 2006-06-27 21:20:04 |
    |      6523 |     60 | 2006-06-27 21:20:43 |
    |      3351 |     61 | 2006-06-27 21:21:36 |
    |      6035 |     62 | 2006-06-27 21:21:39 |
    |      5668 |     63 | 2006-06-27 21:21:54 |
    |      4270 |     64 | 2006-06-27 21:22:16 |
    |      3616 |     65 | 2006-06-27 21:22:26 |
    |       355 |     66 | 2006-06-27 21:22:43 |
    |      6202 |     67 | 2006-06-07 20:08:12 |
    |      7225 |     74 | 2006-05-04 00:00:00 |
    |      7226 |     74 | 2006-05-04 00:00:00 |
    |      7227 |     74 | 2006-05-04 00:00:00 |
    |      7228 |     74 | 2006-05-04 00:00:00 |
    +-----------+--------+---------------------+
    36 rows in set (0.38 sec)
    can you see why there are 4 rows for site 74?
    - yes, because the datetime is same. But GROUP BY SiteID seems to solve that.

    Code:
    SELECT ArticleID
         , SiteID
         , Entry 
      from Articles SA
     WHERE Entry =
          ( SELECT MAX(Entry) 
              FROM Articles 
             WHERE SiteID = SA.SiteID )
    GROUP BY SiteID
    ORDER
        BY Entry DESC LIMIT 0, 100
    This seems to be exactly what I want but how am I suppossed to get this working in secs instead of 10-30 mins ?
    MySQL 5.1

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by anjanesh
    But GROUP BY SiteID seems to solve that.
    i'm only going to say this one more time, you do not want to put GROUP BY into this query

    as far as the performance goes, do you have any indexes on any columns? which ones? have you run an EXPLAIN on the query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    ArticleID is PK.

    Code:
    mysql> EXPLAIN SELECT ArticleID, SiteID, Entry
        -> FROM Articles SA
        -> WHERE Entry = (
        ->
        -> SELECT MAX( Entry )
        -> FROM Articles
        -> WHERE SiteID = SA.SiteID
        -> )
        -> ORDER
        -> BY Entry DESC
        -> LIMIT 0 , 100;
    +----+--------------------+----------+------+---------------+------+---------+------+------+-----------------------------+
    | id | select_type        | table    | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
    +----+--------------------+----------+------+---------------+------+---------+------+------+-----------------------------+
    |  1 | PRIMARY            | SA       | ALL  | NULL          | NULL | NULL    | NULL |  308 | Using where; Using filesort |
    |  2 | DEPENDENT SUBQUERY | Articles | ALL  | NULL          | NULL | NULL    | NULL |  308 | Using where                 |
    +----+--------------------+----------+------+---------------+------+---------+------+------+-----------------------------+
    2 rows in set (0.00 sec)
    MySQL 5.1

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see if this makes a difference --
    Code:
    alter table Articles add index (SiteID,Entry)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    161
    Wow ! Thanks. This defintely is a faster - even without the LIMIT 0,100 - but still takes 61-65 secs.

    But many articles have same date, and the time is 00:00:00 - as many were set manually - so the Cardinality is 16601 - will this be a prob ?

    Without the LIMIT, the number of rows returned were 23,553 but there are only 62 DISTINCT `SiteID`s.
    If all the `Entry` values were unique, I would've got 62 rows from your query right ?

    Does adding more Indexes help in making it faster ? Because (ArticleID+SiteID) is always unique.
    MySQL 5.1

Posting Permissions

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