Tag: postgresql

Postres SQL select with GROUP BY and COUNT

aggregate-functions count postgresql select sql

i have this SQL select SELECT t.debtor_account, COUNT(t.debtor_account) FROM transaction t GROUP BY t.debtor_account HAVING COUNT(t.debtor_account) > 2; which work, but i need select all from …

How to exclude rows with similar columns?

datetime greatest-n-per-group postgresql sql

There is a table: | date | action | issue_id | | 2020-12-14 | close | 1 | | 2020-12-15 | close | 1 | | 2020-12-16 | close | 1 | | 2020-12-14 | close | 2 | …

Handling of generate_series() in queries with date or timestamp with / without time zone

date generate-series postgresql sql timezone

I have a query to generate a report based on a date series that is grouped by date and employee_id. The date should be based on a particular time zone, in this case ‘Asia/Kuala_Lumpur’. But this can change depending on where the user’s time zone is. Definition and sample data for table atten…

Obtaining summary result since last occurence of specifc hour of day in postgresql

postgresql sql

I have rainfall measurements stored in a postgresql table and wish to select the min and and max value since the last occurence of 9am, whether that is 9am of the current day, if after 9am, or 9am of …

Add information from two tables to second table

inner-join postgresql sql

Select max date per year

max postgresql select sql subquery

I have the table as follows: And I’d like to select the max date for every year per user, so the result would be like: Some help? Thank you! Answer Just use aggregation: If you have more columns that you want, then use distinct on:

How to sort and group data in request?

datetime greatest-n-per-group postgresql sql subquery

I have got table. It have some duplicates in purchaseNumber. I need select from this table only newest records for processing. In other words I need to: group elements by purchaseNumber sort them by docPublishDate and select newest. if any of elements in group is have status parsing_status true (suppose it wa…

Postgres comparing strings from json and table

json postgresql sql stored-functions where-clause

I have the following postgres stored function: CREATE OR REPLACE FUNCTION wg.get_user( req jsonb) RETURNS jsonb LANGUAGE ‘plpgsql’ COST 100 VOLATILE AS $BODY$ DECLARE …

Upsert query is not updating the the records in postgres

postgresql postgresql-9.5 sql

I am trying to Upsert into the target table from temp table but My upsert query is not updating the records in postgresql. Although it is inserting but not updating. Please find below query: Insert …

Rails: How to sort models by an associated model’s field with condition?

activerecord postgresql ruby-on-rails ruby-on-rails-6 sql

I have three models: This is what I want: Feedback model has a result field. If result is true or false, the feedback is closed. If result is nil, the feedback is unclosed. I want to sort from the unit with the highest number of unclosed feedbacks to the unit with the lowest. It should be at the bottom of