I am trying to get old value from variable in PL/SQL for loop. For instance: I have l_sequnce variable with increment of 10. I hope I described the problem well 🙂 Answer Use ORDER BY in the SELECT statement and then you only need to check if the value has changed between the previous and current values: Or, d…
How to parsing Json in AWS Athena?
I want to parse the JSON column in Athena but I have a problem in one column. The column includes an escape character. I don’t know if this problem. I want to reach to Message. You can see below sample data and Message under Return. Thanks This is the JSON. Answer If you take only the object which conta…
How to get incremental values running in another column
I have a sample Data like this I’m trying to get result like this I have tried with row number but not able to move forward. Answer If you’re not doing this with tens of thousands of records at a time, you could try something like this: This will give you the result based on this source data:
Deleting massive number of duplicate records without using a new table
Right now I have a table that has a massive number of duplicates that need to be deleted (about 500 million). I have a query that will delete all the duplicates, but it is unable to finish the whole query due to the transaction log being filled up. Moving the nonduplicates to a new table, then renaming it, wo…
How to properly perform a SELECT query [closed]
Closed. This question needs debugging details. It is not currently accepting answers. Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question. Closed 12 months ago. Improve this question I h…
SQL count longest consecutive days with dates table
I have a login table with columns UserID and login_date like below (but with thousands of entries). How would I find the longest streak of consecutive log-ins for each user and what the start and end days were for those streaks? Edit: Using Postgres. Removed question about streaks only counting M-F and not we…
SQL: select max(A), B but don’t want to group by or aggregate B
If I have a house with multiple rooms, but I want the color of the most recently created, I would say: But I get the error: Column ‘room.color’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. If I say max(room.color), then sure, it …
Combining two queries with case statement in BigQuery
I’m new to SQL and have been trying to combine two queries that give me a count of unique uses by day of the week (‘weekday’ – with days of the week coded 1-7) and by user type (‘member_casual’ – member or casual user). I managed to use a case statement to combine the…
How to add pagination to this dynamic sql
I have the following table with its respective data I wanted to be able to sort the dynamic column example sort by ‘StudentName’ ASC it would sort data using column returning When sorting by StudentName: When sorting by Grade ASC: When sorting by TeacherName ASC (different table) I have manage to …
MS Access: Why is my error message appearing immediately after entry?
I have created a form where I enter in faculty information and I made error messages appear if certain fields are missing. However, as soon as I click save, the form saves the entry like I wanted, but also immediately generates the error messages I created, even though I didn’t have a chance to enter an…