Mysql – SQL select the nearest values with join
There are 2 tables: reference: id | score | value | type_id 1 | 0 | 10 | 1 2 | 1 | 20 | 1 3 | 2 | 30 | 1 .. | .. | .. | ..…
Mysql – Sql query for combining different status for item order id
I have three columns 1.order_item_id where datatype is STRING, 2.delivered_date_time where datatype is TIMESTAMP , 3.status where datatype is string , Write a sql query to combine the different status of particular order_item_id separated by semi-colon and first delivered_date_time of…
Mysql – Query to count a column based on the previous row's column result
I have a table with this data: create table my_table ( month varchar(2), services integer null, s_registered integer, s_terminated integer ); insert into my_table(month, services, s_registered, s_terminated) values ('01', 395, 14, 14), ('02', null, 5, 9), ('03', null, 19, 15),…
mysql virtual column incoherence wrt from_unixtime()
Got a table with a virtual column using from_unixtime(): CREATE TABLE aa ( id int NOT NULL AUTO_INCREMENT, epochmillis bigint, session_dt DATETIME(3) generated always as (from_unixtime(epochmillis/1000)), -- utc DATETIME(3) generated always as (convert_tz(from_unixtime(epochmillis/1000), @@time_zone, 'UTC')), PRIMARY KEY (`id`)); I can't…
Postgresql – Why does json_agg() complain about subquery returning more than one row?
I want to select every pet breed count, grouped by their owner and type. I've written a subquery that returns the breed count by pet type which is almost what i need, except i need the result in a json…
Php – SQL search many conditions must return one row
Problem is that i get a whole bunch of users, that does not match the search string no matter what i search. And it seems like there is baseline of users that is allways passed. I cant figure out what…
Postgresql – Find only distinct values from multiple columns in Postgres
Let us assume a Postgres table like this CREATE TABLE example ( value_1 VARCHAR(255), value_2 VARCHAR(255), value_3 VARCHAR(255), value_4 VARCHAR(255), value_5 VARCHAR(255) ); Each row has a different value for each of the columns, and each row has the same…
PostgreSQL Trigger Function Call Error: "function <function> doesn't exist"
I have a PostgreSQL trigger that fires after an INSERT on the auth.users table, calling a function handle_new_user() which, in turn, calls insert_into_profiles() to add user data to another table. The insert_into_profiles function is defined as follows: CREATE OR REPLACE…
MySQL multiple joins to get a single result set (with mysql max function)
I am trying to use a max(..) to get the maximum value from a set, but I want to also get the other row values that correspond to this row (or rows if there are multiple rows that match the…
PostgreSQL code reaching beyond EXISTS operator even for negation condition
for the below code, I have deleted the hist.game_hist table from db. Now, according to the code below, I want my code to pickup the else condition and avoid going into the then condition (as game_hist does not exists, it…