I have the following table in Postgresql. The first 4 records are the base data and the others were generated with the ROLLUP function. I want to add a column “grp_1” that will display the first non-null value of the columns grp1_l1, grp2_l2 and grp2_l3 I can get to the desired result by nesting 3…
Tag: sql
Find the names of last queried tables
I have a query inside my PHP program like I want to know the names of the tables called in this query, including joined tables. The aim is to find the schema of the affected tables which is required for further operations. Is there a direct query to do this or will I have to use some Regex? Answer You
Compare two arrays and count number of the same strings
I would like to compare two arrays in two columns and in the third column return the number of the same strings within those two arrays. Answer You can use unnest(). Assuming that the individual arrays have no duplicates:
How to join two unrelated table in MySQL?
I have two unrelated tables and I need to join in a single row as expected output below. I tried below query and doesn’t work. How to make join these tables?? Table 1 | col1 | amount| | a | 200 | …
SQL loop through columns with name to find max date
I’m looking for the SQL to loop through 20 columns with dates, to find the max. My code is kind of bad now: it’s oracle database, i konow that max date is in filed annex_20 Answer First of all, you don’t need ANNEX_XX_DATE is not null since ANNEX_XX_DATE<>… can’t be true if…
Find first N rows that have unique value with mod(id, N)
For example, N is 10 and a table looks like id 1 2 3 4 5 6 7 10 11 12 13 108 109 111 112 113 Need to find first N rows that have unique value with mod(id, N). Expected result is mod10 1 2 3 4 5 6 7 …
Does having two or more calls to scope_identity() in a single procedure work?
Does having 2 or more calls to scope_identity() in a single procedure work? For example: For sure @id_1 gets the last inserted id of table_1. The question is, does @id_2 get the last inserted id of table_2? Answer Yes, sure – if both table_1 and table_2 for an identity column – why not?? But pleas…
How to unnest multilevel structs in BigQuery?
I have many tables with different table suffixes in BigQuery where each table has four level structs in each row. I made query to unnest it into flattening table. It works correct but not very well because it is seems like too long. How can I make it simpler and more effective? Any suggestions. Thank you. Ans…
The python says ” ‘property’ object has no attribute ‘format'”
I am trying to use spark and I am stuck on the reading the data.. here is my code.. and the error message says that ‘property’ object has no attribute ‘format’ So I think there are something wrong with format.. I tried to read the code of spark but it was just too hard. I will really a…
SQL Query Performance Enhancement on NOT IN
Suppose I have a query need to lookup users who bought book A but not bought book B. Typical SQL query can be: This not in query looks not efficient, any enhancement I can do this query? Answer Try using exists clause instead of IN clause – Apart from this you my try having index on below columns –…