I have two table that I am trying to join(see below). Each have the same four columns on which I need to join. The second is the complete list, while the first may be missing some records. Where the records are missing I want to use the lowest ROWNUM that matches the other three. I am failing to see a
Tag: sql
Presto – how is there an alternative to to_char like postgresql?
I need to build a query in presto that could look back the trailing 70 days, the table I am working with is storing the dates in the format of ‘YYYYMMDD’. in postgresql, I can just simply write the …
One-to-Many SQL SELECT concatenated into single row
I’m using Postgres and I have the following schemes. Orders Comments So, in this case, an order can have many comments. I need to iterate over the orders and get something like this: I tried to use LEFT JOIN but it didn’t work it returns this: Answer You are almost there – you just need aggr…
Insert into table multiple rows with one different value
I believe my question is kinda simple but I haven’t found something like that upon searching. I want to insert into a table testing multiple rows for example: (’03’,’ENG’,’203′) (’03’,’ENG’,’403′) (’03’,’ENG’…
Pandas equivalent for SQL – arithmetic expression within aggregate function
I am a pandas newbie coming from a SQL background although have some exposure to Python. I was wondering if there is a simple way to do the following SQL code in pandas dataframe: Below is all I got so far there doesn’t seem to be a syntax to include arithmetic expressions: Thanks in advance. Answer try…
Pivot rows in a different way using MySQL or SparkDataframe
I have a table like this,I am doing normal pivoting,which is not giving the desired result. I want to get it in this way: I tried doing it like this : But its not giving as expected. Can any one suggest what can be done for the desired result ? Normal SQL / DF solution both will be helpful. Answer
Using global list in recursive SQL query to avoid visted nodes
I have a self-referential table User: Note that there are circular references. I want to get all the followers, and followers of the followers, and so on of a user such that all the followers are presented in a flattened list, with their respective depths For Adam: Problem I want to avoid the rows 3 and 4, wh…
Evaluate Multiple conditions for same row
I have to compare 2 different sources and identify all the mismatches for all IDs Source_excel table Source_dw table Expected result I have tried the below query but it is giving only one mismatch. Actual output I understand that case expression will only check the first satisfying condition. Is there any oth…
How to convert data into this form in SQL :
Input: I have to Convert data from input to output. where we trying if for id freq is n then create n rows of that id. Output: Answer In Presto, one option uses sequence() and a lateral join to generate the rows:
Stored procedure to unpivot
I have a table with these columns: Where fields from 01 to 50 are dates. I need to have a table like this: Where title code should be the column name from 01 to 50, data should be the value of that column in mytable I did this stored procedure by looking in older questions: but I’m getting a syntax