CODEWITHSUNDEEP
sqlsql-servergroup-bydistinct
Hussain Rauf
Hussain Rauf

Reputation: 25

Select top 1 column value in the group by clause?

I am joining two tables and my query contains the group by clause. There is one column in the join table which I want to show in the result but doesn't want it to be part of group by because values are different in column values. I just want to show any top value of that column.

I tried to use Distinct and Top 1 and others but nothing works for me.

SELECT t1.Code, t2.Details, t2.FineDate ,Sum(t1.Amount) from Emp_Actions t1 
INNER JOIN Emp_Fines  ON  t1.Code =  t2.Code 
where (t1.Code = "MYParameter" or @MyParameter = "" )
group by t1.Code,t2.Details,t2.FineDate

Please note that I am using StoredProcedure and code be specific or all. My actual query is too big and I just made a sample to elaborate my issue. I need Top 1 FineDate, I don't want it to part of group by however I want to show it.

Upvotes: 0

Views: 231

Answers (3)

SAS
SAS

Reputation: 4035

One way to get only one value, is using MAX(col) or MIN(col) in your SELECT statement, where col is the column that you don't want to group on. One advantage is that you get somewhat consistent values (first or last in order).

SELECT t1.Code, t2.Details, t2.FineDate, MAX(col) my_col, Sum(t1.Amount) from Emp_Actions t1

Also, there are more advanced analytical window functions (first_value for example), if you want to go that way, to have more control over which value is chosen, also depending on other column values.

https://learn.microsoft.com/en-us/sql/t-sql/functions/analytic-functions-transact-sql?view=sql-server-2017

Upvotes: 1

Aakash Singh
Aakash Singh

Reputation: 1062

You can use use subquery for required result.

I am giving you a sample code :

Select
t1.Code, t2.Details, t2.FineDate,Sum(t1.Amount) 
,(select top 1 a.ColA from Emp_Fines a where a.Code = t1.Code order by ColOfSort Asc/Desc) as RequiredColumn
from Emp_Actions t1 
INNER JOIN Emp_Fines t2 ON  t1.Code =  t2.Code 
where (t1.Code = "MYParameter" or @MyParameter = "" )
group by t1.Code,t2.Details,t2.FineDate

Where RequiredColumn is the column that you wants in the result but don't want to use in group by

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269663

Use MIN() or MAX():

select t1.Code, t2.Details, t2.FineDate, Sum(t1.Amount),
       max(<your column here>) as column+name
from Emp_Actions t1 join
     Emp_Fines  
     on  t1.Code = t2.Code 
where (t1.Code = "MYParameter" or @MyParameter = "" )
group by t1.Code, t2.Details, t2.FineDate

Upvotes: 0

Related Questions