Mysql – SQL Ifelse by Group
I have SQL table such as this: TABLE1 STUDENT SCORE SCOREVAL 1 PASS 10 1 MOD1 4 1 MOD2 5 2 MOD1 4 3 MOD2 2 3 MOD2 1 3 MOD1 9 3 ER 1 4 MOD2 10 4 ER…
Format valid JSON for associate object in SQL Server
This is my query: SELECT a.Id, a.Title, (SELECT m.Id, m.Name, m.MobileNo FROM [msm].[MsmMember] AS m WHERE m.Id = a.MemberId AND m.OrganizationId = @OrganizationId FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS Member FROM msm.MsmArticleSubmission a WHERE a.OrganizationId = @OrganizationId AND a.Id = @Id…
Postgresql – Return 3 select results in same query window
I have 3 queries that I need to see in the same query editor window but can't seem to figure out how Postgres wants it. I have a background in T-SQL where it's intuitive enough to run this and return…
Mysql – Implementing FIFO (first in first out) in sql
I have a table of stock ID Qty DatePurchased Price 11202 4 01/01/2023 3 11202 3 12/06/2023 7 5050 4 11/10/2023 60 11202 4 12/10/2023 5 5050 8 12/12/2023 70 and Item sold table ID Qty 11202 6 I want…
Json – Snowflake – FLATTEN() – How does it work internally?
I am learning Snowflake right now, and the way FLATTEN() works is a bit counter intuitive. A simple query. SELECT raw:first_name::STRING AS FName, raw:last_name::STRING AS LName, f.value::STRING AS Skill FROM tbl, TABLE(FLATTEN(raw:Skills)) f ORDER BY raw:id::INT LIMIT 5; Elegantly, it…
Comparing possibly non-matching records in MySQL
I have a database table for a telephone billing system. Rates are matched by the longest prefix found, so that more specific rates can be used for certain numbers. Let's say the table looks like this: Column Type id SERIAL…
How to query JSON data with multiple nested arrays in SQL Server?
My business requirement is to store lists of issues & responses as JSON data within a SQL Server 2019 table. Each list corresponds to a page within a document. At minimum, I'd like to be able to retrieve the GUID…
How to deal with JSON encoded string in MySQL REPLACE Statement
I have this string in some MariaDB Column (let's called it MY_COL): {"libelle": "comu00e9dien"} that i'd like to replace it with {"libelle": "acteur"} Using this STO post, I figured out how to select rows containing this string: SELECT MY_COL FROM…
Json – Unnest elements of each object in Trino/Presto
I have a column called users with this JSON. { "[email protected]":[1.0,5.95], "[email protected]":[2.0,30.733], "[email protected]":[1.0,4.433], "[email protected]":[2.0,16.25] } I want to unwrap it in this way splitUsers firstValue secondValue [email protected] 1.0 5.95 [email protected] 2.0 30.733 [email protected] 1.2 4.433 [email protected] 2.0 16.25 I managed…
Php – SQL to select persons with same birthday
I have a table (tng_people) and I want Sql to select all the people with the same birthday. I want personId, Lastname, Surname, Birthdate and deathdate. SELECT P.birthDate, DATE_FORMAT(birthDate,'%d %b %Y'), GROUP_CONCAT(CONCAT(familyName, " ",givenName)) AS PersonNames FROM person P WHERE…