CODEWITHSUNDEEP
mysqlsql
Blazed
Blazed

Reputation: 11

Find largest value among repeated entries in an sql table

I have searched desperately for the answer to a seemingly easy query to pull off- but no luck in finding the answer so far. I am hoping someone can at least point me in the right direction.

Say I have a table with rope colors and sizes (inches) with the columns: color, inches.

If the values in color and inches, respectively, are:

Red          38
Red          45
Yellow       12
Blue         85
Blue         12
Blue         18

I want to query for the longest rope of a single color. Ideally, the query would return:

Red        45
Yellow     12
Blue       85

How could I search for this using SQL queries?

Thank you all!

Upvotes: 0

Views: 71

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

You need to research GROUP BY statement and read about aggregate functions.

MySQL Reference manual can be a great source of knowledge in this case.

select
  color, max(inches) as longest_inches
from
  yourtable
group by color

It works by grouping all rows with the same value in column color and then retrieving the maximum value amongst each group thus giving you the expected output

Upvotes: 3

Related Questions