I have to add a dropdownlist to my existing project “moviesite”, i have a primary table moviestable for adding, edit delete movies. now added a new table Genre to add movieGenre. second …
Tag: sql
How do I query SQL for the latest record based on latest Date and Time OR Date_Time columns?
Situation: SerialNumber has duplicate rows and I need to select the latest records and get what is the Status value (either it PASS or FAIL does not matter). SerialNumber must be check based on the previous StationNumber. Example: I want to get the Status value of the latest record SerialNumber 19760123451234…
MySQL database re-orders entries automatically
I have created a MySQL database using MySQL Workbench. In there, I have created a table, the first entries of which are: id firstName lastName 1 John Smith 2 Emma …
Using LAG() on an UPDATE with CASE statements
I have a query on a Python Dashboard (using Plotly/Dash) that let users set some variables and update a table in BigQuery. I’m having trouble with one query that has some CASE statements and using LAG(). I wrote the following query: But I got the following error: “Analytic function not allowed in …
How to pivot in BigQuery?
I’m doing validations between source tables and destination tables in different processes in a Data Lake. I work with StandardSQL in BigQuery, and I do the comparisons between both tables with this query: And this is the result: Is there a way to normalize and denormalize this information as follows? I …
Select most recent record (with expiration date)
Let’s say that we have 2 tables named Records and Opportunities: Records: RecordID CustomerID CreateDate 777 1 1/1/2021 888 2 1/1/2021 999 1 2/1/2021 Opportunities: OppID CustomerID OppCreateDate 10 1 12/31/2020 11 1 1/10/2021 12 2 2/1/2021 13 1 4/1/2021 14 1 8/5/2025 Desired Output: RecordID CustomerID…
Update returning * and select result as rows
I want to run a few updates in a single query returning *, and select the output as rows. I currently have That does what I’m looking for but feels like it could be improved. I almost got it working like this but it displays as a single row not two separate rows Answer I think you simply want union
How do I process execution plan in PostgreSQL?
In PostgreSQL, when I run: I get: How can I get the “Total Cost” from there? I tried: But I get the error: Answer explain always needs to go before a statement, you can’t put it in the middle of one. You can do what you want by wrapping this into a function: If you can’t use a function…
SQL left join is not including all records from left table
I have a requirement to get monthly total hours worked on different tasks for each employee. For that purpose I am writing a stored procedure. But left join is not populating all record from employee table. I know somehow the where condition is filtering out the null values but I really don’t know how t…
Grouping rows based on a date field in SQL Server
I have the following table exams.sql with exam scores in SQL Server. It saves the student’s ID, their score, and the test date. The problem begins when a student takes the exam two (or more) times. I need the complete list of students with their actual qualification: in case they have been evaluated mor…