CODEWITHSUNDEEP
mysqlsql
Eamonn
Eamonn

Reputation: 1320

MySQL Group By filter results by order

I have a table with the following columns:

TblID - AUTO_INCREMENT
IndividualID
BranchID
TreeID

Only TblID is unique. I wish to query the table where TreeID = x and BranchID = y, to retrieve all the Individual records that share those characteristics. There could be many Individual records for one IndividualID, but I only want to retrieve the first one created (thus with the lowest TblID value) per IndividualID.

So I changed from:

SELECT * FROM tbl WHERE TreeID = :TreeID AND BranchID= :BranchID

To

SELECT * FROM `rep_options` WHERE TreeID = :TreeID AND BranchID= :BranchID
    GROUP BY IndividualID ORDER BY TblID ASC

This appears to be working, but I'm not comfortable enough with MySQL Groups to be confident that I'm getting to the right point by the right means.

Is this the right way to filter my returns to just one record per IndividualID, sorted by TblID?

Upvotes: 0

Views: 77

Answers (2)

xQbert
xQbert

Reputation: 35353

SELECT MIN(TblID) AS TblID, IndividualID, TreeID, BranchID
FROM `rep_options` 
WHERE TreeID = :TreeID AND BranchID= :BranchID
GROUP BY IndividualID, TreeId, BranchID
ORDER BY TblID ASC

I explicitly define the group bys for non-aggregate values. It might be a bit more of a performance hit, but my results are less likely to be incorrect when the Group by extensions are enabled in MySQL (which they are by default)

IN this case since TreID and BrancID are in the where clause and you were already grouping by IndividualID it shouldn't matter... meaning you don't need to have TreeID, branchID, but if one of the where clause criteria were later removed, it would matter.

Here's with a select * this may have a bit more ovehead but it should resolve the issue stemming from mySQLs extended Group by features.

The inner select gets the min ID and the individual. This way the group by extensions no longer become an issue.

SELECT *
FROM `rep_options` a
INNER JOIN 
 (Select min(tblID) myID,  IndividualID
  FROM `rep_options` 
  WHERE TreeID = :TreeID AND BranchID= :BranchID
  group by IndividualID) b
  on a.Myid=a.tblID 
 and B.IndividualID = A.IndividualID
ORDER BY TblID ASC

Upvotes: 1

Aziz Shaikh
Aziz Shaikh

Reputation: 16544

If you need the lowest TblID value then use the MIN function, like this:

SELECT MIN(TblID) AS TblID, IndividualID, TreeID, BranchID
FROM `rep_options` 
WHERE TreeID = :TreeID AND BranchID= :BranchID
GROUP BY IndividualID 
ORDER BY TblID ASC

Ideally the GROUP BY clause should contain all the non-aggregated columns mentioned in the SELECT clause but it should not matter in this case because all rows will have same TreeID and BranchID values (due to the WHERE clause).

Upvotes: 0

Related Questions