SQL查询以选择除最大值以外的所有内容 [英] SQL Query to Select Everything Except the Max Value

问题描述

我有一个相当复杂的查询，它从三个表中获取数据，现在我希望它变得更加复杂(哦，亲爱的)！

I have this rather complex query that grabs data from three tables, and now I want it to be even more complicated (Oh dear)!

我希望最后发布的功能显示在页面的自己的部分中，通过选择表中的最后一个条目，这很容易.但是，对于复杂的查询(网站的主页)，我希望不能显示此功能.

I'd like the last posted feature to be displayed in it's own section of the page, and that's pretty easy by selecting the last entry in the table. However, for the complex query (the main page of the site), I'd like to be able to NOT have this feature displayed.

我想将以下查询union还原到之前的查询，但是没有返回正确的结果:

I'd like to union the following query to my previous query, but it isn't returning the correct results:

SELECT
    features.featureTitle AS title, 
    features.featureSummary AS body, 
    features.postedOn AS dummy, 
    DATE_FORMAT( features.postedOn,  '%M %d, %Y' ) AS posted, 
    NULL, 
    NULL, 
    staff.staffName, 
    features.featureID 
FROM 
    features 
    LEFT JOIN staff ON 
        features.staffID = staff.staffID 
WHERE features.postedOn != MAX(features.postedOn)
ORDER BY dummy DESC LIMIT 0,15

此查询返回以下错误:

MySQL错误:#1111-无效使用组函数

MySQL error: #1111 - Invalid use of group function

有什么办法解决这个问题吗?

Is there any way around this?

推荐答案

max查询需要在其自己的子查询中，因此最终的SQL应该是::

The max query needs to be in its own subquery, so your final SQL should be::

SELECT features.featureTitle AS title,
    features.featureSummary AS body, 
    features.postedOn AS dummy,
    DATE_FORMAT( features.postedOn,  '%M %d, %Y' ) AS posted,
    NULL,
    NULL,
    staff.staffName,
    features.featureID 
FROM 
    features 
    LEFT JOIN staff ON 
        features.staffID = staff.staffID
WHERE
   features.postedOn != (select max(features.postedOn) from features)

这篇关于SQL查询以选择除最大值以外的所有内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！