Skip to content

SQL – First n non null columns

I have 30 columns like p1, p2, p3,……,p29, p30. Out of them, any 6 consecutive values will be non-null and the rest are all none. I need to write an SQL query (preferably Redshift) to get all of them into 6 columns. Say a1,a2,a3,a4,a5,a6 Eg. If I have 50 rows of data with 30 columns with a lot of n…

Optimization of Group by Cube in SQL Server

I would like to make a GROUP BY CUBE of a table with 9 columns and more than 107 millions of rows. Here is an example of my code: This is running in SQL Server. For 10K rows it is taking 7 seconds, but when I increase the number of rows to the total 107 million it took more than

Compare Two Relations in SQL

I just started studying SQL and this is a demo given by the teacher in an online course and it works fine. The statement is looking for “students such that number of other students with same GPA is equal to number of other students with same sizeHS”: It seems that in this where clause, we’re…

Search data based on two columns SQL

I have a database table like this: I need search from Id the Id’s that not have Tipo = Inventario and Create a new line with that data, I mean for example, RTOR-00261 and RTOR-0255 dont have INVENTARIO, I need to create new with the fields that there is in SUSTRATO. All data are in the same table Final …

I try to find average salaries by following query

sql

I need to find that who earn more than the average salary of their own companies but if some company have more than one employees ( earn more than of their own company average salary) so then I need to select the highest one through of them Result:- but I need the result as: the salary of emp 5 greater

Comparing count(*) with select for one result

I have a table tb_xyz having field1, field2 as indexed. Now I have two queries: and Assuming both queries are written to just check if this condition is met at least once. Which of them is more optimal. I tried checking for their execution times, but results were mixed, for small data. Answer The first query …

Query based on some data multiple compare

Table: How do i bring the period when customers have max cc_limit? If the cc_limits same for customer for more than one month, take the period with highest year-month data Answer Use row_number() window function,Below should work. EDIT : To clarify why order by should be in the inline view. Scenario : When th…