The first command works as expected, in that it returns every unique combination of those two items, and no more. The python code I provided fails to return anything.
The second command returns only the unique occurences of the first item, with presumably the first encountered value of the second item. The python code produces the same result.
The third command returns the same as the first, but with the additional count(*) item. The python code produces the same result.
Thanks for having a look.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm using ActiveState Python 2.3, mysql-python 1.2.0, and MySQL 4.1.
I'm observing unexpected behaviour when using "group by" with multiple items in a select query:
import MySQLdb
db = MySQLdb.connect(host='localhost',user='user',passwd='somepassword',db='somedb')
Cursor = db.cursor()
This one doesn't return any records
sql = "select sevfirst_yr, first_yr_rd from defspraycorr where sevfirst_yr <> 9999 group by sevfirst_yr, first_yr_rd"
This one does, albeit not the results I want
sql = "select sdef1974, first_yr_rd from defspraycorr where sevfirst_yr <> 9999 group by sevfirst_yr"
This one provides the correct results, although I had to add the count(*) where I don't when working directly with MySQL
sql = "select sevfirst_yr, first_yr_rd, count(*) from defspraycorr where sevfirst_yr <> 9999 group by sevfirst_yr, first_yr_rd"
Cursor.execute(sql)
Recs = Cursor.fetchall()
for record in Recs:
print record[0], record[1]
db.close()
Using count(*) solves my problem, but since it's not required in MySQL, I thought this might be a bug.
Thanks for the software and hard work.
What happens when you execute those statements in the mysql command-line client?
Sorry, should have included that.
The first command works as expected, in that it returns every unique combination of those two items, and no more. The python code I provided fails to return anything.
The second command returns only the unique occurences of the first item, with presumably the first encountered value of the second item. The python code produces the same result.
The third command returns the same as the first, but with the additional count(*) item. The python code produces the same result.
Thanks for having a look.