Issue Joining SQL table with statement containing Row Number Aggregate Function

Question

Using SQL in excel, I have created a statement to pull and join records using the following post as a guide SQL 2008 Combining data from multiple rows with the same ID into one row (First Answer) I am not able to figure out how to join an additional table to it now. I think I am just adding it to the wrong area.

Here is the working code:

sql = sql & "select REPID, ReportDate, "
sql = sql & "max(case when CategoryID = 5001 then Score end) InPercent, "
sql = sql & "max(case when CategoryID = 5002 then Score end) OutPercent "
sql = sql & "from "
sql = sql & "(select REPID, ReportDate, Score, CategoryID, row_number() over(partition by REPID, ReportDate order by Score) rn from REPREPORTING RR Where RepID <> 0) "
sql = sql & " d group by REPID, ReportDate "

And here is the join statement I need to add:

sql = sql & "JOIN EMPCUR_EmployeeCurr P ON P.employee_token_nr = RR.REPID "

I normally would put this at the end of the statement, and have tried that and get the error Incorrect syntax near the word join. I have also placed it prior to the group by line with the same error.

Any assistance is appreciated!

RR refers to a table inside the subquery. If you want to join outside of the subquery, refer to `d.REPID` instead (subquery is `d`) — JohnHC, Jan 18 '17 at 14:48

score 1 · Answer 1 · answered Jan 18 '17 at 14:54

You cant put the JOIN at the end.

Right now you have:

SELECT <fields>
FROM <subquery>
GROUP BY <fields>

You either want join with the subquery

SELECT <fields>
FROM <subquery> s
JOIN <anotherTable> t ON  s.joinCondition = t.joinCondition
GROUP BY <fields>

Or JOIN inside the subquery

SELECT <fields>
FROM <subquery JOIN here> s
GROUP BY <fields>

score 1 · Accepted Answer · answered Jan 19 '17 at 02:26

I think this is what you want:

with d as (
  select RR.REPID, RR.ReportDate, RR.Score, RR.CategoryID,
  row_number() over(partition by RR.REPID, RR.ReportDate order by RR.Score) rn
  from REPREPORTING RR
  JOIN EMPCUR_EmployeeCurr P ON P.employee_token_nr = RR.REPID  -- right here
  Where RR.RepID <> 0
)
select
  d.REPID, d.ReportDate,
  max(case when d.CategoryID = 5001 then d.Score end) InPercent,
  max(case when d.CategoryID = 5002 then d.Score end) OutPercent
from
  d
group by
  d.REPID, d.ReportDate

I moved your subquery d into a CTE for readability, so don't read too much into that.

You could move the join to the outer query (d), but it's more efficient to eliminate rows before invoking grouping function -- the fewer rows you need to group, the better your query will run.

That said, once I did this I couldn't help but notice that your rn field, which invokes a windowing function, doesn't appear to have any impact. Did you mean to use this, perhaps to take only the first row:

with d as (
  select RR.REPID, RR.ReportDate, RR.Score, RR.CategoryID,
  row_number() over(partition by RR.REPID, RR.ReportDate order by RR.Score) rn
  from REPREPORTING RR
  JOIN EMPCUR_EmployeeCurr P ON P.employee_token_nr = RR.REPID
  Where RR.RepID <> 0
)
select
  d.REPID, d.ReportDate,
  max(case when d.CategoryID = 5001 then d.Score end) InPercent,
  max(case when d.CategoryID = 5002 then d.Score end) OutPercent
from
  d
where
  d.rn = 1    --  Did you mean to add this?
group by
  d.REPID, d.ReportDate

If not, I think you can completely avoid the CTE/subquery altogether and eliminate the windowing function (row_number), which while one of my favorite functions, does have some cost associated with it:

select
  RR.REPID, RR.ReportDate,
  max(case when RR.CategoryID = 5001 then RR.Score end) InPercent,
  max(case when RR.CategoryID = 5002 then RR.Score end) OutPercent
from
  from REPREPORTING RR
  JOIN EMPCUR_EmployeeCurr P ON P.employee_token_nr = RR.REPID
group by
  RR.REPID, RR.ReportDate

And, of course, the VBA version:

sql = _
  "select " & _
  "  RR.REPID, RR.ReportDate, " & _
  "  max(case when RR.CategoryID = 5001 then RR.Score end) InPercent, " & _
  "  max(case when RR.CategoryID = 5002 then RR.Score end) OutPercent " & _
  "from " & _
  "  from REPREPORTING RR " & _
  "  JOIN EMPCUR_EmployeeCurr P ON P.employee_token_nr = RR.REPID " & _
  "group by " & _
  "  RR.REPID, RR.ReportDate"

Issue Joining SQL table with statement containing Row Number Aggregate Function

2 Answers2