Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

1 Answer 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

share|improve this answer

Your Answer

 
discard

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.