a little problem with group and having clause

[sonerkoksal]

let's just think a table has two columns named as A and B. i want to group records with A and filter if B is equal to some value. (i've already tried 'higher than' 'lower than' and etc.) I'm just using this code.

SELECT * FROM table GROUP BY A HAVING (B = 1 OR B = 2);

the table is:

- A - B
1. 1 - 1
2. 1 - 0
3. 2 - 2
4. 2 - 3

sql code that i've given works fine with this table and returns A = 1 and A = 2. but when i change order of the records like this

- A - B
1. 1 - 0
2. 1 - 1
3. 2 - 3
4. 2 - 2

and use the same code, it returns nothing. when we group A with '1' value, code does not look the second record. it looks A's first record and checks '0' value. not looking the second value with '1' value.

what is my wrong here and if there's another way to solve this i'd like to see. Thanks.

 

[r937]

what is wrong is that you are grouping incorrectly

first of all, never use the dreaded, evil "select star"

especially with GROUP BY, for you will surely get unpredictable results

see GROUP BY and HAVING with Hidden Fields

also, use WHERE for filters, not HAVING

HAVING should be used only on aggregate expressions


r937.com | rudy.ca

 

[rac2]

Well that SQL statement is ambiguous. And although the syntax is allow in MySQL, I believe it is not a standard SQL statement, nor a well-defined SQL statement. Normally the GROUP BY clause would have all of the same non-aggregate items as the SELECT clause.

SELECT * FROM table GROUP BY A HAVING (B = 1 OR B = 2);

could be expanded to read

SELECT A, B FROM table GROUP BY A HAVING (B = 1 OR B = 2);

The GROUP BY A implies that there will be rows for each value in column A.
A B
1 ?
2 ?

Now what value should be placed in the B column? Nothing in the SQL statement determines that. This is what I mean by ambiguous.

Since MySQL allows this syntax, it must do something, "Oh, let me see, I will take the value of column B from the first row in the group. Whatever."
A B
1 0
2 3

The HAVING clause specifies to only show the rows with B=1 or B=2. There are none. So nothing is shown.

Take a look at

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html

 

[sonerkoksal]

heh.. i hate that star too but only when writing, reading no errors i have, so nvm... i solved this with another way but thanks anyway