Database
Highlighted
Visitor

## Help selecting top 10 values starting from highest by location

Good afternoon,

I have a data set from 25 locations with daily quantity and dollar sales for a year. I am trying to pull the top 10 sales dates based on dollar sales sorted by high to low by location.

```SELECT TOP 10 A.LOCATION_ID, A.SALES_DATE,A.SALES_DOLLARS
FROM ( LOCATION_ID AS LOCATION_ID, SALES_DATE AS SALES_DATE, SUM(QUANTITY_SOLD) AS TOTAL_QTY, SUM(DOLLARS_SOLD) AS SALES_DOLLARS
WHERE SALES_DATE BETWEEN '1-1-2017' AND '12-16-2017'
GROUP BY 1,2 ) AS A```

This query only provides top 10 locations based on dollars. I tried using some of the examples in this forum and could find one that matched what I need.

Any help will be greatly appreciated!!!

Thanks.

2 REPLIES 2

## Re: Help selecting top 10 values starting from highest by location

Something like this, I think:

SELECT A.LOCATION_ID
,A.SALES_DATE
,A.TOTAL_QTY
,A.SALES_DOLLARS
FROM

(SELECT LOCATION_ID AS LOCATION_ID
,SALES_DATE AS SALES_DATE
,SUM(QUANTITY_SOLD) AS TOTAL_QTY
,SUM(DOLLARS_SOLD) AS SALES_DOLLARS
WHERE SALES_DATE BETWEEN '1-1-2017'
AND  '12-16-2017'
GROUP BY 1 ,2 ) A
QUALIFY Rank() Over(Partition by Location_ID Order by SALES_DOLLARS Desc) <= 10

Junior Contributor

## Re: Help selecting top 10 values starting from highest by location

As OLAP functios are calculated after aggregates this can be further simplified:

```SELECT LOCATION_ID AS LOCATION_ID
,SALES_DATE AS SALES_DATE
,SUM(QUANTITY_SOLD) AS TOTAL_QTY
,SUM(DOLLARS_SOLD) AS SALES_DOLLARS
WHERE SALES_DATE BETWEEN '1-1-2017'
AND  '12-16-2017'
GROUP BY 1 ,2
QUALIFY Rank()  -- maybe ROW_NUMBER instead (TOP/ROW_NUMBER vs TOP WITH TIES/RANK)
Over(Partition by Location_ID
Order by SALES_DOLLARS Desc) <= 10```