开发者

Select get entire row corresponding to max in MySQL Group

When I use开发者_如何学Python Max to find the maximum value of a field in a particular MySQL Group after using GROUP BY, is it possible to get the entire row which contains the maximum value?


I stumbled across this thread when working on some forum code. I wanted to get the latest post for each thread and display it in the list of threads for a particular board.

Quassnoi's answer above was very helpful to me and I was able to adapt it. Here is the code in case it helps anyone else:

SELECT p.id As post_id, p.post_number, p.topic_id
FROM forum_post p, (
    SELECT topic_id, MAX(post_number) As max_post_number
    FROM   forum_post d
    GROUP  BY topic_id) dd
WHERE p.id =
(
    SELECT id
    FROM   forum_post
    WHERE  topic_id = dd.topic_id
    AND    post_number = dd.max_post_number
    ORDER  BY topic_id DESC, post_number DESC, id DESC
    LIMIT  1
)


I wonder if this would work:

select * from table where id in (select distinct max(id) from table group by name)


SELECT  di.id, di.orderer, di.ghigh, di.glow
FROM    (
        SELECT  glow, MIN(orderer) AS orderer
        FROM    t_distinct d
        GROUP BY
                glow
        ) dd
JOIN    t_distinct di
ON      di.id =
        (
        SELECT  id
        FROM    t_distinct ds
        WHERE   ds.glow = dd.glow
                AND ds.orderer = dd.orderer
        ORDER BY
                glow DESC, orderer DESC, id DESC
        LIMIT 1
        )

See this article for more explanations:

  • MySQL: Selecting records holding group-wise maximum (resolving ties)


Look at the following query:

SELECT items.*
FROM items, (select @grouping_value:=null) as init
WHERE GREATEST(grouping_field=@grouping_value,
               IF(@grouping_value:=grouping_field,0,0))
ORDER BY grouping_field, sorted_field DESC

The idea is to sort table data by grouping field and sorted field and take the first row from each group

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