Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a SELECT query of a table with GROUP BY

id     col1     category
1      some     1
2      x        1
3      another  1
4      x        2       // should be removed
5      some     2
6      x        2       // should be removed
7      some     3
8      some     3
9      x        4       // should be removed
10     some     4
11     x        4
12     some     4

I want to SELECT or UPDATE to remove a row if the first or last (with id order) is x (a given value). In other words, I want to skip the first and last row of a category IF col1='x'.

I cannot use JOIN as it is a TEMPORARY table.

share|improve this question
There is no problem in JOINing a temporary table. – Shlomi Noach Jan 14 '14 at 14:26
First or last of what?Of category group?In that case why in category 1 the 'middle' is removed? – Mihai Jan 14 '14 at 14:27
@ShlomiNoach in mysql, it is not possible to open a table twice. Thus, it cannot JOINed to itself. – All Jan 14 '14 at 14:35
@Mihai sorry that was a typo. The first and last rows in a category if col1='x' – All Jan 14 '14 at 14:35
@Ali, correct you are. I wasn't thinking about self join. – Shlomi Noach Jan 14 '14 at 18:20
(SELECT MIN(id) FROM test GROUP BY category HAVING col='x' 
UNION SELECT MAX(id) FROM test GROUP BY category HAVING col='x')


share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.