I’m trying to use three projections in same query like below in a Druid environment: But instead I get an error saying – Unknown exception / Cannot build plan for query It seems to work perfectly fine when I put just one count(distinct) in the query. How can this be resolved? Answer As the Druid d…
Get first and last Order and the highest value Item in each order for each Customer, all of which are separate tables
I need to find the first and last Order for each Customer by OrderDate, and the name and SKU of the item with the highest business volume in each of those orders. For reference, the Customer table has >150k records, and Orders and OrderDetails (these are the Items) a lot more. Note: Both Orders and their r…
How to replace a column result if null with another column result in BIRT
I would really appreciate some help with a computed column using an expression in Birt. I have two columns which are giving results, one gives email addresses and one gives contact numbers: telephone, mobile.. When the email result is null then the contact number column shows a number, these are separate comm…
SSMS – get DDL of all views in a schema
I have a SQL Server database with several schemas, each of them contains several views. I need to create the same views in other databases so I would like to get the DDL of all the views in a script, generated through SQL. In Oracle it was quite easy, accessing ALL_VIEWS and referencing the QUERY column. How …
How to aggregate and group by in each column?
I have this table: I’d like to summarize major and minor columns to get this result: Is there any way to achieve this? I tried: But it didn’t count each number. Answer You may achieve this using window functions. major minor major_count minor_count A a 3 2 A b 3 1 B c 2 1 B d 2 1 View
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:…
Snowflake-SQL – Add row from one query to another query
I’m looking for assistance in combining query results. I have one query that results in: The query: and I have another query that results in: The query: Is there a way to add the TypeACount column to the second query? Answer You can run both queries in a table expressions in a CTE and then join them.
Update and insert from one table with multiple same ID rows but different column attributes to second table
I would like to update the records from one table to another table and if there are new records then insert those records. In this case, the new records are having the same ID but different column attributes. When I am running the update and insert query, it’s only considering the last entry from table …
Recursive select that selects rows based own plus childrens values
I need to select rows in a table like this: Select all rows in the table where both conditions are met: Condition 1: the value column should not match with any value in table v Condition 2: no decendent (on any level, ie: child or sub child, sub- sub- child etc) has a value that matches with any value in
How to query and filter relation entity in room database
For example: User: Post: Data if i use SELECT * FROM user i got the desire data(a user and all posts), but how can i filter the post for a specific type, like WHERE post.type = ‘sth’ is that possible? Answer but how can i filter the post for a specific type, It depends on exactly what you want to