1

How can we achieve in getting the max and min values that is group by group.

eg query, select country,max(size) from tablename group by country, this will query each max values of a group column

data output is ->

country  |  size
myr      |  456
usd      |  123
eur      |  909

expected output should only show the max size which is 909 from eur

country  |  size
eur      |  909
Serg
  • 22,285
  • 5
  • 21
  • 48
Farid Arshad
  • 334
  • 2
  • 14
  • `select max(size) from yourtablename` and get your output. do not add group and you will get result – Ajay2707 Apr 19 '21 at 06:15
  • @Ajay2707 , this will only return the max value of the size whereby the usecase im looking should return both country and maxvalue – Farid Arshad Apr 19 '21 at 06:20
  • 1
    order by max size desc take first row. `LIMIT 1`, `Top(1)`, .. depending on your DBMS. – Serg Apr 19 '21 at 06:30
  • 1
    probably `select * from table order by size desc limit 1` or even `select *, max(size) from df` – Onyambu Apr 19 '21 at 06:36

4 Answers4

0

You can use window function -

select country, size from 
(select country, size, row_number() over (order by size desc) rn from mytable) rs 
where rs.rn=1

You did not mention DB so i assumed most popular DB syntax.

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
0

Please check

Declare @t table(country varchar(50),  size int)
insert into @t values ( 'myr', 456),('usd',123),('eur', 909)


select t.* from @t t
join
( select max(size) as id from @t) maxval
on t.size = maxval.id

see this answer: SQL Query to get column values that correspond with MAX value of another column?

Ajay2707
  • 5,690
  • 6
  • 40
  • 58
0

For SQLite, MySQL v5.6 you can simply use:

SELECT country, MAX(size) FROM tablename

For MS-SQL you can use TOP():

SELECT TOP(1) country, size FROM tablename 
ORDER BY size DESC

For PostgreSQL, MySQL v5.7+ you can use LIMIT:

SELECT country, size FROM tablename 
ORDER BY size DESC LIMIT 1
iacob
  • 20,084
  • 6
  • 92
  • 119
0

It seems like you want to get the row having the max size. So, you can order the table by Size in descending order. then pick the top row of it. So, you may use the following query.

SELECT Country, Size
FROM tableName
ORDER BY Size DESC
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY

Also, you can tweak the above query if you really want to use Group By in your query, use the following-

SELECT Country, MAX(Size) AS Size
FROM tableName
GROUP BY Country
ORDER BY Size DESC
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY
Adnan Sharif
  • 919
  • 7
  • 17