CODEWITHSUNDEEP
mysqlsqldatabase
t0mkaka
t0mkaka

Reputation: 1851

Group By clause not showing the same row values?

I have table with the following columns and data 

filename1       filename2       hd
S5000L00.dat    S5111L01.dat    0.46
S5111L00.dat    S5000L01.dat    0.46
S5001L00.dat    S5002L01.dat    0.47
S5002L00.dat    S5001L01.dat    0.47
S5003L00.dat    S5002L01.dat    0.38
S5002L00.dat    S5003L01.dat    0.38
S5000L00.dat    S5000L01.dat    0.21*
S5002L00.dat    S5002L01.dat    0.42
S5002L01.dat    S5000L00.dat    0.42
S5001L00.dat    S5000L03.dat    0.24

Now I want to group by filename1 and view the minimum hd for each file

When I run the following query 

Select filename1, filename2, min(hd) as hd 
from data group by filename1 
order by hd asc;

I get the followiing result

filename1       filename2       hd
S5000L00.dat    S5111L01.dat    0.21*
S5001L00.dat    S5002L01.dat    0.24
S5003L00.dat    S5002L01.dat    0.38
S5002L00.dat    S5001L01.dat    0.38
S5002L01.dat    S5000L00.dat    0.42
S5111L00.dat    S5000L01.dat    0.46

The starred rows are where is the problem. In the table the filename2 is S5000L01.dat but in the resultset the filname2 is S5111L01.dat . I want the same filename but I couldn't get to form a query.

Thanks

Upvotes: 1

Views: 39

Answers (1)

Alma Do
Alma Do

Reputation: 37365

Use JOIN:

SELECT
  g.filename1,
  data.filename2,
  g.hd
FROM
  data
    INNER JOIN
      (SELECT 
        filename1, 
        min(hd) AS hd 
      FROM 
        data 
      GROUP BY 
        filename1) AS g
      ON data.filename1=g.filename1
      AND data.hd=g.hd
ORDER BY g.hd ASC

the reason why your query isn't working: you're relying on MySQL GROUP BY extension. This extension allows to mix non-grouped columns with grouping conditions, but server is free to chose any row. That's why there's no guarantee that desired filename2 will be selected. In fact, your original query isn't valid in normal SQL, it's MySQL feature - but I strongly recommend you do not rely on it.

Upvotes: 1

Related Questions