CODEWITHSUNDEEP
sql
Sick Series
Sick Series

Reputation: 183

SQL code to find the top 6

Write SQL code to find the top 6 Client Country for each company.

image

How to approach this query? So far I am thinking:-

select top 6 Client_Country, count(*) Total
from table group by Client_Country
order by total desc

Upvotes: 2

Views: 156

Answers (6)

Sick Series
Sick Series

Reputation: 183

SELECT TOP 6 Company, Client_Country, Revenue FROM table where Revenue = (select max(Revenue) from Revenue i where i.Client_Country= table.Client_Country) order by Revenue desc;

Am I on right track?

Upvotes: 0

vhadalgi
vhadalgi

Reputation: 7189

try this !

;with cte as
(
select *,rn=dense_rank()over(partition by company order by Revenue desc) from table
)

select top 6 with ties * from cte

with ties lets you select in case of ties

Upvotes: 0

Jeff Rosenberg
Jeff Rosenberg

Reputation: 3572

To get the top for each country, you'll need to use a windowing function. Otherwise, you'll just get the top 6 overall.

I'm not sure what RDBMS you're using, but in SQL Server you can do something like this:

;WITH top_cte AS
(
  SELECT *, ROW_NUMBER() OVER(PARTITION BY Company ORDER BY Revenue DESC) AS [Rank]
  FROM table
)
SELECT *
FROM top_cte
WHERE [Rank] <= 6

EDIT: Edited to use ROW_NUMBER instead of RANK. The one issue with RANK is that if you had, say, 3 companies tied for 5th place, you'd get 8 results back instead of 6.

Upvotes: 2

naffy
naffy

Reputation: 111

i suggest you use a rank() function to get this working, then CTE to restrict it to the top 6 , something like:

SELECT * FROM (
    SELECT company,client_company, RANK() OVER (PARTITION BY company ORDER BY REVENUE DESC) as top6
    FROM table
) k
WHERE k.top6<7

Upvotes: 0

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

SELECT * FROM
(
  SELECT *,Row_Number() Over(Partition By Company Order By (Select Null)) RN FROM TAble1
) AS T
Where RN < 7

Upvotes: 0

Goon10
Goon10

Reputation: 150

SELECT TOP 6 CLIENT_COUNTRY, COMPANY 
FROM TABLE
GROUP BY COMPANY
ORDER BY REVENUE DESC

It depends on your RDBMS, use :

TOP :SQL server, MS Access LIMIT: MySQL ROWNUM : Oracle

Upvotes: -1

Related Questions