I am working with Microsoft SQL Server and want to find E_ID and E_Name where T1 T2 has the MAX value.

I have two steps to reach the necessary result:

1. Find the sum of two columns AS "total" in a table
2. Find the row that contains the maximum value from total

Table named "table1" looks like the following (T2 may contains NULL values):

E_ID	E_Name	T1	T2
1	Alice	55	50
2	Morgan	60	40
3	John	65
4	Monica	30	10
5	Jessica	25
6	Smith	20	5

Here is what I've tried:

SELECT 
    E_ID, E_Name, MAX(total) AS max_t
FROM 
    (SELECT  
         E_ID, E_Name, ISNULL(T1, 0)   ISNULL(T2, 0) AS total
     FROM    
         table1) AS Q1;

I get this error:

'Q1.E_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I get the result only when I keep MAX(total) AS max_t in the SELECT part but I also want to have the columns E_ID and E_Name.

I would appreciate any suggestion you have!

CodePudding user response：

Try this - just sort by the Total column in a descending fashion, and take the first row in the result:

SELECT TOP (1)
    Q1.E_ID, Q1.E_Name, Q1.Total
FROM 
    (SELECT  
         E_ID, E_Name, ISNULL(T1, 0)   ISNULL(T2, 0) AS Total
     FROM    
         table1) AS Q1
ORDER BY
    Q1.Total DESC;

CodePudding user response：

You can use the query:

SELECT top 1 E_ID, E_Name, (T1 T2) as Total 
FROM Table1 
GROUP BY E_ID,E_Name 
ORDER BY Total desc

CodePudding user response：

You don't need subqueries:

SELECT 
    TOP 1
    E_Name
FROM 
    tab
ORDER BY 
    COALESCE(T1,0) COALESCE(T2,0) DESC

Fiddle here: https://dbfiddle.uk/?rdbms=sqlserver_2019l&fiddle=0cca8f7ca28dc45dd1ece505f9176bdc.

CodePudding user response：

SELECT

TOP (1) , E_ID, E_Name, ISNULL(T1, 0) ISNULL(T2,0) AS Total

FROM table1

ORDER BY Total DESC;

CodePudding user response：

If you wanna see all the records you just need to apply the GROUP BY clause.

SELECT E_ID , E_Name , MAX(total) AS max_t FROM (SELECT
E_ID , E_Name , ISNULL(T1, 0) ISNULL(T2, 0) AS total FROM
table1 ) AS Q1 GROUP BY E_ID , E_Name;

If you want to see only the MAX value in the dataset you just need to apply the TOP 1 (for one record in the result), then sum the T1 and T2 as total and then apply the ORDER BY DESC;

SELECT TOP 1 E_ID , E_Name , (T1 T2) AS total FROM table1 ORDER BY total DESC