I have a variable @k=35. and a table xrf having contents as show below. How to I get the values in between which @k exist in table xrf. output is 30 and 40. Answer Using conditional aggregation
Tag: window-functions
Count of line item in MySQL
I have a complex query joined to multiple tables to show a “transaction history”, I want a quick and easy way to count the occurrences of order_id to make it as the line_number. I had it done with code but for performance reason I want to have it done with MySQL. Here is an example of what I have:…
Group rows by dense_rank() and loop through each sub-group and compare another column in next row of that sub group?
I have tried the following in LINQPad: I want to write a query which will return only the IDs 1 and 2 (not 3 and 4) because: ID 1 – has more than 1 rows and startdate of its rownum 2 is 1 day ahead of enddate of its rownum 1 ID 2 – has more than 1 rows and
Recursive CTE have performace issue, need suggestion to optimize query
I wanted to get TOP 5 records from log table where “Approve date” is changed like NULL to value and vice versa. Date value is doesn’t matter, but order matters. In this case I want first record and 5th record (Someone approved the data that’s why a value),then 7th record value is null someon…
Get Max And Min dates for consecutive values in T-SQL
I have a log table like below and want to simplfy it by getting min start date and max end date for consecutive Status values for each Id. I tried many window function combinations but no luck. This is what I have: This is what want to see: Answer This is a typical gaps-and-islands problem. You want to aggreg…
Create query to get count of uncompleted calls
There is table waiter_log as Call with call-id ‘f27de4f’ started in 9010 and finished in 9003 because there is a record with proceed_wait = 0 for call-id=’f27de4f’ Call with call-id ‘asdf231′ started in 9010, still proceed in 9002 and not finished yet because there is no re…
Getting date, and count of unique customers when first order was placed
I have a table called orders that looks like this: And that contains the following data: I would like a result set returning each unique date, and a separate column showing how many customers that placed their first order that day. I’m having problems with querying this the right way, especially when th…
Find all rows in between a set in PostgreSQL
I have a table named tc_fuel that is receiving all the fuel related data from GPS vehicles, and I want to get the last “Tank Usage” to calculate MPG for a whole tank, but the readings when the tank is …
I need a Row_number over partition to achieve this in SQL Server
I have three columns PID, AppNo and ProcessedDate I need a query to update the AppNo in the format below PID AppNo ProcessedDate 11 1 09/30/2019 18:21 3 1 09/25/2019 08:37 3 2 …
How to use LAST_VALUE in PostgreSQL?
I have a little table to try to understand how the LAST_VALUE function works in PostgreSQL. It looks like this: id | value —-+——– 0 | A 1 | B 2 | C 3 | D 4 | E 5 | [null] 6 | F …