My table is like I am trying to get customer_ids where they didn’t do any transactions previous month and made transactions with at least 2 transaction_type at this_month.While doing this I am converting rundate to MM-YYYY format. What I’ve done for getting previous month and this month on MM-YYY …
Tag: sql
Merge tables with unequal number of columns without returning duplicate rows
I have two tables, one is called original_table and the other is table_with_additional_column. table_with_additional_column is a subset of original_table but with an additional column called sequenceNr. I want to merge the two tables such that I can get all rows from original_table with sequenceNr set to NULL…
Format JSONB column by taking the text value from same jsonb coulmn
CREATE TABLE test(id serial, data jsonb); INSERT INTO test(data) values (‘dummydata-got-uploaded’); I need to correct the jsonb column value with below query. update test set data={“addDet”: data }::jsonb where id =1; ERROR: syntax error at or near “{” LINE 1: update test s…
Oracle bug when using JSON_ARRAYAGG with CASE expression to emulate standard SQL FILTER
I’m using Oracle 18c (reproduces in 19c as well) and I’d like to emulate the standard SQL FILTER clause in the JSON_ARRAYAGG() aggregate function to filter out values from the aggregation. However, this produces an error: The error being: ORA-40590: invalid format dbfiddle here. What’s causi…
How can I delete records that have the same values in two non-primary key columns while factoring in a third?
Here is a link to a sqlfiddle with sample data: http://sqlfiddle.com/#!18/d8d552/3 I’m trying to delete 1 row from rows that have the same values in both name and color. If they have they same score, then it doesn’t matter which one is deleted. If those rows have a different score, then I want to …
How to join a table with itself?
I want to ask how can I join the table with itself, using the same column twice, and giving each a different WHERE to sort out. I have an Employees table that contain Employee_name, job_id and manager_id. I want it to get the name of all the managers through the job_id on one column, and then all the employee…
How to select a table that have one to another and another table relationship
I don’t know if the question make sense or not. I’m trying to request information from 2 different tables that are linked (via FK) to a table and display the data with information from that linked tables. post user role Is there a way to select/show this table?: result Answer Try this:
Rails & Postgres selecting sub keys of a JSONB column
I have a column of a database table that is of type JSONB and I’m wanting to get some data from that column. For the most part the column is a flat list of key value pairs. Ex: However, the key I’m after contains an array of JSON data (or can be null/nil): Now, what I want I do
i need to display customer’s first name and last name who have made the most number of booking. Using select
So using this table i have to display customers F Name and L name, so I tried using (Group By) but didn’t work Answer It sounds like you just need the name of the person with the highest value Bookingtotal. You don’t specify your RDBMS, however Your specific database might use limit instead of top…
SQL zero-to-many column comparison
Say I have two columns in a table, a and b. For each value of a, there can be zero-to-many values of b. If the columns are sorted by a, then by b, what is the most efficient way to mark the first value of b which is greater than the corresponding value of a in a new third column?