Here I am creating three tables, one for storing the Train Info. Another for holding onto the Passenger info and the other one to hold the ticket Info. In the Train_Info table, I am inserting two unique routes with the same source and destination as there can be different routes for the same source and destin…
How to get record by Date in my case using SQL Server?
I have 2 tables ProductLog and Product ProductLog Product The select statement RegNo value to be ProductLog.RegNo , which is Product.Date <= ProductLog.TransDate I want to get a result like below Expected Output I tried by ranking order Date by ASC, and where rank =1, it set all to lowest date value. (I un…
Finding the immediate upper and lower of X
For a project, I have a table nearest_values as id value 1 8450 2 8500 3 8550 4 8600 5 8650 6 8700 Given a value say 8555, I need to select rows 2 and 3 ie immediately below and above as below. id value 2 8500 3 8550 another example for say value 8601 the selected rows should be
SQL Function has no destination when called
I’m getting a success when creating the below function, but when I call it I receive an error stating there is no destination. Answer Language SQL: https://www.postgresql.org/docs/current/xfunc-sql.html language plpgsql: https://www.postgresql.org/docs/current/plpgsql.html Language sql is more easier to…
Is there an UPDATE equivalent command to SELECT json data
Is there a way to update the data retrieved from the below select (in this case, change “MS220” to something else)? It’s difficult enough to do select from JSON in some cases. I’m not sure how to update just a single element. Answer Try JSON_MODIFY() with the path ‘$[0].Name̵…
Display SQL query result on front-end WordPress site
I am trying to get the total data (SUM) within a column in a WordPress database. I am using the following SQL (and PHP) code to run the request; however, I do not know how to display the result on the front end of my site. or I have tried using print_r($avg_items_purchased); and var_dump($avg_items_purchased)…
How to get data from json column in SQL Server that starts with array element
I have a database table that I need to extract data from where the column of interest has json it. What makes this particularly difficult is the most outer elements of the json is ‘[‘ & ‘]’ as in the parent element is an array. I need to get the value associated with key ‘Nam…
How to keep table name when inner joining related tables
I am new to SQL and wonder how to select nested tables. I have two tables like this: sensors sensor_id model_no location_id int varchar int locations location_id name location radius int varchar point int They are linked with foreign key. Currently, I select using to get the data from both like this: I wonder…
Postgresql sum not working as expected when it is clear
I am solving the following Hard Leetcode SQL Question. Link to Question: https://leetcode.com/problems/trips-and-users/ (You can directly look at the solution and understand the problem) Question: Trips table: Users table: Output: Here’s my code: And the output is: How is the cancellation_rate is 0.00 w…
Create Unique ID for Groups of Records
I spent a good amount of time searching for a solution on here and google but came up empty. I thought dense_rank() might work but I can’t get it to do what I’m needing. I’m using SSMS 18. What I’m trying to do is assign a unique ID to groups of records that I have partitioned with row…