Mysql – Result without a subquery
I am trying to come up with a solution without a subquery which was asked in one of the interview. customerId in Orders table is a foreign key referencing id in Customer table. Table: Customer: id is a primary key…
Postgresql – Problem using json_array_elements to extract values from array of JSON Objects in Postgres
I have the following code in which I am trying to extract JSON as follows: DROP TABLE IF EXISTS tmp2; CREATE TEMP table tmp2 ( c TEXT ); insert into tmp2 values ( N'{"SerialNumber":"907578","SoftwareVersion":"1.2.777","Build":"4829","ProductCode":"TR-3500-A","BuildDate":null,"Description":"Study desk","ConnectionType":2,"Capabilities":[2,3], "ChannelListDto":[ {"ChannelId":0,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":"test2","InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null}, {"ChannelId":1,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":null,"InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null}, {"ChannelId":2,"ConversionType":0,"DeviceSerialNumber":null,"Dimension":"","FixedName":null,"Name":null,"InstrumentationChannel":-1,"IsAlarmable":false,"IsInternal":true,"IsEnableable":false,"IsEnabled":false,"JournalledReadingBytes":0,"LowerLimit":null,"Precision":null,"Symbol":"","TypeId":5,"UpperLimit":null} ]…
Retrieving min value in json array from MariaDB table
I have a MariaDB table including a JSON field: { arrayOfDates : ['2020-01-11', '2021-01, 12', '2019-03-12'], ... } For each record in my-table I need to extract the field arrayOfDates and pick the min value in the array. Note I'm…
Postgresql – How to access fields of anonymous record?
Given the following query: WITH t as ( SELECT name, array_agg(DISTINCT("age", "gender")) as "ages_and_genders" FROM ( SELECT * FROM (VALUES ('bob', 33, 'm'), ('bob', 33, 'f'), ('alice', 30, 'f')) AS t ("name","age", "gender") ) as t GROUP BY name )…
Run UPDATE mySQL Query on Multiple Rows
I have a table like this librarylog patronName bookId dueDate John 24 2024-01-14 Sam 42 2024-01-07 Bob 13 2024-01-07 I'm trying to create a SQL query which updates all the dueDate values and increases them by a week. This is…
Mysql – update maintenance date in customer table from the latest date in service table
Customers Field Type Null Key Default Extra recno int NO PRI NULL auto_increment CustomerKey int NO NULL MaintDate date YES NULL Service Orders Field Type Null Key Default Extra recno int NO PRI NULL auto_increment CustomerKey int NO NULL ServiceDate…
MySQL ORDER columns by empty last but without alphabetical order
I'm trying to order columns by empty string last. For example. NAME -------------------------- Apple '' Orange Avocado Banana Cauliflower Broccoli '' Potato Cabbage The order should be like this without alphabetical order NAME -------------------------- Apple Orange Avocado Banana Cauliflower Broccoli…
Mysql – Why this query not having an output? | I want to filter out the ('student_id', student_name') Who never scored maximum or minimum
Query 1: WITH cte AS ( SELECT *, MAX(score) OVER (PARTITION BY exam_id) AS max_score, MIN(score) OVER (PARTITION BY exam_id) AS min_score FROM student JOIN exam USING (student_id) ORDER BY exam_id, student_id ) SELECT * FROM cte; By Query 1,…
MySQL – inner join on two tables simultaneously
I have this join: SELECT * FROM persons p LEFT JOIN orders o ON p.id = o.person_id LEFT JOIN old_orders oo ON p.id = oo.person_id LIMIT 100 orders and old_orders have exactly the same structure and for misc reasons they…
Mysql – SQL how to drop rows based on values from two columns
I have a SQL query result I would love to clean up, the query result is following NAME OPT_IN JOHN TRUE JOHN FALSE KEK TRUE and now I want to select | JOHN | FALSE | when | JOHN |…