SQL Server Order by before union error: Incorrect syntax near keyword union

PRA
 
on Nov 13, 2020 04:09 AM
999 Views

Hi!

I used below script, but not worked order by before union.

Incorrect syntax near keyword "union"

WITH CTE AS ( 
SELECT ROW_NUMBER() OVER (PARTITION BY (st.IdStd) ORDER BY Years DESC) Row_No, st.IdStd, ltrim(rtrim(st.LastName))LastName, st.IdSpec, st.TypeStud, st.StudType, st.Finance, st.Year, st.IdCourse, st.SdtGroup, st.Param, 
case when st.TypeStud = 1 and st.StudType = 0 then a.Money when st.TypeStud = 1 and st.StudType = 1 then a.MNine when st.TypeStud = 1 and st.StudType = 2 then a.Master when st.TypeStud = 1 and st.StudType = 3 then a.Phd when st.TypeStud = 3 and st.StudType = 0 then a.Corresp when st.TypeStud = 2 and st.StudType = 3 then a.Corresp when st.TypeStud = 2 and st.StudType = 0 then a.Corresp else 0 end'Money', 
case when st.TypeStud = 1 and st.StudType = 0 then ISNULL(SUM(c.Money),0) when st.TypeStud = 1 and st.StudType = 1 then ISNULL(SUM(c.Money),0) when st.TypeStud = 1 and st.StudType = 2 then ISNULL(SUM(c.Money),0) when st.TypeStud = 1 and st.StudType = 3 then ISNULL(SUM(c.Money),0) when st.TypeStud = 3 and st.StudType = 0 then ISNULL(SUM(c.Money),0) when st.TypeStud = 2 and st.StudType = 3 then ISNULL(SUM(c.Money),0) when st.TypeStud = 2 and st.StudType = 0 then ISNULL(SUM(c.Money),0) else 0 end'Payed', 
case when st.TypeStud = 1 and st.StudType = 0 then ISNULL((a.Money - SUM(c.Money)),0) when st.TypeStud = 1 and st.StudType = 1 then ISNULL((a.MNine - SUM(c.Money)),0) when st.TypeStud = 1 and st.StudType = 2 then ISNULL((a.Master - SUM(c.Money)),0) when st.TypeStud = 1 and st.StudType = 3 then ISNULL((a.Phd - SUM(c.Money)),0) when st.TypeStud = 3 and st.StudType = 0 then ISNULL((a.Corresp - SUM(c.Money)),0) when st.TypeStud = 2 and st.StudType = 3 then ISNULL((a.Corresp - SUM(c.Money)),0) when st.TypeStud = 2 and st.StudType = 0 then ISNULL((a.Corresp - SUM(c.Money)),0) else 0 end'Remain', 
ISNULL(c.Years,'')Years 
FROM cash c right outer join students st on c.IdStd = st.IdStd inner join agrem a on st.Year = a.Year and st.IdSpec = a.Spec where st.StdActive = 1 and c.PayType = 0 group by c.Years, st.Year, st.IdStd, st.LastName, st.IdSpec, st.TypeStud, st.StudType, st.Finance, st.IdCourse, st.SdtGroup, c.PayType, st.Param, a.Money, a.Corresp, a.MNine, a.Master, a.Phd
) 
select r.IdStd, ltrim(rtrim(r.LastName))LastName, r.Money, r.payed, r.remain, r.Years 
from CTE r where r.idspec= '2790133' and idcourse = '1' and sdtgroup = '103' and param = '279013304' and r.Years IN (SELECT DISTINCT MAX(Years) FROM cash GROUP BY IdStd,course) order by r.LastName
union
select r.IdStd, ltrim(rtrim(r.LastName))LastName, case r.Finance when 1 then case r.StudType when 0 then case r.TypeStud when 1 then a.Money when 2 then a.Corresp when 3 then a.Corresp end when 1 then a.MNine when 2 then a.Master  when 3 then a.Phd end else 0 end'Money', NULL, NULL, case r.Finance when 1 then r.Year else '' end Years 
from students r inner join agrem a on a.Spec = r.IdSpec and r.Year = a.Year and r.idspec= '2790133' and idcourse = '1' and sdtgroup = '103' and param = '279013304' where r.IdStd not in (select IdStd from CTE) and r.StdActive = 1 group by r.Year, r.Year, r.IdStd, r.IdCourse, r.IdSpec, r.LastName, r.Param, r.SdtGroup, r.StudType, r.TypeStud, r.Finance, a.Money, a.Corresp, a.MNine, a.Master, a.Phd  order by r.LastName

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
PRA
 
