Cte in from Clause of SQL Query

MySQL CTE not referenced in WHERE clause

You may refer to cte or another table by the name (not using a subselect from it) using TABLE keyword.

WITH
cte AS (SELECT '1332')
SELECT * FROM stock_prices
WHERE Country_Exchange_Code = 'T' AND Stock_Code IN (TABLE cte);

fiddle

TSQL-ORDER BY clause in a CTE expression?

You can't use "Order By" in a CTE but you can move the order by to the select statement calling the CTE and have the affect I believe you are looking for

;with y as(
select txn_Date_Time,txn_time,card_No,batch_No,terminal_ID
from C1_Transaction_Information
where txn_Date_Time='2017-10-31'

)

select * from y order by card_No;

Custom Sort Order in CTE

You need to sort the outer query.

Sorting a subquery is not allowed because it is meaningless, consider this simple example:

WITH CTE AS
( SELECT ID
FROM (VALUES (1), (2)) AS t (ID)
ORDER BY ID DESC
)
SELECT *
FROM CTE
ORDER BY ID ASC;

The ordering on the outer query has overridden the ordering on the inner query rendering it a waste of time.

It is not just about explicit sorting of the outer query either, in more complex scenarios SQL Server may sort the subqueries any which way it wishes to enable merge joins or grouping etc. So the only way to guarantee the order or a result is to order the outer query as you wish.

Since you may not have all the data you need in the outer query, you may would probably need to create a further column inside the CTE to use for sorting. e.g.

WITH ctedivisiondesc AS
(
SELECT *
FROM ( SELECT DH1.ID_NUM,
DH1.DIV_CDE,
DDF.DEGREE_DESC AS DivisionDesc,
DTE_DEGR_CONFERRED,
ROW_NUMBER() OVER (PARTITION BY ID_NUM ORDER BY DTE_DEGR_CONFERRED DESC) AS [ROW NUMBER],
CASE
WHEN DDF.DEGREE_DESC = 'Certificate' THEN 1
WHEN DDF.DEGREE_DESC = 'Undegraduate' THEN 2
WHEN DDF.DEGREE_DESC = 'Graduate' THEN 3
WHEN DDF.DEGREE_DESC = 'Doctor of Chiropractic' THEN 4
ELSE 5
END AS SortOrder
FROM TmsePrd.dbo.DEGREE_HISTORY AS DH1
INNER JOIN TmsePrd.dbo.DEGREE_DEFINITION AS DDF
ON DH1.DEGR_CDE = DDF.DEGREE

) AS t
WHERE t.[ROW NUMBER] <= 1
)
SELECT ID_NUM,
DIV_CDE,
DivisionDesc,
DTE_DEGR_CONFERRED
FROM ctedivisiondesc
ORDER BY SortOrder;

Aggregate column from CTE cannot be used in WHERE clause in the query in PostgreSQL

The t2.count is being interpreted as an aggregate COUNT() function, and your t2 table does not have a column called count.

Make sure that your table does actually have a count column, or make sure to compute it's aggregate count on another CTE before joining, and then comparing the the results. Also avoid using the alias "count", like the following:

WITH CTE AS (
SELECT t1.x, COUNT(t1.y) AS total
FROM table1 t1
GROUP BY t1.x
),
CTE2 AS (
SELECT t2.x, COUNT(t2.y) AS total
FROM table2 t2
GROUP BY t2.x
)
SELECT
CTE1.x,
CTE1.total AS newCount,
CTE2.total AS oldCount
FROM
CTE2
JOIN CTE1 ON CTE2.x = CTE1.x
WHERE
CTE2.total != CTE1.total;


Related Topics



Leave a reply



Submit