SQL查询选择除最大值之外的所有内容

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.

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

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

This query returns the following error:

此查询返回以下错误：

MySQL error: #1111 - Invalid use of group function

MySQL错误：＃1111 - 无效使用组功能

Is there any way around this?

有没有办法解决？

2 个解决方案

#1

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

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

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)

#2

the problem you have is that is that you need to find the max (latest) feature from the table, while going over each row, but MAX() is a group function - you have to group all rows to use it.

你遇到的问题是你需要从表中找到最大（最新）功能，同时遍历每一行，但MAX（）是一个组功能 - 你必须将所有行分组才能使用它。

you can use a sub-select to get the id of the last feature:

您可以使用子选择来获取最后一个功能的ID：

WHERE featureId <> (SELECT featureId From features ORDER BY postedOn DESC LIMIT1)

there is a problem with this approach - the subselect is run for every row, but it is not that expensive.

这种方法存在问题 - 每行都会运行子选择，但它并不昂贵。

#1