Improve company productivity with a Business Account.Sign Up

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

Order and Group the same query

I'm trying to create a query which provides me with a list of the top 20 suppliers of our company.  This information is stored in an MSSQL database.

I can count the number of orders against each supplier and list them by using group.  I would then like to order this descending, so I see the top 20 suppliers and limit it so the others are disguarded.

As soon as I add the order function I get an error.

Can anyone tell me where I'm going wrong?

Thanks
SELECT     CardCode, COUNT(*) AS POs
FROM         OPOR
WHERE     (DocDate >= '06/01/2009') AND (DocDate <= '06/30/2009')
ORDER BY POs DESC
GROUP BY CardCode

Open in new window

0
Richie4236
Asked:
Richie4236
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT     CardCode, COUNT(*) AS POs
FROM         OPOR
WHERE     (DocDate >= '06/01/2009') AND (DocDate <= '06/30/2009')
GROUP BY CardCode
ORDER BY POs DESC
0
 
pssandhuCommented:
SELECT CardCode, COUNT(*) AS POs
FROM OPOR
WHERE (DocDate >= '06/01/2009') AND (DocDate <= '06/30/2009')
GROUP BY CardCode
ORDER BY COUNT(*) DESC
0
 
j_s_kelleyCommented:
Orber by goes after the Group By.  In fact, Order By really should be the last thing in a query.  It can't exist in a subquery or a group by operation.  The only way you can use order by in a sub query is if you are using the TOP modifier.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
awking00Commented:
See attached.
query.txt
0
 
BrightApolloCommented:
Here's a portable ANSI solution.

SELECT o1.CardCode, COUNT(o1.*) AS POs
FROM OPOR o1
WHERE (SELECT COUNT(o2.*) as POs FROM OPOR o2
              WHERE o1.POs > o2.POs
               AND o2.DocDate >= '06/01/2009' AND o2.DocDate <= '06/30/2009') < 21
WHERE o1.DocDate >= '06/01/2009' AND o1.DocDate <= '06/30/2009'
ORDER BY POs DESC

At this point if you wanted to group the results you can use the above as a table query.

--#
0
 
BrightApolloCommented:
This is weird, the accepted answer doesn't limit to 20 rows, it just rewrites the SQL syntax to execute.
0
 
Richie4236Author Commented:
The question / post was mainly about grouping and ordering, as the title suggests.  The answer I accepted fixed my problem.
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.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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