My base query: It can get me these outputs: Output example: Obs: There is a constraint on (id, on_date) meaning there can always be only one record of a project production on a specific date. Duplicate records, that have the same id, and exist in both dates and have different production values (wanted) Single records, that exists on only one
Tag: sql
How can I prioritize the maximum of multiple columns in SQLite
I have a little issue with my SQLite query which I use in python. In my little test_table I got 4 columns: sys_id, aq, vq, off_id. If I search for example for off_id=7477310 I get results: But I would also like a direct comparsion by this priority (vq,aq,sys_id) so I tought I use max. So the goal is if one
Postgres order entries by id, self reference id and date, after every parent, list childs
I have the table orders with the following fields id, updated_at, self_ref_id, and others which doesn’t count. The column self_ref_id is a self reference two an order, one order can have multiple children, a child will not have other children. I am trying to order all entries by updated_at desc but after every parent order, I want to have his
Sort by NULL field only if field is NULL, then sort by name in Postgresql
I have the following sort in Postgresql: What I want is having all records with verified_at NULLS first, but only if the field verified_at is not null, then order by name. I don’t want the verified_at to have the priority on name if verified_at IS NOT NULL. With this code, verified_at has the priority on name, after the NULLs are
PHP/SQL Find duplicate rows in a column, with the condition that another column is different
I need an SQL query to do the following task: I have two columns. Column ‘foo’ and column ‘bar’. The query needs to return results if and only if column ‘foo’ has different values but ‘bar’ has the same values. For example: If I was to run the query on the above dataset, then both rows indicated withe arrows above
trying to select random data PostgreSQL
I’m trying to select random strings. The problem is it returns the same value for each row. Why is that and how to fix? 10 rows Answer Postgres overoptimizes the subquery. I think this is an error, because it is missing the fact that random() is volatile. A simple fix is a correlation clause: I rewrote the logic a bit,
How to update multiple records in Codeigniter
In above code snippet first function is declared in controller and second function is declared in model. I want to update owner for multiple rows having ids in $astrep. Can anyone please help me? Answer You can use whereIn() or orWhereIn() methods from query builder class. Refer https://codeigniter4.github.io/userguide/database/query_builder.html#looking-for-specific-data
Create a stored procedure such that rows with same key are converted into a column
You are given the table tracks, which contains the following columns: received_at: the unique timestamp of the action event_name: the name of the action that was performed at this time anonymous_id: the anonymous ID of a user user_id: the user ID, which can be null This is the mentioned DDL for the problem. Task is to find two events for
Case when date = current_date then text
I have a table like this: But when I execute this query: I get this result: SQL Fiddle I would expect that in the rows where is_equal is true, the result should be TODAY. What am I doing wrong? Answer Nevermind, I solved it, but I don’t want to discard the question. The solution is to CAST(token_date as varchar) in
How to make separately unique each column in postgresql?
I know we can apply primary key to a column to provide uniqueness for a row and we can apply multiple primary keys and get a composite key. But this didn’t work for my case. I have userID and email columns. And I want them to be unique at the same time. When I applied primary key attribute to both