Return Top 10 based on a particular column

Question

Hello, in the query below, I would like to only return the Top 10 rows based on the column 'local'. How do I do that?

SELECT     ie.Market AS Marknad,
ie.[Sales Area_Buyer] AS Distrikt,
ie.Statisticscustomer_Supplierno_ AS KundNr,
cu.Name,
SUM(ie.[Src_ Curr_ Amount]) * - 1 AS 'local'
FROM         [Table1] AS ie
INNER JOIN  [Table2] AS cu ON cu.Market = ie.Market AND cu.No_ = ie.Statisticscustomer_Supplierno_
WHERE     (ie.Market = @Marknad) AND (ie.[Sales Area_Buyer] = @Distrikt)
GROUP BY ie.Market, ie.[Sales Area_Buyer], ie.Statisticscustomer_Supplierno_, cu.Name
ORDER BY 'local' DESC

Answer 1

Hello Spehe,

  Try the below

SELECT *
  FROM
	(
		SELECT     ie.Market AS Marknad, 
		ie.[Sales Area_Buyer] AS Distrikt, 
		ie.Statisticscustomer_Supplierno_ AS KundNr, 
		cu.Name, 
		SUM(ie.[Src_ Curr_ Amount]) * - 1 AS 'local',
		RANK() OVER(ORDER BY SUM(ie.[Src_ Curr_ Amount]) * - 1) As Rnk
		FROM         [Table1] AS ie 
		INNER JOIN  [Table2] AS cu ON cu.Market = ie.Market AND cu.No_ = ie.Statisticscustomer_Supplierno_
		WHERE     (ie.Market = @Marknad) AND (ie.[Sales Area_Buyer] = @Distrikt)
		GROUP BY ie.Market, ie.[Sales Area_Buyer], ie.Statisticscustomer_Supplierno_, cu.Name
	) X
 WHERE x.Rnk <=10

---

Best Regards Sorna

Answer 2

Hello Spehe,

this would also be possible

Select Top 10 *
From(
SELECT     ie.Market AS Marknad, 
ie.[Sales Area_Buyer] AS Distrikt, 
ie.Statisticscustomer_Supplierno_ AS KundNr, 
cu.Name, 
SUM(ie.[Src_ Curr_ Amount]) * - 1 AS 'local'
FROM         [Table1] AS ie 
INNER JOIN  [Table2] AS cu ON cu.Market = ie.Market AND cu.No_ = ie.Statisticscustomer_Supplierno_
WHERE     (ie.Market = @Marknad) AND (ie.[Sales Area_Buyer] = @Distrikt)
GROUP BY ie.Market, ie.[Sales Area_Buyer], ie.Statisticscustomer_Supplierno_, cu.Name
ORDER BY 'local' DESC)

BR,

geocon

Answer 3

Hello Sorna,

thank you for taking an interest in this. When I try your suggestion, I can get it to work, but there are values missing that should be included in the result. I want the query to return the Top 10 values for the column 'local', but it looks as if it only returns the 10 first records.

Answer 4

Hello geocon,

thank you for the help. Though your suggestion seems promising, I get a syntax error at the last bracket when I try to run the query.

Answer 5

Hello Spehe,

   Try the below code

SELECT *
  FROM
	(
		SELECT     ie.Market AS Marknad, 
		ie.[Sales Area_Buyer] AS Distrikt, 
		ie.Statisticscustomer_Supplierno_ AS KundNr, 
		cu.Name, 
		SUM(ie.[Src_ Curr_ Amount]) * - 1 AS 'local',
		DENSE_RANK() OVER(ORDER BY SUM(ie.[Src_ Curr_ Amount]) * - 1) As Rnk
		FROM         [Table1] AS ie 
		INNER JOIN  [Table2] AS cu ON cu.Market = ie.Market AND cu.No_ = ie.Statisticscustomer_Supplierno_
		WHERE     (ie.Market = @Marknad) AND (ie.[Sales Area_Buyer] = @Distrikt)
		GROUP BY ie.Market, ie.[Sales Area_Buyer], ie.Statisticscustomer_Supplierno_, cu.Name
	) X
 WHERE x.Rnk <=10
--------------------------------------------------------------------------------

---

Best Regards Sorna

Answer 6

