Skip to content

SQL query: how to filter a many to many relationship

sql

I have ONE table, contains a clientID and a subclientID, how to filter this to retrun clients that contain BASE and 1 ONLY. In this case, I would like to return client 46 clientID subclientID 44 1 44 9 44 BASE 44 2 45 BASE 45 2 46 BASE 46 1 46 1 EDIT: subclient may contains duplicates Answer Here’s

Find student id with highest marks using INNER JOIN and EXCEPT?

Given a table, find the highest marks using INNER JOIN and EXCEPT. It straight forward find marks. Select max(marks) from Students. But how to find highest using INNER JOIN and EXCEPT? Students Table sno name marks 1 A 90 2 B 95 3 C 96 4 D 82 5 E 87 Answer You don’t need to write an INNER JOIN

How do you swap two attributes in array column on Bigquery?

I have a table with two columns: group_id, students (Array of student). Each student have a two attribute: name and last_name. How do you swap name with last_name for each student? I need to swapping students.name to students.last_name, any idea? Answer Finally, thanks to Mikhail Berlyant who answered this qu…

Using Cases for currency conversion in oracle

I have two tables what I am trying to find is the ‘daily amount GBP Equivalent per day for the last three months’. For example on date ’05/01/2022 ‘ check the given amount in Payments_history table if the currency is GBP add it into total and move on to the next payment, next if the cu…

How can I join SQL subqueries as they are?

I have 3 subqueries that when executed independently they all return 3 rows with the desired columns and values. Once I put them all in the from statement and select them all This is the result of all three subqueries being respectively executed standalone Standalone This is the result Snippet above being run…

Return last year value with last_value function in SQL

I need to return the last value of the last date of the year. the problem is that the last value that already comes in SQL is being returned. Product Date Value Last_Value_Previous_Year Prod A 31/12/2020 5000.00 2000.00 Prod A 01/01/2020 2000.00 2000.00 Prod A 01/01/2021 1000.00 3000.00 Prod A 01/02/2021 1500…

How to get distinct parent_sku count for the below dataset?

Need your help to extract distinct Parent_sku count from the below data set. Condition: If one child of a parent_sku has “High” sales_tag then that parent_sku count should be excluded from “Low” sales_tag. P.S. Sales_tag column is based on child_sku column. Thank you for your help. Dat…

Faster counts with mysql by sampling table

I’m looking for a way I can get a count for records meeting a condition but my problem is the table is billions of records long and a basic count(*) is not possible as it times out. I thought that maybe it would be possible to sample the table by doing something like selecting 1/4th of the records. I be…