CODEWITHSUNDEEP
sql-server-2012
SSMSJ
SSMSJ

Reputation: 219

T-SQL How can I get the most common value from a result set

Trying to get a single result for the most common date in a result set :

SELECT col1, col2,  
(SELECT MIN(CONVERT(VARCHAR,[date], 103)) FROM TABLE )  AS [Date]
FROM TABLE WHERE [Date] BETWEEN '20160101' AND '20160131'

Results :

enter image description here

So I just want 15/01/2016. I know I need to use a subquery and a count for this and have tried many different solutions, being a newbie I'm finding subqueries harder to get my head around than JOINs especially correlated nested subqueries.

Upvotes: 0

Views: 46

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Group by date, order by count descending and select top 1:

select top 1 with ties [date] 
from table 
where [date] between '20160101' and '20160131'
group by [date]
order by count(*) desc

with ties will select multiple dates if max count can be the same for some dates.

Upvotes: 3

Related Questions