with - sql select max value

SQL查詢以選擇具有最小值的不同行 (6)

我想要一個SQL語句來獲取具有最小值的行。

考慮一下這個表：

id  game   point
1    x      5
1    z      4
2    y      6
3    x      2
3    y      5
3    z      8

如何選擇point列中具有最小值的ID，按遊戲分組？像這樣：

id  game   point    
1    z      4
2    y      6
3    x      2

使用：

SELECT tbl.*
FROM TableName tbl
  INNER JOIN
  (
    SELECT Id, MIN(Point) MinPoint
    FROM TableName
    GROUP BY Id
  ) tbl1
  ON tbl1.id = tbl.id
WHERE tbl1.MinPoint = tbl.Point

嘗試：

select id, game, min(point) from t
group by id

由於這僅使用sql標記，因此以下是使用ANSI SQL和窗口函數：

select id, game, point
from (
  select id, game, point, 
         row_number() over (partition by game order by point) as rn
  from games
) t
where rn = 1;

肯克拉克的答案在我的案例中沒有用。它可能也不適用於你的。如果沒有，試試這個：

SELECT * 
from table T

INNER JOIN
  (
  select id, MIN(point) MinPoint
  from table T
  group by AccountId
  ) NewT on T.id = NewT.id and T.point = NewT.MinPoint

ORDER BY game desc

這會奏效

select * from table 
where (id,point) IN (select id,min(point) from table group by id);

SELECT * from room
INNER JOIN
  (
  select DISTINCT hotelNo, MIN(price) MinPrice
  from room
 Group by hotelNo
  ) NewT   
 on room.hotelNo = NewT.hotelNo and room.price = NewT.MinPrice;