Solved

# How to write the group by query to find highest.

Posted on 2013-05-18
266 Views
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
Question by:searchsanjaysharma

LVL 16

Assisted Solution

Surendra Nath earned 84 total points
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
``````
0

LVL 75

Assisted Solution

Anthony Perkins earned 166 total points
So is your table called mstenquiries or cuaf.dbo.mstcuaf ?
0

LVL 75

Assisted Solution

Anthony Perkins earned 166 total points
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
``````
0

LVL 12

Assisted Solution

sachitjain earned 83 total points
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 40

Assisted Solution

Sharath earned 167 total points
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
``````
0

LVL 40

Accepted Solution

Sharath earned 167 total points
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
``````
0

Author Closing Comment

tx
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!