How to write the group by query to find highest.

I have a table mstenquiries
It contains city,course,feeddate
i want to find the feeddate having highest no. of enquiries.

select top 1 Convert(varchar,feeddate,106),count(*)  from cuaf.dbo.mstcuaf where sname not like 'Test%' and course<>'' group by  Convert(varchar,feeddate,106) order by 2 desc

This result will give date and highest count
Now i want the course on this day which was having the highest enquiries.

How to find that/
searchsanjaysharmaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SharathConnect With a Mentor Data EngineerCommented:
you can also try like this.
;WITH CTE 
     AS (SELECT *, 
                COUNT(*) 
                  OVER ( 
                    PARTITION BY CONVERT(VARCHAR, feeddate, 106))         feddate_cnt, 
                COUNT(*) 
                  OVER ( 
                    PARTITION BY CONVERT(VARCHAR, feeddate, 106), course) course_cnt 
           FROM CUAF.DBO.mstcuaf 
          WHERE sname NOT LIKE 'Test%' 
            AND course <> '') 
SELECT TOP 1 city, 
             course, 
             feeddate 
  FROM CTE 
 ORDER BY feddate_cnt DESC, 
          course_cnt DESC 

Open in new window

0
 
Surendra NathConnect With a Mentor Technology LeadCommented:
try the below code

;with cte as
(
select top 1 Convert(varchar,feeddate,106) feeddate,count(1)  co
from cuaf.dbo.mstcuaf 
where sname not like 'Test%' 
and course<>'' 
group by  Convert(varchar,feeddate,106) 
order by 2 desc
)
select top 1 course,count(1)
from cuaf.dbo.mstcuaf m
join cte c
on c.feeddate = Convert(varchar,m.feeddate,106)
where m.sname not like 'Test%' 
and m.course<>'' 
group by course
order by 2 desc

Open in new window

0
 
Anthony PerkinsConnect With a Mentor Commented:
So is your table called mstenquiries or cuaf.dbo.mstcuaf ?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Anthony PerkinsConnect With a Mentor Commented:
Assuming it is cuaf.dbo.mstcuaf then something like this should do it:
SELECT TOP 1 course, COUNT(*) EnquiriesByCourse
FROM cuaf.dbo.mstcuaf m
	INNER JOIN (
			SELECT	TOP 1
					feeddate,
					COUNT(*) EnquiriesByDate
			FROM    cuaf.dbo.mstcuaf
			WHERE   sname NOT LIKE 'Test%'
					AND course <> ''
			GROUP BY feeddate
			ORDER BY EnquiriesByDate DESC) x ON m.feeddate = x.feeddate
GROUP BY
		EnquiriesByCourse

Open in new window

0
 
sachitjainConnect With a Mentor Commented:
There could be a generic query to find Nth highest and in this case it is:
;with cte (feedate, countOfEnquiries) as
(
      select Convert(varchar,feeddate,106), count(course) from cuaf.dbo.mstcuaf group by Convert(varchar,feeddate,106)
)
select x.feedate, x.countOfEnquiries
from cte x
where {N-1} = (select count(*)
                  from cte y
                  where y.countOfEnquiries > x.countOfEnquiries)


So in this case, since you are going to find highest value so your N in {N-1} becomes 1 and query becomes
;with cte (feedate, countOfEnquiries) as
(
      select Convert(varchar,feeddate,106), count(course) from cuaf.dbo.mstcuaf group by Convert(varchar,feeddate,106)
)
select x.feedate, x.countOfEnquiries
from cte x
where 0 = (select count(*)
                  from cte y
                  where y.countOfEnquiries > x.countOfEnquiries)


In case you want to find second highest, your N becomes 2, 3rd highest your N becomes 3 and so on.
0
 
SharathConnect With a Mentor Data EngineerCommented:
I think aceprkins has wrong field in GROUP BY clause.  And also the ORDER BY is missing.
SELECT TOP 1 course, COUNT(*) EnquiriesByCourse
FROM cuaf.dbo.mstcuaf m
	INNER JOIN (
			SELECT	TOP 1
					feeddate,
					COUNT(*) EnquiriesByDate
			FROM    cuaf.dbo.mstcuaf
			WHERE   sname NOT LIKE 'Test%'
					AND course <> ''
			GROUP BY feeddate
			ORDER BY EnquiriesByDate DESC) x ON m.feeddate = x.feeddate
GROUP BY
		course
ORDER BY EnquiriesByCourse DESC

Open in new window

0
 
searchsanjaysharmaAuthor Commented:
tx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.