Q: SQL statements? Where to put Order by?

Question

I have here the formula for my current query. However, I encounter an error putting order by at the end of the statement.Incorrect syntax is encountered.

below is my formula. I want to know where to insert order by without any error.

select * from #source;

with cteNumericRef as
(
    select [NAME],[BRAND],[REFERENCE]
    from #source
    where ISNUMERIC([REFERENCE]) = 1
)

, cteCheckRow as
(
)

, ctePairedRow as
(
    select
          num_ref.[NAME]
        , num_ref.[BRAND]
        , num_ref.[REFERENCE]
        , row_number() over (partition by num_ref.[REFERENCE] order by num_ref.[NAME]) as [Pair_Num]

    from cteNumericRef num_ref

    left join cteCheckRow check_row
        on check_row.[REFERENCE] = num_ref.[REFERENCE]

    where check_row.[REFERENCE] is null
)

, cteTextRow as
(
    select [NAME],[BRAND],[REFERENCE],
        case [REFERENCE]
            when 'paired' then 'PAIRED'
            when 'nonpaired' then 'UNIQUE'
            when 'dropped' then 'DROPPED'
            when '' then ''
        else 'CHECK' end as [COMMENT]
    from #source
    where ISNUMERIC([REFERENCE]) <> 1
)

select
    left_row.[NAME]
    , left_row.[BRAND]
    , left_row.[REFERENCE]
    , right_row.[BRAND] as [COMMENTS]
from ctePairedRow left_row
inner join ctePairedRow right_row
    on left_row.[REFERENCE] = right_row.[REFERENCE]
    and left_row.[Pair_Num] <> right_row.[Pair_Num]

union all

select
    num_ref.[NAME]
    , num_ref.[BRAND]
    , num_ref.[REFERENCE]
    , check_row.[COMMENT]
from cteNumericRef num_ref
inner join cteCheckRow check_row
    on check_row.[REFERENCE] = num_ref.[REFERENCE]

union all

select
      [NAME]
    , [BRAND]
    , [REFERENCE]
    , [COMMENT]
from cteTextRow;

Please don't deface your question – Jacob G Oct 20 '16 at 01:44 — Jacob G, Oct 20 '16 at 01:44

Eralper · Accepted Answer · 2016-10-13T07:23:41.157

1

You cannot use ORDER BY in CTEs

But using ORDER BY following the last SELECT after all CTEs and all UNION ALL will fit like

....
union all

select
      [NAME]
    , [BRAND]
    , [REFERENCE]
    , [COMMENT]
from cteTextRow
ORDER BY .....;

here is a sample case

;with cte as (
    select 'Kodyaz' as sozcuk
    union all
    select 'Eralper'
    union all
    select 'Vader'
)
select 'Phantom' as sozcuk
union all
select * from cte
order by sozcuk

and output as

edited Oct 13 '16 at 07:23

answered Oct 13 '16 at 06:42

Eralper

6,461
2
21
27

I tried that, it didn't work. – Chels Oct 13 '16 at 07:14
I added a SQL sample. Did you get error in your case, what is it? Or Order By did not work as you wanted? – Eralper Oct 13 '16 at 07:24
I dunno what im doing wrong – Chels Oct 13 '16 at 09:16
(90726 row(s) affected) Msg 208, Level 16, State 1, Line 106 Invalid object name 'ctecheckrow'. this is the error – Chels Oct 13 '16 at 09:16
First of all, you need to add semicolumn before the first CTE expression. Like " ;with cteNumericRef as " instead of " with cteNumericRef as " – Eralper Oct 13 '16 at 16:05
Thanks eralper! it worked :D – Chels Oct 14 '16 at 01:31

Q: SQL statements? Where to put Order by?

1 Answers1