CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Bill
Bill

Reputation: 19248

Select order before group

This has frustrated me a bit, and I have googled around for a while.

id                   int(11)                
target_id            int(11)    
text                 text   latin1_swedish_ci
ip_address           varchar(20)    latin1_swedish_ci
created_by_user_id   int(11)    
created_date         datetime

Data example:

id target_id    ip_address    created_by_user_id    text    created_date
1  113          192.168.0.1   1                     test1   2014-08-24 12:32:01
1  113          192.168.0.1   1                     test2   2014-08-24 13:32:01 
1  1            192.168.0.1   113                   test3   2014-08-21 13:32:01  
1  1            192.168.0.1   113                   test4   2014-08-22 13:32:01

How could I select the latest result, and also group by target_id? I have tried the following SQL statement to group correctly, but it didn't bring the latest result.

SELECT
    *
FROM
    `fq_message`
GROUP BY
    target_id
ORDER BY
    id DESC

returns:

1  113          192.168.0.1   1                     test1   2014-08-24 12:32:01
1  1            192.168.0.1   113                   test3   2014-08-21 13:32:01

I want to achieve

1  113          192.168.0.1   1                     test2   2014-08-24 13:32:01 
1  1            192.168.0.1   113                   test4   2014-08-22 13:32:01

Upvotes: 1

Views: 149

Answers (2)

sameh.q
sameh.q

Reputation: 1709

Try his code:

SELECT
    ID, 
    target_id, 
    ID_ADDRESS, 
    CREATED_BY_USER_ID, 
    (SELECT TEXT FROM [YOUR_TABLE_NAME] WHERE ID = TMP.ID AND TARGET_ID = TMP.TARGET_ID AND ID_ADDRESS = TMP.ID_ADDRESS AND CREATED_BY_USER_ID = TMP.CREATED_BY_USER_ID AND CREATED_DATE = TMP.CREATED_DATE), 
    CREATED_DATE

FROM
(
    SELECT 

        ID, 
        target_id, 
        ID_ADDRESS, 
        CREATED_BY_USER_ID,
        MAX(CREATED_DATE) AS CREATED_DATE 

    FROM 
    /* Instead of this from, use from [your table] */
    (
        SELECT 1 ID, 113 TARGET_ID, '192.168.0.1' ID_ADDRESS, 1   CREATED_BY_USER_ID, 'test1' TEXT, TO_DATE('2014-08-24 12:32:01', 'YYYY-MM-DD HH24:MI:SS') CREATED_DATE FROM DUAL UNION
        SELECT 1   , 113          , '192.168.0.1'           , 1                     , 'test2'     , TO_DATE('2014-08-24 13:32:01', 'YYYY-MM-DD HH24:MI:SS')              FROM DUAL UNION
        SELECT 1   , 1            , '192.168.0.1'           , 113                   , 'test3'     , TO_DATE('2014-08-21 13:32:01', 'YYYY-MM-DD HH24:MI:SS')              FROM DUAL UNION
        SELECT 1   , 1            , '192.168.0.1'           , 113                   , 'test4'     , TO_DATE('2014-08-22 13:32:01', 'YYYY-MM-DD HH24:MI:SS')              FROM DUAL
    )
    GROUP BY ID, target_id, ID_ADDRESS, CREATED_BY_USER_ID
) TMP
ORDER BY ID, CREATED_DATE DESC

Result:

1   113 192.168.0.1 1   8/24/2014 1:32:01 PM
1   1   192.168.0.1 113 8/22/2014 1:32:01 PM

Tested in Oracle

Upvotes: 0

Andomar
Andomar

Reputation: 238048

MySQL allows you to write queries that are formally not SQL. For example, the following query might work in MySQL, or it might not. A sane database would certainly reject it:

select  *
from    (
        select  *
        from    fq_message
        order by
                created_date desc
        ) SubQueryAlias
 group by
        target_id

This tells MySQL to sort before group, as you ask. But it's not real SQL. The SQL standard says subquery sets, and tables, are unordered. All other databases will reject the inner query as imposing a sort order that does not matter.

The query also selects * but groups on target_id. MySQL will grab the first value it encounters, which if it honors the subquery sort, is the latest value per target. But there is no guarantee that it will do so. How MySQL handles these things can even change per MySQL version.

A better approach is to write SQL that requires MySQL to do the right thing. One such way is a filtering join with the set of latest dates per target:

select  msg.*
from    fq_message msg
join    (
        select  target_id
        ,       max(created_date) as max_date_for_this_target
        from    fq_message
        group  by
                target_id
        ) filter
on      msg.target_id = filter.target_id
        and msg.created_date = filter.max_date_for_this_target

The join is an inner join and will only return rows for which the on clause finds a match. The effect is that messages that do not have the latest date for their target are filtered out.

See the tag for more approaches and examples.

Upvotes: 3

Related Questions