I’m working on a query but I’m running into an issue with deltas and getting the latest info. If I have a table as such…. I’d like to get results like: I want the latest delta that has a value per ID. I’m writing this in postgres but running into some issues. Any suggestions? Ans…
Tag: postgresql
Update table A by Joining Table B and Table A
I have table A, Table B My database looks some thing like this Table A key1 key2 key5 key6 Table B key3 key4 Data in A.key2 is same as data in B.key3 and A.key5 has same data as B.key 4 I want to …
PostgreSQL – How to find the row with a record that matches it with a value higher than given value?
Let’s say I have two tables with a 1-to-many relation. Table A (user): id INT, name TEXT Table B (skill): id INT, user_id INT, skill_name TEXT, skill_level INT Each user may have multiple skills. And …
update average/count from another table
I’ve been provided the below schema for this problem and I’m trying to do two things: Update the ACCOUNT table’s average_eval row with the average of the evaluation row from the POST_EVAL table per account_id. Update the ACCOUNT table with a count of the number of posts per account_id, with …
Suitable Index for improving Top N recent queries with tags
I am designing the schema of a database in PostgreSQL which would contain posts like Stack Overflow with tags. Users would search recent posts by different tags. From my research, I found that …
DISTINCT work differntly in MYSQL & POSTGRESQL
I have created a sample table report in SQL and filled sample data in it using the following command. I need to find the list of recently visited(based on date column) branches with out duplication. So I used the following query It works on MYSQL but shows the following error on POSTGRESQL. How to fix this? O…
Need help understanding Postgresql query execution
I have a generated query from an ORM that selects data from a nested join on a junction table. When executing it looks like it performs a full table scan on a table with >55 million records resulting in a slow execution time. My expectation was that it would perform the filtering first then lookup by index…
Counting organizations which user has not contacted
I am new at PostgreSQL. I have the following tables created in my schema: User table: Organization table: And finally Organization_rating table: With this schema, an organization has a business type in which gives support to the user that also have that business type. After giving this support, the user can r…
Find closest match to value in another table
I have a table_a with many rows and columns for each timestamp in PostgreSQL 13. I’m trying to find the row where the value in column X is closest to a benchmark value obtained from another table. This second table has only a single benchmark value for each timestamp. For each timestamp, I need to retur…
Add a new column from compare result
I need add a new column (status) on my main table (Table 1) on my bd Postgres, to do this , I need to compare two columns from table 1 on table 2 , if the two columns exist , I need to copy the status from table 2 to the new column on table 1 , if not exist