Geek Answers Handbook

SQL Query to select everything except the maximum value

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

I would like the last published function to appear in its own section of the page, and it's pretty easy by selecting the last entry in the table. However, for a complex request (the main page of the site) I would like to be able to NOT show this function.

I would like union to execute the following query against my previous query, but it does not return 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?

+4
source share
2 answers

The max query should be in its own subquery, so your last 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)
+5
source

the problem is that you need to find the maximum (last) function from the table when going through each row, but MAX () is a group function - you need to group all the rows to use it.

you can use a subselect to get the id of the last function:

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

There is a problem with this approach - a subquery is run for each row, but it is not that expensive.

0
source

More articles:


All Articles