CODEWITHSUNDEEP
sqldatabasems-accessselect
Sami Meskaoui
Sami Meskaoui

Reputation: 49

SQL query : select max of 2 columns of same row

Column1 | Column2
--------+-------- 
1       |    1
3       |    2
1       |    3
2       |    1
2       |    4

I want the max of column1 and after that the max of column2 : in this example --> 3 : 2

Upvotes: 1

Views: 491

Answers (5)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

Use the following :

select column1, column2
  from tab 
 where column1 = ( Select max(column1) from tab );

SQL Fiddle Demo

Upvotes: 1

DxTx
DxTx

Reputation: 3347

If you want the whole row that has the max value of column1, try this... 

SELECT TOP 1 * 
FROM   tablename 
ORDER  BY column1 DESC

Sample Date

+---------+---------+
| Column1 | Column2 |
+---------+---------+
| 1       |       1 |
| 3       |       2 |
| 1       |       3 |
| 2       |       1 |
| 2       |       4 |
+---------+---------+

Output

+----------+----------+
| Column1  |  Column2 |
+----------+----------+
| 3        |        2 |
+----------+----------+

if you want the max values of both column1 and column2, try this...

SELECT MAX(column1) as column1, 
       MAX(column2) as column2
FROM   tablename

Sample Date

+---------+---------+
| Column1 | Column2 |
+---------+---------+
| 1       |       1 |
| 3       |       2 |
| 1       |       3 |
| 2       |       1 |
| 2       |       4 |
+---------+---------+

Output

+----------+----------+
| Column1  |  Column2 |
+----------+----------+
| 3        |        4 |
+----------+----------+

Upvotes: 2

Aaron Dietz
Aaron Dietz

Reputation: 10277

For your sample data, this will work:

SELECT Column1, Column2
FROM yourTable
WHERE Column1 = (SELECT MAX(Column1) FROM yourTable)

Based on your comment

and after that the max of column2

You probably want this instead:

SELECT Column1, MAX(Column2) as Column2
FROM yourTable
WHERE Column1 = (SELECT MAX(Column1) FROM yourTable)
GROUP BY Column1

This handles cases where there are multiple rows with the MAX(Column1), and only returns the row among those with MAX(Column2)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269493

I would use order by:

select column1, column2
from t
order by column1 desc, column2 desc
fetch first 1 row only;

I see you are using MS Access, so this is phrased as:

select top (1) column1, column2
from t
order by column1 desc, column2 desc;

The only challenge with this approach is that is might return multiple rows, if you have duplicates in the data. If this is an issue, either include a unique id as the last key in the order by:

order by column1 desc, column2 desc, id;

Or use aggregation:

select max(column1), max(column2)
from (select top (1) column1, column2
      from t
      order by column1 desc, column2 desc
     ) as t;

The max() only serves to reduce the result set to a single row (in the event of duplicates). Because all the values from the subquery would be the same, almost any aggregation function would work.

Upvotes: 2

Dorado
Dorado

Reputation: 411

I'm hard time to interpret your table but there are built-in functions in SQL that would help in your queries.

SELECT MAX(column1), MAX(column2)
FROM table_name
WHERE condition;

Upvotes: 0

Related Questions