SQL Query to Select Everything Except the Max Value

Question

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

Is there any way around this?

Why use features.postedOn AS dummy (assuming by naming it dummy, not using it) not just ORDER BY features.postedOn? — Stefan, Jul 25 '09 at 19:38
features.postedOn never gets *used* in the context of the site, it's always the formatted string 'posted'. It's a dummy string to maintain the order of the various entries, hence it's name 'dummy' (as ordering by 'posted' would return nonsensical results). — different, Aug 05 '09 at 16:18

score 6 · Accepted Answer · answered Jul 25 '09 at 19:19

6

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)

answered Jul 25 '09 at 19:19

Eric

92,005
12
114
115

1

filtering on the date field is probably not a good idea as it is not guaranteed to be unique - there could be two posts at the same time. filtering by id is better. – Guss Jul 25 '09 at 19:39
Given my site's frequency of posting featured articles (1 a week or fewer) it'll be a non-issue. Thanks anyway. :-) – different Jul 25 '09 at 20:12

score 0 · Answer 2 · answered Jul 25 '09 at 19:35

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.

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

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.

score 0 · Answer 3 · answered Aug 28 '22 at 04:36

You could also order by the PostedOn field in descending order and add OFFSET 1, which will display your results starting from the second row.

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
ORDER BY features.postedOn DESC
OFFSET 1;

SQL Query to Select Everything Except the Max Value

3 Answers3

Linked