CODEWITHSUNDEEP
sqlsql-server
JohnN
JohnN

Reputation: 1010

Selecting the maximum value only for another maximum value

If I have two int data type columns in SQL Server, how can I write a query so that I get the maximum number, at the maximum number of the other column?

Let me give an example. Lets say I have this table:

|  Name | Version | Category | Value | Number | Replication |
|:-----:|:-------:|:--------:|:-----:|:------:|:-----------:|
| File1 | 1.0     | Time     |   123 |      1 |           1 |
| File1 | 1.0     | Size     |   456 |      1 |           1 |
| File2 | 1.0     | Time     |   312 |      1 |           1 |
| File2 | 1.0     | Size     |   645 |      1 |           1 |
| File1 | 1.0     | Time     |   369 |      1 |           2 |
| File1 | 1.0     | Size     |   258 |      1 |           2 |
| File2 | 1.0     | Time     |   741 |      1 |           2 |
| File2 | 1.0     | Size     |   734 |      1 |           2 |
| File1 | 1.1     | Time     |   997 |      2 |           1 |
| File1 | 1.1     | Size     |   997 |      2 |           1 |
| File2 | 1.1     | Time     |   438 |      2 |           1 |
| File2 | 1.1     | Size     |   735 |      2 |           1 |
| File1 | 1.1     | Time     |   786 |      2 |           2 |
| File1 | 1.1     | Size     |   486 |      2 |           2 |
| File2 | 1.1     | Time     |   379 |      2 |           2 |
| File2 | 1.1     | Size     |   943 |      2 |           2 |
| File1 | 1.2     | Time     |   123 |      3 |           1 |
| File1 | 1.2     | Size     |   456 |      3 |           1 |
| File2 | 1.2     | Time     |   312 |      3 |           1 |
| File2 | 1.2     | Size     |   645 |      3 |           1 |
| File1 | 1.2     | Time     |   369 |      3 |           2 |
| File1 | 1.2     | Size     |   258 |      3 |           2 |
| File2 | 1.2     | Time     |   741 |      3 |           2 |
| File2 | 1.2     | Size     |   734 |      3 |           2 |
| File1 | 1.3     | Time     |   997 |      4 |           1 |
| File1 | 1.3     | Size     |   997 |      4 |           1 |
| File2 | 1.3     | Time     |   438 |      4 |           1 |
| File2 | 1.3     | Size     |   735 |      4 |           1 |

How could I write a query so that I selected the maximum Replication value at the maximum Number value? As you can see, in this table, the maximum value in Number is 4 but the maximum number in Replication where Number = 4 is 1

All I can think to do is this:

SELECT MAX(Replication) FROM Table
WHERE Number IS MAX;

which is obviously wrong and doesn't work.

Upvotes: 0

Views: 61

Answers (2)

jpw
jpw

Reputation: 44921

Just use a subquery to find the max number in the where clause. If you just want one single number as the result there is no need to use group by and having (which would make the query a lot more expensive):

select max([replication]) from tab
where number = (select max(number) from tab)

Upvotes: 0

A_Sk
A_Sk

Reputation: 4630

You can try Group By and Having

select max(Replication) from Table_Name group by [Number] having 
[Number]=(select max([Number]) from Table_Name)

Upvotes: 1

Related Questions