I have SQL test table as below: Item Doc_No Code Line_Item 1 abc1234 101 01 2 abc1234 102 01 3 def5678 101 01 4 def5678 102 01 5 ghi1234 101 01 6 ghi1234 101 02 7 jkl5678 101 01 I am trying to eliminate rows when duplicate values of “Doc_No” has pair values of “101” and “102̶…
Convert Postgresql into HiveSql
How do I convert below mentioned postgresql query into HiveSql Answer Use CASE expressions like this: Also in Hive version >= 1.3 you can use quarter function:
How do I restrict my SQL aggregation Rollups to a specific grouping?
The following SQL returns 5 grouped columns and two aggregated columns: I do want the rollup to the level of TenancyName, but it don’t need all the other intermediate rollup lines. In fact, you can see that rolling up from the Doctor’s (Employee’s) row up to the EmployeeId produces the exact…
SELECT DISTINCT in Redshift subquery not deduping when using ORDER BY in outer query
The Problem While using a CTE built from the information_schema, SELECT DISTINCT within a subquery, and an ORDER BY in the outer query, the output included duplicated results unexpectedly. I’ve tried …
How to have MySQL return multiple column of values based on a different benchmark?
I have a MySQL database table (sales) consisting of sales data with their dates. I have a SQL query that relies on a specific date benchmark. This query supposedly returns only one numeric value. Now I set the condition to be dependant on the benchmark. The problem is, I don’t want this benchmark to sta…
how to select third, fourth, fifth date in sql using window function
The table rental of movies has this data: how to generate pairs of : first rental_ts, second rental date, second rental, third rental date, …. second LAST rental date, last rental date, last rental date, null? using window functions- this is not working: i expect: basically i need more than 1 level back…
How do I perform a dynamic WHERE clause in QuestDB?
I’m looking to create a dynamic WHERE clause in QuestDB for something like the following example SQL: What’s the nicest way to do this? I can’t find guidance in the WHERE keyword documentation Answer This depends on what you’re trying to achieve, but what might be useful is using coale…
Custom Week Number SQL
I need to convert the following logic to python and SQL (SQL query is more imp): I have a table with ID and Date columns. I need to add a column called “Week_Num” such that: Every time it sees a new ID, Week_Num becomes 1 7 dates correspond to 1 week so if the first week begins on 29th Oct
how to create schema in mysql and how to import existing record into the database?
I have file with name data that contain information like in my linux machine I have created a database in Mysql with name “invaliduser” and inside that i have create table “invalidlog” and i created a schema like this when i am trying to import the data file in mysql with the following…
Need help in framing SQL Query
TABLE-1 OrderDate CustomerNo ProductId Quantity 2021-03-01 1 1 10 2021-03-01 1 3 20 2021-03-02 1 2 15 2021-03-02 1 3 10 2021-03-03 1 1 10 2021-03-03 1 5 25 This table also contains data for other customers also Customers 2,3,4 … TABLE-2 ProductId ProductName 1 P1 2 P2 3 P3 4 P4 5 P5 Products are not Fix…