CODEWITHSUNDEEP
sqldatabasegreatest-n-per-group
balaji
balaji

Reputation: 1304

SQL query to select distinct row with minimum value

I want an SQL statement to get the row with a minimum value.

Consider this table:

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

How do I select the ids that have the minimum value in the point column, grouped by game? Like the following:

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

Upvotes: 87

Views: 288692

Answers (11)

Nathan
Nathan

Reputation: 8981

Most of the answers use an inner query. I am wondering why the following isn't suggested.

select
   *
from
   table
order by
   point
fetch next 1 row only   // ... or the appropriate syntax for the particular DB

This query is very simple to write with JPAQueryFactory (a Java Query DSL class).

return new JPAQueryFactory(manager).
   selectFrom(QTable.table).
   setLockMode(LockModeType.OPTIMISTIC).
   orderBy(QTable.table.point.asc()).
   fetchFirst();

Upvotes: 0

thenor
thenor

Reputation: 1

This is portable - at least between ORACLE and PostgreSQL:

select t.* from table t 
where not exists(select 1 from table ti where ti.attr > t.attr);

Upvotes: 0

Mohamed Ashraf
Mohamed Ashraf

Reputation: 71

SELECT DISTINCT 
FIRST_VALUE(ID) OVER (Partition by Game ORDER BY Point) AS ID,
Game,
FIRST_VALUE(Point) OVER (Partition by Game ORDER BY Point) AS Point
FROM #T

Upvotes: 2

The Conspiracy
The Conspiracy

Reputation: 3973

This alternative approach uses SQL Server's OUTER APPLY clause. This way, it

  1. creates the distinct list of games, and
  2. fetches and outputs the record with the lowest point number for that game.

The OUTER APPLY clause can be imagined as a LEFT JOIN, but with the advantage that you can use values of the main query as parameters in the subquery (here: game).

SELECT colMinPointID
FROM (
  SELECT game
  FROM table
  GROUP BY game
) As rstOuter
OUTER APPLY (
  SELECT TOP 1 id As colMinPointID
  FROM table As rstInner
  WHERE rstInner.game = rstOuter.game
  ORDER BY points
) AS rstMinPoints

Upvotes: 0

Ken Clark
Ken Clark

Reputation: 2530

Use:

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

Upvotes: 84

Shiroy
Shiroy

Reputation: 1838

This is another way of doing the same thing, which would allow you to do interesting things like select the top 5 winning games, etc. 

 SELECT *
 FROM
 (
     SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Point) as RowNum, *
     FROM Table
 ) X 
 WHERE RowNum = 1

You can now correctly get the actual row that was identified as the one with the lowest score and you can modify the ordering function to use multiple criteria, such as "Show me the earliest game which had the smallest score", etc.

Upvotes: 46

user330315
user330315

Reputation:

As this is tagged with sql only, the following is using ANSI SQL and a window function:

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;

Upvotes: 19

Tshultrim Dorji
Tshultrim Dorji

Reputation: 1

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;

Upvotes: 0

samthebrand
samthebrand

Reputation: 3090

Ken Clark's answer didn't work in my case. It might not work in yours either. If not, try this:

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

Upvotes: 1

Aspirant
Aspirant

Reputation: 2278

This will work

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

Upvotes: 23

www
www

Reputation: 4391

Try:

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

Upvotes: -4

Related Questions