Adapting With/CTE statement

Question

Using SQL Server 2012.... This code works as a standalone script but I need the value returned within a iif(...) nested in a Case...when as part of a larger script:

with GroupedValues (Value, Frequency, SelectingCriteria) AS
(Select
    Table1.Cost as Value,
    Count(*) as Frequency,
Table2.AccountNumber as SelectingCriteria

from Table1, Table 2 
)

Group by Table1.Cost, Table2.AccountNumber) 

Select
    Max(Value) as Value
From GroupedValues a INNER JOIN
    (select Max(Frequency) AS MaxFrequency
     from GroupedValues) b
on a.Frequency=b.MaxFrequency

Is this your actual code? This doesn't look like functioning code. — Taryn, Mar 14 '13 at 21:13
First get rid of the implict joins and join the 21st century! — HLGEM, Mar 14 '13 at 21:28
Am I understanding you right, you'd like to put the above code inside an IIF which is then part of a CASE statement? — billinkc, Mar 14 '13 at 21:29
If I understand you correclty you need to use the results of teh CTE later in a script? Inthis case you cannot use a CTE, they can only be referred to in the statement immediately following the CTE. A temp table might be what you need or a table varaible. — HLGEM, Mar 14 '13 at 21:30
It is functioning code, but the answer below works great as part of my nested situation. I'm still a newbie so thanks for the help. — Frank Horatio, Mar 15 '13 at 06:46

Pieter Geerkens · Accepted Answer · 2013-03-14T21:32:55.633

0

I believe you are just doing this:

select top 1 
  Value
from (
  select
    Table1.Cost, Table2.AccountNumber
    Count(*) as Frequency,
    Table1.Cost as Value
  from Table1 cross join Table2 
  group by Table1.Cost, Table2.AccountNumber, Table1.Cost 
) T
order by 
  Frequency desc, 
  Value desc

edited Mar 14 '13 at 21:32

answered Mar 14 '13 at 21:14

Pieter Geerkens

11,775
2
32
52

1

Do not perpetuate the use of implict joins which are a SQL antipattern – HLGEM Mar 14 '13 at 21:28
@HLGEM: It is a cross join, not an inner or outer join; the one and only circumstance in which I tolerate that syntax myself. You are preaching to the converted. – Pieter Geerkens Mar 14 '13 at 21:30
3

There is this really nice syntax called CROSS JOIN. It is clearer and easier to maintain. I especially would not put a CROSS JOIN in implicit syntax since maintainers will not know if it was meant as one or was a bug. – HLGEM Mar 14 '13 at 21:31
@FrankHoratio: You are welcome. The best thanks, of course, is a vote. – Pieter Geerkens Mar 15 '13 at 06:53

Adapting With/CTE statement

1 Answers1