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: This
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 someone rejected it. I tried using recursive
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 aggregate groups
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 record with proceed_wait = 0 for call-id=’asdf231’ Similarly for call with call-id ‘rete125’ there
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 the data consists of multiple orders the same
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 …