Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to write the group by query to find highest.

Posted on 2013-05-18
7
Medium Priority
?
274 Views
Last Modified: 2013-06-07
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
Comment
Question by:searchsanjaysharma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 252 total points
ID: 39177529
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 498 total points
ID: 39177876
So is your table called mstenquiries or cuaf.dbo.mstcuaf ?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 498 total points
ID: 39177882
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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 12

Assisted Solution

by:sachitjain
sachitjain earned 249 total points
ID: 39178095
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
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 501 total points
ID: 39178297
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
 
LVL 41

Accepted Solution

by:
Sharath earned 501 total points
ID: 39178639
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
 

Author Closing Comment

by:searchsanjaysharma
ID: 39229036
tx
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question