Here is my query. UserName column is citext, having non-unique index on it. var logs = Db.Logs .GroupBy(x => x.UserName) .ToDictionary(g => g.Key, g => g.Max(m => m….
Tag: sql
MariaDB/MySQL UPDATE statement with multiple joins including a ranged join
I have for tables A Login History An IP To Location Table An Account Table Some Orders All tables have fitting indices for this problem. My goal is to fill the country code of the orders with the country from the ip2location table. I have a login history and because I want to make the problem not more complic…
H2 vs PostgreSQL generated column with function
I’m trying to setup a generated column which will also take null checks into consideration when subtracting values. In PostgreSQL I did: ALTER TABLE session ADD COLUMN duration INTERVAL GENERATED …
Many conditions depends on IF clause within WHERE clause
How can I implement a WHERE clause that depends on one @value condition, like that pseudocode below: Answer As you’ve seen you can’t use an if like that, but you can create the desired behavior using the and and or logical operators:
How to select where there isn’t an entry in pivot table relating to X id
In my database I have a table of notifications types that looks something like: A table for events: And a pivot table event_called_notifications to keep track of what type of notification have been called already for each event: I would like to select all the events that don’t haven’t have a parti…
SQL get ids with minim 3 common values
Can someone help me to get all ids with the same color from this table? Thank you Should return 1, 3, 9 Answer You can try next query: Check it on SQLize.online
UNION ALL query using WITH statement breaks
I wrote a query using with statement and union all. But, I am not used to writing complex queries. It keeps breaking in the union all. Please point me where it is wrong? WITH t1 (source, target) AS ( …
Incorrect syntax near the keyword ‘INNER’ in sql update INNER JOIN
I tried to update some records by joining two tables. but It gives me some syntax errors. Please help me to fix this. Answer The correct syntax in SQL Server uses FROM: Note that I’ve also introduced table aliases so the query is easier to write and to read.
Group data by foreign key and date with total by date
I need help to select daily payments made and group by the organization and date. Group by date, then the total number of payments and the sum total amount of payments for each day Tables are as …
How to get the average from computed columns in Postgresql?
I have a query: SELECT sum(column_1), sum(…) as sum_1, sum(…) as sum_2, sum(…) as sum_3 FROM table_1 How to get an average data from sum_1, sum_2, sum_3? If I write a query in the next …