CODEWITHSUNDEEP
phpmysql
user1601513
user1601513

Reputation: 153

mysql returning the lowest column value in GROUP BY query

I've been using this query in a program for quite some time now but it has started giving me the wrong result. I am expecting to return one column name per unique LEVEL and then the lowest z_index value found in that level.

SELECT * FROM Conrad WHERE ProductID = '1' GROUP BY level ORDER BY z_index

It used to return something like this:

ProductID    level    z_index
==============================
   1          One       10
   1          Two       20
   1          Three     30

That is the expected result. But it is now returning this:

ProductID    level    z_index
==============================
   1          One       83
   1          Two       224
   1          Three     308

I still get one per level but it is now returning any z_index value found for that level.

Did something change in MySql or has my query been bad forever?

Update Saturday January 21: Now when I add in more column names that I will need the query returns the correct values for the z_index but the values of file_name and file_loc seem to be from the first row of the level and not of the row that contains the correct z_index value.

What caused that to happen?

SELECT ProductID, level, min(z_index), file_name, file_loc FROM Conrad WHERE ProductID = '1' GROUP BY level ORDER BY z_index

ProductID    level    z_index   file_name   file_loc
====================================================
   1          One       10        wrong.png    ?
   1          Two       20        file.png     ?
   1          Three     30        names.png    ?

Upvotes: 1

Views: 93

Answers (2)

reza
reza

Reputation: 1507

your query is little wrong for minimum value. try following query

SELECT ProductID
     , level
     , MIN(z_index) z_index
  FROM Conrad 
 WHERE ProductID = 1
 GROUP 
    BY ProductID
     , level 
 ORDER 
    BY z_index

Upvotes: 2

raphael75
raphael75

Reputation: 3238

Try this:

select
  productid,
  level,
  min(z_index)
from
  Conrad 
WHERE 
  ProductID = '1' 
GROUP BY 
  level 
ORDER BY z_index

Upvotes: 0

Related Questions