I am trying to fetch some results from the database using two SQL select queries, I am using CTE, but since I have millions of rows I need to limit the results, I want to limit results to 20, what I tried is: Aggregate functions don’t seem to work on limit, moreover, even if they do, I would have a
Tag: common-table-expression
SQL – WITH RECURSIVE doesn’t work, when there is another query before
I have a (postgresql) query, that goes like This works perfectly fine. But when I reorder the with queries to that it suddenly shows a syntax error. This behaviour doesn’t occur, when I have standard non-recursive queries. With non-recursive queries, I can order them, as they please me. Why does it do that? Answer recursive refers to the entire with
Cohort Analysis using SQL (Snowflake)
I am doing a cohort analysis using the table TRANSACTIONS. Below is the table schema, Below is a quick query to see how USER_ID 12345 (an example) goes through the different cohorts based on the date filter provided, The result for this query with the time frame (two weeks) would be and this USER_ID would be classified as a Regular
Cannot use a CTE in a scalar subquery in Db2
I’m trying to use a CTE in a DB2 LUW v11.5.4.0 scalar subquery, but this doesn’t work: I’m getting this error: SQL Error [42601]: An unexpected token “AS” was found following “1 IN ( WITH t (x)”. Expected tokens may include: “JOIN”.. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.26.14 Can this be done? Is there a workaround? (This is similar but not the same
SQLite: How to avoid using two CTE when using CASE statement?
I have a table trx with following schema: where id is the transaction id and p_id the id of the person doing it. I need to query trx so I get a table that allows me to plot an histogram of frequencies of transactions, that means, I want to know how many p_id did only 1 transaction, how many did
How can I write a SQL query to calculate the quantity of components sold with their parent assemblies? (Postgres 11/recursive CTE?)
My goal To calculate the sum of components sold as part of their parent assemblies. I’m sure this must be a common use case, but I haven’t yet found documentation that leads to the result I’m looking for. Background I’m running Postgres 11 on CentOS 7. I have some tables like as follows: And a view like so, which is
running sum or cumulative frequency using subquery
Below is the output of cumulative frequency according to datewise, which I want to change into the subquery. The output is also correct but I want to do it by using a subquery. OUTPUT Answer You would use window functions: Not only is this simpler to write, but it should be significantly faster — and no CTE or subquery is
Oracle DB sql group the table_names patterns into a group
I am trying to classify certain patterns of tables in individual groups and display the results this is fine i included a case statements and classified it but how to group the counts , let provide the sql logic and existing results and expected results so that it will be understood. database is Oracle 11g SQL Query EXISTING RESULTS EXPECTED
SQL: Updating 2 different columns in table from CTE condition
Hi I have a temp table with 2 different columns that I am trying to update from a CTE that has a boolean column ‘uninstalled’. As in: I then want to update the table ‘temp_customers’ above from the CTE and their respective columns (where uninstalled = true shall update column ‘app_uninstalled’ with the correct volume of accounts and where uninstalled
How to write the sql in SQLite with this?
WITH cteCountDownlines AS ( –=== Count each occurrence of EmployeeID in the sort path SELECT EmployeeID = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT), NodeCount = COUNT(*) –Includes current …