Select Top 10 *
From(
SELECT     ie.Market AS Marknad, 
ie.[Sales Area_Buyer] AS Distrikt, 
ie.Statisticscustomer_Supplierno_ AS KundNr, 
cu.Name, 
SUM(ie.[Src_ Curr_ Amount]) * - 1 AS 'local'
FROM         [Table1] AS ie 
INNER JOIN  [Table2] AS cu ON cu.Market = ie.Market AND cu.No_ = ie.Statisticscustomer_Supplierno_
WHERE     (ie.Market = @Marknad) AND (ie.[Sales Area_Buyer] = @Distrikt)
GROUP BY ie.Market, ie.[Sales Area_Buyer], ie.Statisticscustomer_Supplierno_, cu.Name
ORDER BY 'local' DESC)x
use above,,,

Answer 7

Hello Sorna,

this query also works, but seem to return the lowest 10 records for the column 'local'

Answer 8

Hello skc_chat

your query returns the error message 'The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.'

Answer 9

Hello Spehe,

the error seems to occur due some copy/paste mistake... sry.

But alter your initial query - which works - by adding the "Top 10" part to your Select statement that it looks like this:

"Select Top 10 ie.Market AS ..."

The thing is that "Select" should be evaluated in the last step of processing, so that the ordering will effect the result set prior the "Select" and does the required ordering - if I'm not totally wrong...

BR,

geocon

Answer 10

oops..  i overlooked and did it in hurry.
try this way

SELECT     ie.Market AS Marknad,
ie.[Sales Area_Buyer] AS Distrikt,
ie.Statisticscustomer_Supplierno_ AS KundNr,
cu.Name,
SUM(ie.[Src_ Curr_ Amount]) * - 1 AS 'local'
INTO #T
FROM         [Table1] AS ie
INNER JOIN  [Table2] AS cu ON cu.Market = ie.Market AND cu.No_ = ie.Statisticscustomer_Supplierno_
WHERE     (ie.Market = @Marknad) AND (ie.[Sales Area_Buyer] = @Distrikt)
GROUP BY ie.Market, ie.[Sales Area_Buyer], ie.Statisticscustomer_Supplierno_, cu.Name
ORDER BY 'local' DESC

select TOP 10 * FROM #T

Answer 11

Try this,

SELECT TOP 10 ie.Market                         AS Marknad,
              ie.[Sales Area_Buyer]             AS Distrikt,
              ie.Statisticscustomer_Supplierno_ AS KundNr,
              cu.Name,
              Sum(ie.[Src_ Curr_ Amount]) * -1  AS 'local'
FROM   [Table1] AS ie
       INNER JOIN [Table2] AS cu
               ON cu.Market = ie.Market
                  AND cu.No_ = ie.Statisticscustomer_Supplierno_
WHERE  ( ie.Market = @Marknad )
       AND ( ie.[Sales Area_Buyer] = @Distrikt )
GROUP  BY ie.Market,
          ie.[Sales Area_Buyer],
          ie.Statisticscustomer_Supplierno_,
          cu.Name
ORDER  BY 'local' DESC 

---

Regards, Dineshkumar
Please "Mark as Answer" if my post answers your question or "Vote as Helpful" if it helps you

Answer 12

This query worked the way I wanted it to. Thank you!

Answer 13

Welcome SPEHE

---

Regards, Dineshkumar
Please "Mark as Answer" if my post answers your question or "Vote as Helpful" if it helps you

Answer 14

Hello Spehe,

  The order of the ranking needs to be changed as below. One more point is , if you are using a Top 10 and if two local values are same still it would return the top 10 rows only not the all the rows with Top 10 local values. Please ensure is this is what you are looking for.

SELECT *
  FROM
	(
		SELECT     ie.Market AS Marknad, 
		ie.[Sales Area_Buyer] AS Distrikt, 
		ie.Statisticscustomer_Supplierno_ AS KundNr, 
		cu.Name, 
		SUM(ie.[Src_ Curr_ Amount]) * - 1 AS 'local',
		DENSE_RANK() OVER(ORDER BY SUM(ie.[Src_ Curr_ Amount]) * - 1 DESC) As Rnk
		FROM         [Table1] AS ie 
		INNER JOIN  [Table2] AS cu ON cu.Market = ie.Market AND cu.No_ = ie.Statisticscustomer_Supplierno_
		WHERE     (ie.Market = @Marknad) AND (ie.[Sales Area_Buyer] = @Distrikt)
		GROUP BY ie.Market, ie.[Sales Area_Buyer], ie.Statisticscustomer_Supplierno_, cu.Name
	) X
 WHERE x.Rnk <=10

---

Best Regards Sorna

Answer 15

Hello Sorna,

thank you for the information. I have learned a lot, and will look into your suggestions.

Regards,

//Peter