CODEWITHSUNDEEP
sqlt-sqlselect
Petrik
Petrik

Reputation: 827

SQL RANK with multiple WHERE clause

I have got few sales offices, together with their sales. I am trying to set-up report that will basically tell how is each office performing. Getting some SUMs, COUNTs are quite easy, however I am struggling with getting rank of single office.

I would like to have this query return the rank of single office, during the entire period and/or specified time (eg. BETWEEN '2015-01-01' AND '2015-01-15')

I need to also exclude some offices from the rank list (eg. OfficeName NOT IN ('GGG','QQQ')), so using the sample data, the rank of office 'XYZ' would be 5.

In case that the OfficeName = 'XYZ' is included in WHERE clause, the RANK would be obviously = 1 as SQL filters out other rows, not contained in WHERE clause before executing the rest of the code. Is there any way of doing the same, without using the TemporaryTable ? 

SELECT OfficeName, SUM(Value) as SUM,
RANK() OVER (ORDER BY SUM(VALUE) DESC) AS Rank

FROM Transactions t
JOIN Office o ON t.TransID=o.ID

WHERE OfficeName NOT IN ('GGG','QQQ')
--AND OfficeName = 'XYZ' 

GROUP BY OfficeName

ORDER BY 2 DESC;

I am using MS SQL server 2008.

SQL Fiddle with some random data is here: http://sqlfiddle.com/#!3/fac7a/35

Many thanks for help!

Upvotes: 2

Views: 483

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Here is an amusing way to do this without a subquery:

SELECT TOP 1 OfficeName, SUM(Value) as SUM,
       RANK() OVER (ORDER BY SUM(VALUE) DESC) AS Rank
FROM Transactions t JOIN
     Office o
     ON t.TransID = o.ID
WHERE OfficeName NOT IN ('GGG','QQQ')
GROUP BY OfficeName
ORDER BY (CASE WHEN OfficeName = 'XYZ' THEN 1 ELSE 2 END);

Upvotes: 0

ughai
ughai

Reputation: 9880

You just need to wrap your code as derived table or use a CTE like this and then do the filter for OfficeName = 'XYZ'.

;WITH CTE AS 
(
SELECT OfficeName, SUM(Value) as SUM,
RANK() OVER (ORDER BY SUM(VALUE) DESC) AS Rank
FROM Transactions t
JOIN Office o ON t.TransID=o.ID
WHERE OfficeName NOT IN ('GGG','QQQ')
GROUP BY OfficeName
)
SELECT * 
FROM CTE
WHERE OfficeName = 'XYZ';

Upvotes: 0

A ツ
A ツ

Reputation: 1267

if i understand you correctly you want to do:

SELECT * 
FROM (
  SELECT OfficeName, SUM(Value) as SUM,
    RANK() OVER (ORDER BY SUM(VALUE) DESC) AS Rank    
  FROM Transactions t
  JOIN Office o ON t.TransID=o.ID
  WHERE OfficeName NOT IN ('GGG','QQQ')
  GROUP BY OfficeName
) dat
WHERE OfficeName = 'XYZ';

Upvotes: 3

Related Questions