I followed this post Order by using month name in PostgreSQL but not success! I have a query (its working) and I just need to order the results by mont name. This is thr query I am using: I am trying: If i use order by mes_2021 Answer One trick is to use a window function on the date: This,
Tag: postgresql
Check if a chat between 2 people exists
I have a chat system that handles group chats. A conversation can have many participants and the only distinction between group and non group chats is a non group chat will only have 2 participants. I want a query to check if a non group chat exists between 2 users. Conversations table Participants table To c…
Postgresql: How do I use dynamic values when searching jsonb array of objects?
I am currently trying to build a query for finding specfic object within a jsonb array. I have the following query which works fine if I used a hard coded string for the “game” value e.g. However, if I use a dynamic value like I currently do for username, I get invalid json syntax error. e.g. How …
Reading and Writing UTC to TIMESTAMP in Postgresql
I have a Java application that inserts data into a database using prepared statements. In the preparedStamement date is set in UTC format. I want to be sure that when read and write operations execute on the table, the response should ALWAYS be in UTC format. At the below query, when the data is read it will …
how can I can running status from my table?
I have a below table which has multiple rows with same executionid and different status. How can I get the row which status is running, rows will be exclude if executionid associated with both running and completed status? Below image is the sample data : Expected result should be: Answer Using String_AGG() t…
Optimising filtering multiple SQL count sub queries with SQLAlchemy
I’m trying to convert raw postgres-SQL into sqlalchemy which uses a count and filter This works great and is very fast (sub 100ms – even when filtering 20 fields) I’ve written the sqlalchemy as (simplified) This produces the following SQL This works but is quite slow (+1 second). I’m n…
How to remove and carry forward month prior when running balance decreases?
I have the following data: client_id balance_month balance 100000000 September 1,2021 12:00 AM $7000 100000000 August 1,2021 12:00 AM $7000 100000000 July 1,2021 12:00 AM $6000 100000000 June 1,2021 12:00 AM $3000 100000000 May 1,2021 12:00 AM $20000 100000000 April 1,2021 12:00 AM $1000 100000000 March 1,202…
SQL query to select all tuples that do not have a specific attribute at all
I have a table (shown below) that maps users to the fruits that they like. This table is a result of a a complicated SELECT statement, and now I am left with specifying the WHERE condition. I would like to select all users that do not like the fruit “Orange” at all. U.id F.fruit 1 Apple 1 Orange 1…
How to make revenue $0 for months prior to decrease in SQL?
I have the following data: client_id balance_month balance 100000000 September 1,2021 12:00 AM $400 100000000 August 1,2021 12:00 AM $100 100000000 July 1,2021 12:00 AM $200 100000000 June 1,2021 12:00 AM $300 200000000 September 1,2021 12:00 AM $99 200000000 August 1,2021 12:00 AM $100 200000000 July 1,2021 …
SQL how can i detect if a value decrease over time?
Hi, how can i check who are the employees whose salary has fallen ? : I only want to fetch the name of employees whose salary has fallen Answer You can use the positional analytic function LAG() to find these rows. This is a standard SQL function that peeks at a previous row, according to a specific criteria.…