Sorting field within GROUP BY

anjanesh · #1 (**permalink**) 06-30-06, 07:49

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

r937 · #2 (**permalink**) 06-30-06, 08:42

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 )

anjanesh · #3 (**permalink**) 06-30-06, 09:57

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.

r937 · #4 (**permalink**) 06-30-06, 10:35

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

anjanesh · #5 (**permalink**) 06-30-06, 12:43

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.

r937 · #6 (**permalink**) 06-30-06, 13:17

please do not use field1, field2, use your real names

please show your query exactly as you ran it

anjanesh · #7 (**permalink**) 06-30-06, 14:22

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

r937 · #8 (**permalink**) 06-30-06, 16:54

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

anjanesh · #9 (**permalink**) 06-30-06, 23:38

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)

r937 · #10 (**permalink**) 06-30-06, 23:42

those results look okay -- can you see why there are 4 rows for site 74?

anjanesh · #11 (**permalink**) 07-01-06, 00:47

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)

Quote:

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 ?

r937 · #12 (**permalink**) 07-01-06, 01:12

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?

anjanesh · #13 (**permalink**) 07-01-06, 01:40

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)

r937 · #14 (**permalink**) 07-01-06, 04:29

see if this makes a difference --

Code:

alter table Articles add index (SiteID,Entry)

anjanesh · #15 (**permalink**) 07-01-06, 05:11

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech