SQL group by输出特定数据

Question

ID     tag      index 
001       1     fsklgg
001       2     segwrh
001       3     esfjkg
002       4     seggrg
002       5     gehewv
002       6     egwgsg
003       1     esgges
003       4     yjkdsa
003       9     wrfsbb

我想按规则输出

1. each ID will only have one output
2. the output will be  sort by "tag" which is the largest

像这样

ID     tag      index 
001       3     esfjkg
002       6     egwgsg
003       9     wrfsbb

但我的回答总是

ID     tag      index 
001       1     fsklgg
002       4     seggrg
003       1     esgges

我使用GROUP BY标签，但答案将是最小的。我尝试使用DESC的ORDER，希望答案会改变，但它不起作用。有人能教我如何接近答案吗？或者我应该使用什么命令？

我的源代码是

SELECT
t1.`藥品代碼` AS ID,
t1.`藥價參考截止日期` AS tag,
t1.`藥價參考金額` AS `index`,
t1.`藥品英文名稱` AS index2,
t1.`藥價參考日期` AS index3,
t1.ATC_CODE AS index4
FROM
    `健保用藥品項查詢檔` AS t1
GROUP BY
ID
HAVING
id IS NOT NULL
ORDER BY
id ASC,
tag ASC

使用MariaDB

Answer 1

SELECT t1.ID,
       t1.tag,
       t1.index
FROM yourTable t1
INNER JOIN
(
    SELECT ID, MAX(tag) AS tag
    FROM yourTable
    GROUP BY ID
) t2
    ON t1.ID  = t2.ID AND
       t1.tag = t2.tag
ORDER BY t1.tag

使用子查询：

SELECT t1.ID,
       t1.tag,
       t1.index
FROM yourTable t1
WHERE t1.tag = (SELECT MAX(tag) FROM yourTable WHERE ID = t1.ID)

Answer 2

也可以试试这个：

SELECT a.* 
FROM tableA a
WHERE EXISTS(
     SELECT ID, tag FROM (
              SELECT ID, MAX(tag) tag FROM tableA GROUP BY ID) t 
WHERE t.id = a.id AND t.tag = a.tag) 
ORDER BY a.tag DESC