Skip to content

SQL Query/ Assigning Rank

I am interested in SQL query not the PLSQL code. We need to assign the rank based on date and id value Input table should look like below output table should look like below Answer This is a type of gaps-and-islands problem. In this case, the simplest solution is probably the difference of row numbers: Why th…

process/transaction data partitioning

i have problem with partitioning the process data i have from our workflow system. data are stored in database step after step (one step is one row) for couple of processes (id) and a date of the event. in simplified data is looks as follows: what i need to have is such short table the “x” step is…

Easy substraction of year’s values

I do have the following database table containing the timestamps in unix format as well as the total yield (summing up) of my solar panels every 5 mins: Now I would like to calculate how much energy was produced each year. I thought of reading the first and last timestamp using UNION of each year: This works …

Amazon Redshift – lateral column alias reference

Based on Amazon Redshift announces support for lateral column alias reference: The support for lateral column alias reference enables you to write queries without repeating the same expressions in the SELECT list. For example, you can define the alias ‘probability’ and use it within the same selec…

Select the last record in MySQL in WHERE condition

I have a table that store ticket and statue relation I want to select rows that last status_Id equal 2 ,Rows are marked in table. I think I have to use GROUP BY on column ticket_Id but it return with first status_Id. Answer This problem is a good candidate for ROW_NUMBER: The above logic finds all latest rows…