on Nov 24, 2020 12:18 AM

 

	with cte as (
	SELECT ROW_NUMBER() OVER (PARTITION BY (st.IdStd) ORDER BY Years DESC) Row_No, st.IdStd, ltrim(rtrim(st.LastName))LastName, st.IdSpec, st.TypeStud, st.StudType, st.Finance, st.Year, st.IdCourse, st.SdtGroup, st.Param,
	case when st.TypeStud = 1 and st.StudType = 0 then a.Money when st.TypeStud = 1 and st.StudType = 1 then a.MNine when st.TypeStud = 1 and st.StudType = 2 then a.Master when st.TypeStud = 1 and st.StudType = 3 then a.Phd when st.TypeStud = 3 and st.StudType = 0 then a.Corresp when st.TypeStud = 2 and st.StudType = 3 then a.Corresp when st.TypeStud = 2 and st.StudType = 0 then a.Corresp else 0 end'Money',
	case when st.TypeStud = 1 and st.StudType = 0 then ISNULL(SUM(c.Money),0) when st.TypeStud = 1 and st.StudType = 1 then ISNULL(SUM(c.Money),0) when st.TypeStud = 1 and st.StudType = 2 then ISNULL(SUM(c.Money),0) when st.TypeStud = 1 and st.StudType = 3 then ISNULL(SUM(c.Money),0) when st.TypeStud = 3 and st.StudType = 0 then ISNULL(SUM(c.Money),0) when st.TypeStud = 2 and st.StudType = 3 then ISNULL(SUM(c.Money),0) when st.TypeStud = 2 and st.StudType = 0 then ISNULL(SUM(c.Money),0) else 0 end'Payed',
	case when st.TypeStud = 1 and st.StudType = 0 then ISNULL((a.Money - SUM(c.Money)),0) when st.TypeStud = 1 and st.StudType = 1 then ISNULL((a.MNine - SUM(c.Money)),0) when st.TypeStud = 1 and st.StudType = 2 then ISNULL((a.Master - SUM(c.Money)),0) when st.TypeStud = 1 and st.StudType = 3 then ISNULL((a.Phd - SUM(c.Money)),0) when st.TypeStud = 3 and st.StudType = 0 then ISNULL((a.Corresp - SUM(c.Money)),0) when st.TypeStud = 2 and st.StudType = 3 then ISNULL((a.Corresp - SUM(c.Money)),0) when st.TypeStud = 2 and st.StudType = 0 then ISNULL((a.Corresp - SUM(c.Money)),0) else 0 end'Remain',
	ISNULL(c.Years,'')Years
	FROM cash c right outer join students st on c.IdStd = st.IdStd inner join agrem a on st.Year = a.Year and st.IdSpec = a.Spec where st.StdActive = 1 and c.PayType = 0 group by c.Years, st.Year, st.IdStd, st.LastName, st.IdSpec, st.TypeStud, st.StudType, st.Finance, st.IdCourse, st.SdtGroup, c.PayType, st.Param, a.Money, a.Corresp, a.MNine, a.Master, a.Phd
	),
    outercte as
	(
	select e.IdStd, ltrim(rtrim(e.LastName))LastName, e.Money, e.payed, e.remain, e.Years
	from CTE e where e.idspec= '2790133' and idcourse = '1' and sdtgroup = '103' and param = '279013304' and e.Years IN (SELECT DISTINCT MAX(Years) FROM cash GROUP BY IdStd,course)
	union
	select r.IdStd, ltrim(rtrim(r.LastName))LastName, case r.Finance when 1 then case r.StudType when 0 then case r.TypeStud when 1 then a.Money when 2 then a.Corresp when 3 then a.Corresp end when 1 then a.MNine when 2 then a.Master  when 3 then a.Phd end else 0 end'Money', NULL, NULL, case r.Finance when 1 then r.Year else '' end Years
	from students r inner join agrem a on a.Spec = r.IdSpec and r.Year = a.Year and r.idspec= '2790133' and idcourse = '1' and sdtgroup = '103' and param = '279013304' where r.IdStd not in (select IdStd from CTE) and r.StdActive = 1 group by r.Year, r.Year, r.IdStd, r.IdCourse, r.IdSpec, r.LastName, r.Param, r.SdtGroup, r.StudType, r.TypeStud, r.Finance, a.Money, a.Corresp, a.MNine, a.Master, a.Phd
	)
	select t.IdStd, ltrim(rtrim(t.LastName))LastName, t.Money, t.payed, t.remain, t.Years
	from outercte t order by t.LastName