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
Dynamic Column in Select Statement Postgres
Self-Referencing Constraint in Ms SQL
How to Substitute a String If Record Is Null in T-Sql
How to Count the Number of Words in a String in Oracle
Insert Deleted Values into a Table Before Delete with a Delete Trigger
Access Query Counter Per Group
Identifying Transitive Dependencies
From Keyword Not Found Where Expected (Oracle SQL)
Strip Non-Numeric Characters from a String
Converting Delimited String to Multiple Values in MySQL
Summarize the List into a Comma-Separated String
SQL Server: How to Group Multiple Row Values into Separate Columns
SQL Server:Return Column Names Based on a Record's Value
Different Ways to Alias a Column
Why Doesn't Oracle Raise "Ora-00918: Column Ambiguously Defined" for This Query
SQL Server:Pivot with Custom Column Names
SQL Server 2008 - Conditional Query
How to Change the Formatting for My Return Values in This Function