Skip to content

Tag: sql

Remove duplicates with least row ids from Oracle

I have a database table which looks like ID Book_no Book_name Book_category ID1 1 B1 CB1 ID1 2 B1 CB1 ID1 3 B2 CB1 ID1 4 B2 CB1 ID1 5 B3 CB1 ID2 1 B1 CB2 ID2 2 B1 CB2 ID2 3 B2 CB2 And the expected result is like ID Book_No Book_name Book_category ID1 2 B1 CB1 ID1 4

INSERT INTO SELECT AND VALUES

Error: “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.” Answer The error is pretty clear. The outer select returns too many rows. You can just remove it: Note that the constant val…

NULL changes to 0 when added to the db

Ok I have tried everything I can think of, I couldn’t make it. I am creating some inputs like this: And then when I get the $_POST I am checking if the input is 0 or “” and setting it to NULL. And I am adding to the DB with this But when the query runs, qty6 (for example) that

Merging Rows and put values in different columns

I have the following table: and want to get to this result: An ID value in the first table occurs max 4 times: Booked + 1 = was either marked booked the first time in database OR was either marked booked the last time in database Booked + 0 = was either marked not-booked the first time in database OR

SELECT WHERE multiple records don’t exist

sql

I have the following two tables. attempts: attempt_tags: I’m looking to select a record from the attempts table where (for example) tagid 2 and 11 are BOTH not present, so the result of the query in this example would return everything except for id 40. I initially thought this question would be a good …

SQL: escaping with backslash

I’m writing a lexer for SQL for syntax coloring and have the problem of parsing code like Is my understanding correct, that escapes some characters like underscore (_), but not if it is followed by a single quote (‘)? Does the escaping depends on the SQL dialect or is the behavior the same for al…

Using SQL result as a filter for another query

Here’s my code and there’s a thousand transaction_no result. Which is I have to use as a filter for another code with the same table. Answer You could use in, if you want to filter on the transactions: If you want all rows for transactions that have the specified item, you can also use qualify:

Postgres: Split column values & transpose

I have a table like this: And I want to convert it to this: I have tried splitting the column based on ; but the product_id count varies based on the category_id. Is there anyway I can achieve this? Answer You can use PostgreSQL’s array manipulation functions: string_to_array does exactly what it says —…