I’m trying to do something that I’m almost positive works in Postgresql: In other words, I want to grab the “something” row to be used for a bunch of new rows in “somwhere”. Each new entry should have a reference (like foreign key value) back to the same “something…
Tag: common-table-expression
Postgres giving relation does not exist error for alias
Above query is working fine in MS Sql but showing this error when running it in postgres Answer In MS Sql Server it’s possible to update the CTE. In Postgresql you can link to a CTE for an update. For example: However, such update assumes that the id in that table is unique. But, to mark the newest dupl…
Can’t JOIN on CTE column inside CTE, unknown column
I am trying to achieve the following… I get the following error: Why can’t I reference the relations table? Answer Based on your CTE, this is just a guess, but I think you probably want: We don’t know your table structures, so I can’t say I have the right columns for that join, but I&#…
How to select data with percentages from Clickhouse?
Given the following table: I’m trying to fetch all statuses by host, with their relative percentage. In order to do that, I need to count results grouped by host and status, and divide each counted field by its total host fields count. For example, this query would have worked in MySQL: But ClickHouse t…
Trying to delete duplicate rows in SQL Server where the difference is the date or batch number
I have this query: Which is returning the following results ID_NUMBER INCEPTION_DATE OCCURRENCE TRANSACTION_DATE FILE_LOAD_DATE BATCH_NUM 112897732 2008-09-15 4 2008-07-03 2008-07-07 17:57:19 06341 112897732 2008-09-15 4 2008-07-13 2008-07-18 03:35:55 06753 828194721 2008-11-11 1 2008-09-06 2008-09-17 02:50:4…
Selecting values from a nested column based on a condition applied to another nested column in BigQuery
How can I use the index of a “special” value from a nested column (ex: the index of the max value in that nested column) to select a value from another nested column using that index? As an example, consider a table with the following schema: Field name Type Mode id STRING NULLABLE username STRING…
How to retain the first record while using Window function LAG()?
I have a data which looks like – Actual Table – VIN Mode Status Start End ABC123456789 Mode 1 Waiting for Auth 01/01/2010 00:00:00 05/05/2014 14:54:54 ABC123456789 Mode 1 Waiting for URL 05/05/2014 14:54:54 05/13/2014 19:09:51 ABC123456789 Mode 1 Waiting for User 05/13/2014 19:09:51 11/13/2014 22:…
Cycle detected while executing recursive query
I am using CTE recursive query to get below output but don’t know why it is throwing “cycle detected while executing recursive WITH query”. Can anyone please tell me where is wrong with my query? my query: SELECT order_id, product_id, quantity FROM cte; table/data script: Answer In the recur…
cte FULL OUTER JOIN MYSQL
I have this table(called trial): ‘year1’ refers to values in Column ‘val1’, ‘year2’ to values in Column ‘val2’. I want to get totals of all values grouped by year. So I would like to see a result like: I set up a common table expression(cte) using a self join to…
Update of many rows with join extremely slow
I have a table with five relevant fields – id, source, iid, track_hash, alias. I want to group all entries into groups with a common track_hash and then for each row save the id of the row with the lowest source (with ties broken in favor of the highest iid) entry from its group into the alias field. To…