I have the following table BOOKSALES. Some Background about the attributes – BookID – The unique ID of the book SaleDate – Date on which the book was sold RegisterDate – Date on which sale was registered in accounts books The table looks like this – Now I want to execute the quer…
How to pivot given string by replacing special characters in SQL?
In an interview, I have been asked to pivot a string by replacing special characters. Given string was ‘AAA%BBB$CCC#DDD’ Expected output as shown in then image: How can we do this in SQL Server? Thanks in advance! Answer In SQL Server 2014?(I guess the operating system you are using is Windows Ser…
Maintain order when using SQLite WHERE-clause and IN operator
Consider the following tbl: For reference, tbl looks like this: When issuing the following statement, the result will be ordered according to ID. However, I need the ordering to adhere to the order of the passed list of values. Here’s what I am looking for: Answer You can create a CTE that returns 2 col…
SQL – Combine two rows if difference is below threshhold
I have a table like this in SQL Server: What I would like to have is a query that outputs consolidated records based on the time difference between two consecutive records (end_time of row n and start_time row n+1) . All records where the time difference is less than 2 minutes should be combined into one time…
Snowflake unsupported subquery when using function
This is the function I created: I’m using the ROW_NUMBER function because the RATE_DATE field is actually datetime and so there are multiple records per date. When I call the function by itself, it works fine. However, when I try to use it in a view, I get the unsupported subquery type error. The view w…
Populate a List in one entity using information stored in a different table using EF Core 5
Summary I have two tables, a Users table and a Sponsorships table. The Sponsorships table has two foreign keys to the Users table. The relationship here is that a user can be sponsored by a several different users, and a user can sponsor several different users. I want to use this information to fill a list o…
How to get the rows (based on timestamp) of the table that reached the last intent
I’m trying to find in some chatbot logs the users that reached the last intent, but I need the intent before that one. i.e. Table 1 ID Intent 1 last 1 first 2 first 3 second 4 last 4 second 4 first would need to get this ID Intent 1 first 4 second Answer Consider below approach (note you need
MySQL: gets the id instead of the value
I have two tables in my database. Men table: Women table: When I do a inner join, I get: instead of: It gets the id, instead of the partner name. Anyone know what is wrong with my query? Answer
SQL/Presto: rank with multiple conditions (one condition is checking if rows is in the same tag)
I have a data (dt) like the following: I want to rank them within a group, first by score (lower score is better) then concat customer_id if the 2 customers are in the same group with the same score, and same tag (NOT null) I need to rank them by phase (older phase is preferred) to produce a final list.
Updating all the record for a column in a table with value from another table
Hi I want to update all the values of RequestId column of IIL_CHANGE_REQUEST Table with the RequestId of TABLE_NAME_4MINS Table where REQUESTBY column in both tables are same. I am trying to do this in oracle daatabalse(sql developer) My query: But every time I do this I get an error saying: Error report R…