CODEWITHSUNDEEP
mysqlselectinner-join
stackoverflow
stackoverflow

Reputation: 19444

MySQL how do I only show the max/highest values with the following query?

I have the following tables:

mysql> select * from F_FINDINGS;
+---------+------------+
|   F_ID  | CONFIDENCE |
+---------+------------+
|       1 |        100 | 
|       2 |         70 | 
|       2 |       0.35 | 
|       1 |        100 | 
+---------+------------+

mysql> select * from F_THINGS;
+---------+-------------------+--------+------------+
|   F_ID  |    F_TITLE        |  S_ID  | F_VALUE    |
+---------+-------------------+--------+------------+
|       1 | STUFF A           |      1 |        1.1 |       
|       2 | STUFF C           |      1 |      202.2 | 
|       3 | OBJECT P          |      1 |       10.7 |           
|       4 | Things X          |      2 |        540 |   
|       5 | STUFF D           |      2 |       1080 | 
|       6 | OBJECT M          |      2 |        455 |       
|       7 | Things A          |      3 |        333 |              
|       8 | Things B          |      4 |        825 |         
|       9 | STUFF A           |      1 |      103.4 |          
|      10 | STUFF A           |      1 |       98.4 |
+---------+-------------------+--------+------------+

  mysql> select * from DUMP;
   +--------+----------+
   | D_ID   | D_NAME   |
   +--------+----------+
   |      1 |   E0     | 
   |      2 |   A1     | 
   |      3 |   AB     | 
   +--------+----------+

mysql> select * FROM STUFF;
+--------+--------+-------------------+
|   S_ID |   D_ID |   S_TITLE         |
+--------+--------+-------------------+
|      1 |      1 | plastic           | 
|      2 |      2 | metal             | 
|      3 |      3 | wood              | 
|      4 |      3 | gel               | 
+--------+--------+-------------------+

I'm using the following query

SELECT
d.D_NAME,
s.S_TITLE,
f.F_VALUE,
IF(r.CONFIDENCE IS NULL, 'N/A', CONCAT((r.CONFIDENCE),'%')AS CONFIDENCE

FROM F_THINGS f

JOIN STUFF s ON s.S_ID=f.S_ID
JOIN DUMP d on d.D_ID=s.D_ID

LEFT JOIN F_FINDINGS r ON f.F_ID=r.F_ID;

Desired Result:

+----------+-----------+------------+------------+
| D_NAME   | S_TITLE   | F_VALUE    | CONFIDENCE |
+----------+-----------+------------+------------+
| E0       |  plastic  |      202.2 | 70%        |  
| E0       |  plastic  |       10.7 | N/A        |
| E0       |  plastic  |        1.1 | 100%       |  
| A1       |  metal    |        540 | N/A        | 
| A1       |  metal    |       1080 | N/A        | 
| A1       |  metal    |        455 | N/A        | 
| AB       |  wood     |        333 | N/A        | 
| AB       |  wood     |        825 | N/A        | 
| E0       |  gel      |      103.4 | N/A        | 
| E0       |  gel      |       98.4 | N/A        | 
+----------+-----------+------------+------------+

I only want the highest value shown for each

Upvotes: 0

Views: 429

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

This should do it, however there are no values in STUFF that would give your desired result ('gel' and 'plastic' will need the same S_ID and D_ID, and still be different somehow) which makes the result slightly different from your desired one. Next time you may want to add STUFF so no one has to reverse engineer it ;-)

SELECT
d.D_NAME,
s.S_TITLE,
f.F_VALUE,
IF(MAX(r.CONFIDENCE) IS NULL, 'N/A', CONCAT((MAX(r.CONFIDENCE)),'%')) AS CONFIDENCE
FROM F_THINGS f
JOIN STUFF s ON s.S_ID=f.S_ID
JOIN DUMP d on d.D_ID=s.D_ID
LEFT JOIN F_FINDINGS r ON f.F_ID=r.F_ID
GROUP BY D_NAME, S_TITLE, F_VALUE;

Demo here.

Also added a demo with the values in F_THINGS corrected (different S_ID for lines you want grouped with gel and plastic) to make the grouping you're requiring possible here.

Upvotes: 1

DRapp
DRapp

Reputation: 48139

Then just change your 

LEFT JOIN F_FINDINGS r ON f.F_ID=r.F_ID;

to

LEFT JOIN ( select F_ID, MAX(  CONFIDENCE ) as Confidence
               from F_Findings
               group by F_ID ) as r
   on F.F_ID = r.F_ID

Upvotes: 0

user466764
user466764

Reputation: 1176

try...

ORDER BY confidence DESC

as the last line of your query

Upvotes: 1

Related Questions