LIMIT in SubQuery?

_sh

This one is baffling me and I cannot understand why MySQL have added such a limitation as preventing LIMIT within a subquery!? Eitherway, I'm trying to get this query to work, I am aware that there are some tricks that you can implement to achieve the desired effect, can anyone assist please?

DELETE FROM tbl_queries WHERE QueryTypeID = 2 AND QueryUserID = '1' AND QueryID NOT IN (SELECT QueryID FROM tbl_queries WHERE QueryTypeID = 2 AND QueryUserID = '1' ORDER BY QueryID DESC LIMIT 4)

Essentially, I want to delete every past query stored for that user of that query type, but retain the most recent 4.

_sh

@sh;10982567 wrote:
This one is baffling me and I cannot understand why MySQL have added such a limitation as preventing LIMIT within a subquery!? Eitherway, I'm trying to get this query to work, I am aware that there are some tricks that you can implement to achieve the desired effect, can anyone assist please?

DELETE FROM tbl_queries
WHERE QueryTypeID = 2 AND QueryUserID = '1' AND QueryID NOT IN
(SELECT QueryID FROM tbl_queries WHERE QueryTypeID = 2 AND QueryUserID = '1' ORDER BY QueryID DESC LIMIT 4)

Essentially, I want to delete every past query stored for that user of that query type, but retain the most recent 4.

Eventually solved this one myself, you simply wrap the subquery within another subquery, seems ridiculously pointless, what were MySQL thinking!?

I have highlighted the additional elements in red incase anyone else is trying to achieve the same:

DELETE FROM tbl_queries
WHERE QueryTypeID = 2 AND QueryUserID = '1' AND QueryID NOT IN
(SELECT * FROM (SELECT QueryID FROM tbl_queries WHERE QueryTypeID = 2 AND QueryUserID = '1' ORDER BY QueryID DESC LIMIT 4) AS TEMPTABLE)

bradgrafelman

Or utilize the fact that just about any subquery (or perhaps just any subquery) could be rewritten as a JOIN instead. For example, this (untested -- EDIT: has now been tested 🙂) might work as well:

DELETE FROM tbl_queries
	LEFT JOIN
	(
		SELECT QueryID
		FROM tbl_queries
		WHERE
			QueryTypeID = 2
			AND QueryUserID = '1'
		ORDER BY QueryID DESC
		LIMIT 4
	) AS exemptRows
	ON tbl_queries.QueryID = exemptRows.QueryID
WHERE
	QueryTypeID = 2
	AND QueryUserID = '1'
	AND exemptRows.QueryID IS NULL

EDIT: Hmm, that still doesn't get around the limitation. Frustrating indeed.

EDIT2: Woops, nevermind - it does indeed work. Also corrected a logic error (should've been 'IS NULL').

_sh

Thanks Brad, you've probably noticed that I've found a lovely trick that does the job, very strange though, I wonder if MySQL tackled this in later versions of MySQL as I'm not sure whether my host are using the most recent.