Finding the maximum difference between columns from different rows – Postgresql
Given the following data: Course_ID Teacher_ID Minimum_grade Maximum_grade 1 1 7 8 2 2 5 6 3 2 5 7 4 3 5 8 5 3 6 7 6 4 5 6 7 4 4 6 8 4 5 7…
passing an array of objects in postgresql using node
var query = `select * from map_balances where balance_userid = $1 AND balance_vendorid = $2 AND balance_amount >= $3` var values = [accesstoken,token,arr.map(e=>e.vid),arr.map(e=>e.amt)]; client.query(query,{arr:values},async(err,projectdata)=>{ if (err) { res.json({success:false,message:err.message,responsecode:-1}); } else { res.json({success:true,message:projectdata.rowCount,responsecode:200}); } }); here arr=[{"vid": 636,"amt": "21","add": "123123"},{"vid": 636,"amt":…
Find a value in the certain date based on update record column – Mysql
Table looks like this: id Product Price price_update_date 1 Product 21.99 2022-11-09 6:32:11 2 Product 22.99 2022-09-27 9:06:14 3 Product 19.99 2022-07-10 6:27:49 4 Product 18.99 2022-05-24 7:31:20 5 Product 17.98 2022-04-21 8:59:18 6 Product 17.99 2022-02-02 8:33:48 7 Product…
Generating Fibonacci sequence with PL/pgSQL function – Postgresql
I'm trying to generate the Fibonacci sequence with a function in SQL. It takes an input parameter pstop integer (10 by default) and returns a table with all Fibonacci numbers less than pstop. But the output starts with these numbers…
json column with nested values
I have a JSON type SQL column in SQL table as below example. I want the below code to be converted into separate columns such as drugs as table name and other attribute as column name, how can I use…
SQL to retrieve entire path from a parent child table – Postgresql
I'm oversimplifying my problem a bit. I have a table with the following format: id parent_id name 1 [null] id1 2 1 id2 3 2 aa3 4 3 bb4 5 2 abc 6 [null] abc1 I want to retrieve, in…
Postgresql – How to SELECT using a json aggregate function that takes two columns as key: value pair?
Supposing I have something like Name username Mike BestMike99 Someone DaBest What query do I need to run in order to SELECT/return a json like json_agg {Mike: BestMike99, Someone: Dabest}
SQL query to compare two rows within the same table – Mysql
Here I have pizza table pizza_id toppings 1 1,2,3,4,5,6,8,10 2 4,6,7,9,11,12 I would like to know toppings with each pizza_id in common. Most used toppings in both the pizza_id..., expected answer as below table Pizza_id toppings 1 4,6 2 4,6…
Postgresql : Repeat operations on 50 tables of the same schema?
I am new to Postgresql (v.13). I have 50 tables into a "ign" schema (schema other than public). I would like for each of these 50 tables: Add a prefix to the name of the table: "IGN_bdTopo_" Add a suffix…
Unable to get lowest value using join – Mysql
I have two tables info and sell_info. info table contains "list of record" and sell_info contains "product which are on sale". I want to get "lowest" value of record where id = 1. How can i do this? Here is…