I have two tables with 2 columns. I cross join and subtract the values. I then find the row_number ordered by the subtraction and choose where row = 1. I’m finding the t2.id that has the closest val to t1.id These tables are quite large. Is the row_number function doing a lot of extra unneeded work by ordering everything after
Tag: cross-join
Evalaute Oracle Table to Calulate or Extend Multiple Date Ranges into One Period
I have a table that shows date ranges of an object. For reference The object needs some sort of event in order to prevent it from expiring. We have a rule where we can apply a protection against the object from expiring, so that there is no need to action the object. The date ranges are a period of time
How to output every combination of values in 2 columns, in groups? – SQL
I have a table where I want every parent to be mapped to every child within each group. Input: Output: So within each group, I want every value in parent to map to every value in child. If there are no values in child for a group, I want that group completely omitted (as shown where group_id = 3) I
Performing a Union on groups of rows without the same columns. Trying to generate empty histogram bins
I am using MySQL. I have a table grades, that looks like so: I have a view that counts how many grades are in each bin, for each class using the following query. This returns: I want to add empty bins to this view, like so: So the bins increment by 5, starting at 40, and stopping at 100. My
Microsoft SQL Server , view transformation
Is it possible to transform the following view : to this structure ? I’ve tried with cross join but I have no idea how to make a condition based on column name. Answer You need APPLY instead of JOIN to be able to access outer columns
SQL: Retrieve missing values from superset
I have a table like this: I want to return a table with all the ratings possible. In this case 1 to 5. So my resulting table will be: I have tried using something like: but this does not work. Can you please suggest a solution for this? Answer You can cross join the list of distinct devices with numbers
SQL Statement summarize missing employee certifications
I am trying to create a report on tables that I can’t modify and am not sure if this is even possible. Using the script below, I am trying to get a query result of: Answer You can cross join employees and certifications to generate all possible combinations, then use a subquery to check whether each tuple exists in the
JOIN multiple tables based on one reference table and count records
I have these following tables: cars: id manufacturer ——————– 1 Nissan 2 Toyota 3 Honda 4 Volkswagen type: id type car_id ————–…
PostgreSQL cross join using max returns null
I want to select the max() value of the modified_at column in each of several tables: This works correctly as long as each of the tables has at least 1 row. The problem is that when just one of the tables has 0 rows, null is returned for all tables: What is a solution that returns the correct values for
How to join generate_series and an array
I want to generate a list of columns using generate_series() and joining it to a specific array (a list of value, e.g. [1, 2, 3]), and my expected result looks like this: Day | Code 2019-08-01 …