例如,表格包含MYINDEX和NAME两列。
MYINDEX | NAME
=================
1 | BOB
2 | BOB
3 | CHARLES
如何找到特定名称的最高MYINDEX行?例如,我想要查找名称为“BOB”的ROW-2。
SELECT MAX(MYINDEX) FROM table WHERE NAME = [insertNameHere]
编辑:要获取整行:
Select * //never do this really
From Table
Where MYINDEX = (Select Max(MYINDEX) From Table Where Name = [InsertNameHere]
SELECT MAX(MYINDEX) FROM table
WHERE NAME = 'BOB'
对于整行,请执行以下操作:
SELECT * FROM table
WHERE NAME = 'BOB'
AND MyIndex = (SELECT Max(MYINDEX) from table WHERE NAME = 'BOB')
有几种方法可以解决这个问题。我假设您可能需要行中的其他列,否则就像其他人所说的那样,只需使用名称,MAX(my_index) ... GROUP BY name
就可以解决问题。以下是一些示例:
SELECT
MT.name,
MT.my_index
FROM
(
SELECT
name,
MAX(my_index) AS max_my_index
FROM
My_Table
GROUP BY
name
) SQ
INNER JOIN My_Table MT ON
MT.name = SQ.name AND
MT.my_index = SQ.max_my_index
SELECT
MT1.name,
MT1.my_index
FROM
My_Table MT1
WHERE
NOT EXISTS
(
SELECT *
FROM
My_Table MT2
WHERE
MT2.name = MT1.name AND
MT2.my_index > MT1.my_index
)
SELECT * FROM table WHERE NAME = 'BOB' ORDER BY MYINDEX DESC LIMIT 1;
SELECT MAX(MYINDEX) AS [MaxIndex]
FROM myTable
WHERE Name = 'Bob'
使用
FROM TABLE SELECT MAX(MYINDEX), NAME GROUP BY NAME