is it possible to create materialized views using the JOOQ builder? Something like this DSL.createMaterializedView().execute()
Tag: sql
Hive: randomly select N values from distinct values of one column
Suppose I have a dataset like this I would like to randomly select, say, 3 values from the distinct ID values. One possibility is to get a table like this How shall I do that in Hive? Answer Here is one option using a join and rand(): The subquery randomly selects 3 ids, then the outer query brings all relate…
Writing SQL query : getting the average value per minute
I want to make a query getting the average created value per minute. I don’t come up with any idea but using subquery like below. Would there be any other ways using only one query (without subquery) …
SQL Group with time
Sample table id dept ctime 100 A 09:15:12.0000000 200 B 08:00:00.0000000 300 A 09:00:00.0000000 400 B 08:50:00.0000000 expect to see countid dept xtime 2 A …
PostgreSQL DETECT age’s groups
Let’s consider a part of table like this: id_client | age ———–+—– 16052 | 65 25027 | 29 25659 | 44 33297 | 32 38750 | 53 43063 | 28 56311 | 48 …
Get all group by values in column a where all values in another column is true
I have a table that looks like this: I wanted to create a query that gives me a group by OrderID and Canbefulfilled but if there is a single 0 in Canbefulfilled column , I want that column to be 0 I …
How to use LOWER() on elements of a jsonb column in PostgreSQL?
I have a PostgreSQL table like this one: Table t id | keys (jsonb) —+—————- 1 | [“Key1”, “Key2”] My goal is to query this table to find out if one of the keys of a …
Oracle combines two records in one record despite of Union
I have created two simple queries to combine the records using UNION However, I faced a problem in one case, the data in the 1st table is exactly like the 2nd one Then the result appears in one record,…
Timecard report that shows hours worked every day, even when its zero
############################################ The above query produces this: query results What I want is for the work_date column to show every date in the month. If someone doesn’t work then WO#, act_hours, phase#, and trans_no would all be null but the work_date field, shop, and employee # would still…
How to count SQL based on a DateTime and an other variable?
I have a SQL table with failed testresults: This table is telling me that the test has failed, but I want to know if it is the first, second or third test. In the end I would like to do it with a logo where a x stands for a failed test, and a X for the concerned test, like