Solved

Group By and Order By

Posted on 2008-06-16
3
187 Views
Last Modified: 2010-04-21
I am fairly new to SQL and seem to be stuck... I would greatly appreciate any help.  I am trying to write an SQL statement that will tell me what text appears most often in my data.  For example, if I have

Ice Cream
Cookie
Cake
Ice Cream
Muffin

I would like to get 'Ice Cream'

Here's what I have come up with...

"SELECT CC as Complaint, COUNT(CC) AS Total from Data WHERE XYZ GROUP BY CC", which returns

Cake             1
Cookie          1
Ice Cream     2
Muffin           1

I was thinking if I could at least order the 'Total' column I could just always grab the top result, but I cannot seem to get the 'Order By' function to work with the field.  Any idea how to make this work or another way to get the result I am looking for?  Thanks!
0
Comment
Question by:jmoran18251
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21791880
you where close:
SELECT CC as Complaint, COUNT(*) AS Total from Data WHERE XYZ GROUP BY CC ORDER BY COUNT(*) DESC "

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21791882
with mysql, to get only the "top winner":

SELECT CC as Complaint, COUNT(*) AS Total from Data WHERE XYZ GROUP BY CC ORDER BY COUNT(*) DESC LIMIT 1 "

Open in new window

0
 

Author Closing Comment

by:jmoran18251
ID: 31467522
I figured I had to be in the ballpark, but I just couldn't quite get there.  Thank you!  That was killing me!
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Import Data from Multiple Text Files in Excel 12 63
Create Consolidation Sheet 3 38
AJAX pass along a variable 3 47
Select question from MySQL 1 13
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

820 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