CODEWITHSUNDEEP
sqldatabase
lai hang
lai hang

Reputation: 79

Select the row in same group with minimum value?

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

I want to select the record with minimum point in game x,game y, game z

Upvotes: 0

Views: 63

Answers (8)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You should run below simple query :

select game, min(point) point from [table_name] group by game

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10711

I guess the simplest way how to find all values (all attributes) you need even without GROUP BY is as follows:

SELECT t1.id, t1.point, t1.[group]
FROM your_table t1
WHERE t1.point = (
      SELECT min(t2.point) 
      FROM your_table t2 
      WHERE t2.[group] = t1.[group]
     )

Upvotes: 0

Souravi Sinha
Souravi Sinha

Reputation: 123

In Oracle, this can be done as:-

select id,game,point As MinPoint 
from table_name where 
point in (select min(point) from table_name group by game);

The output on similar kind of data is as follows:-

Select * from sql_prac;

ID DEPT SAL

     1 A                          2000 
     2 B                        200001 
     3 C                        200100 
     4 D                        200050 
     5 A                        205000 
     6 B                        260000 
     7 C                        200007 
     8 D                        202000 
     9 R                       2006600

9 rows selected

select id,game,point As MinPoint from sql_prac where point in (select min(point) from sql_prac group by game);

    ID DEPT                   MINPOINT

     1 A                          2000 
     2 B                        200001 
     4 D                        200050 
     7 C                        200007 
     9 R                       2006600

Upvotes: 0

Chris Travers
Chris Travers

Reputation: 26474

If you want the whole record, you will have a little more work to do:

SELECT * from table_name
 WHERE (game, point) IN (select game, min(point) from table_name
                       GROUP BY game);

Alternatively you could use a CTE, or window functions.

Upvotes: 2

jarlh
jarlh

Reputation: 44805

Many answers here, but since we don't know which dbms OP is using, I'd go with the most general answer:

select t1.id, t1.game. t1.point
from tablename t1
join (select game, min(point) as minpoint
      from tablename
      group by game) t2
    on  t1.game = t2.game
    and t1.point = t2.minpoint

Have a derived table (i.e. the subquery) with a GROUP BY to find each game's min point. JOIN with that result.

Upvotes: 0

iamsankalp89
iamsankalp89

Reputation: 4749

USe aggregate function with group by , Try this: 

select game, MIN(point)
from Table_name
group by game;

You can give to name the Aggregate function

select game, MIN(point) as Minimum_Points
from Table_name
group by game;

Upvotes: 0

Balakrishna Gondesi
Balakrishna Gondesi

Reputation: 118

you can also use over partition by.

Query:

 select id,game,min_point from (
    select id, GAME, POINT,min(POINT) over (PARTITION by GAME) as MIN_POINT
    from YOUR_TABLE) where point = min_point

Result:

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

Upvotes: 0

Ullas
Ullas

Reputation: 11566

Use MIN aggregate function with GROUP BY.

Query

select game, min(point) as minPoint
from your_table_name
group by game;

Upvotes: 0

Related Questions