Sql How To Find Rows Which Have Highest Value Of Specific Column

For example, the table has columns MYINDEX and NAME. MYINDEX | NAME ================= 1 | BOB 2 | BOB 3 | CHARLES Ho do I find row with highest MYINDEX for spec

Solution 1:

SELECT Max(MYINDEX) FROM table WHERE NAME = [insertNameHere]

EDIT: to get the whole row:

Select*//never do this really
FromTableWhere MYINDEX = (SelectMax(MYINDEX) FromTableWhere Name = [InsertNameHere]

Solution 2:

There are several ways to tackle this one. I'm assuming that there may be other columns that you want from the row, otherwise as others have said, simply name, MAX(my_index) ... GROUP BY name will work. Here are a couple of examples:

SELECT
    MT.name,
    MT.my_index
FROM
(
    SELECT
        name,
        MAX(my_index) AS max_my_index
    FROM
        My_Table
    GROUPBY
        name
) SQ
INNER JOIN My_Table MT ON
    MT.name = SQ.name AND
    MT.my_index = SQ.max_my_index

Another possible solution:

SELECT
    MT1.name,
    MT1.my_index
FROM
    My_Table MT1
WHERENOTEXISTS
    (
        SELECT*FROM
            My_Table MT2
        WHERE
            MT2.name = MT1.name AND
            MT2.my_index > MT1.my_index
    )

Solution 3:

SELECTMAX(MYINDEX) FROMtableWHERE NAME ='BOB'

For the whole row, do:

SELECT*FROMtableWHERE NAME ='BOB'AND MyIndex = (SELECTMax(MYINDEX) fromtableWHERE NAME ='BOB')

Solution 4:

If you wanted to see the highest index for name = 'Bob', use:

SELECTMAX(MYINDEX) AS [MaxIndex]
FROM myTable
WHERE Name ='Bob'

Solution 5:

If you want to skip the inner join, you could do:

SELECT*FROMtableWHERE NAME ='BOB'ORDERBY MYINDEX DESC LIMIT 1;