I have this dataset : UserPlans table : how to only select this row (with ID 3) since this data has been deleted but doesn’t have any data with deletedAt = NULL with same userId ? Answer You seem to want rows where userid has no other row whose deletedAt is null. If so, you can use not exists and
LINQ for getting the 5 latest records for each distinct name
I have been trying to get the 5 most recent records from a database with distinct names from the “Name” Column. Meaning I want the 5 latest records for each of the unique names in the table. Here is a sample table: I would expect the latest 5 Bob records (out of the 6 ), the 3 Chris records, and
If Row from left joined table is null use another row
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
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…