[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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/
0
searchsanjaysharma
Asked:
searchsanjaysharma
6 Solutions
 
Surendra NathCommented:
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 PerkinsCommented:
So is your table called mstenquiries or cuaf.dbo.mstcuaf ?
0
 
Anthony PerkinsCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sachitjainCommented:
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
 
SharathData 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
 
SharathData 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
 
searchsanjaysharmaAuthor Commented:
tx
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now