0

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.

5
  • There is no problem in JOINing a temporary table. Jan 14, 2014 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, 2014 at 14:27
  • @ShlomiNoach in mysql, it is not possible to open a table twice. Thus, it cannot JOINed to itself.
    – Googlebot
    Jan 14, 2014 at 14:35
  • @Mihai sorry that was a typo. The first and last rows in a category if col1='x'
    – Googlebot
    Jan 14, 2014 at 14:35
  • @Ali, correct you are. I wasn't thinking about self join. Jan 14, 2014 at 18:20

1 Answer 1

1
SELECT * FROM test WHERE id IN
(SELECT MIN(id) FROM test GROUP BY category HAVING col='x' 
UNION SELECT MAX(id) FROM test GROUP BY category HAVING col='x')

Fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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