In my latest project I have been migrating my database which was using SQLite to a PostgreSQL database. I have been updating my SQL code to work with PostgreSQL, but have been struggling with one difference between SQLite and PostgreSQL,…
I want to find the best way to perform a server-side query to find JSON documents by some initial query criteria, then grouping all these documents by some field in the document and then reducing each group to only contain…
I have a result variable composed of 3 lists : var result = { type: [a,a,b,c,d,a,a,b], program : [P1, P1, P2, P1, P2, P2, P2, P1], reference : [ref1,ref2,ref3,...,ref8]} I want to group my reference by type and program to…
I understand that this query will produce numbers and cumulative tallies: SELECT number , SUM(number) OVER (ORDER BY number) cumulative_number FROM (SELECT 1 number UNION SELECT 2 UNION SELECT 3) inside GROUP BY number Result: number cumulative_number 1 1 2…
I am trying to find all the rows in my table, which a certain column is duplicated in them. and identify them by the unique id column that each row has. I want to delete them and leave only one…
I'm trying to group rows when it starts with 1 and has consecutive 0s, and stop grouping once it meets another 1 or 2. Here is an example: (column 'group' is what I expect to see) +-----+------+-------+ | seq |…
I have a MySQL database table that looks like below: +----------+------------+---------------------+ | name | event | created | +----------+------------+---------------------+ | Player1 | Logged in | 2023-02-14 10:05:00 | | Player2 | Logged in | 2023-02-14 10:05:30 | | Player3 |…
In a one-to-many relationship, users have many contracts. However, I only want the most recent contract for a specific set of users. My query to get all contracts for users look like this: SELECT userid FROM contract WHERE userid IN…
Context : I'm trying to build an architecture displaying POIs which can be at different know locations over time. I have 2 collections, pois { _id: ObjectId, name: string } locations _id: ObjectId, point: { type: 'Point', coordinates: Array<number> },…
As the subject states, I have a table with multiple rows per user and I need to get a count of all rows for each user with the data from the row with the oldest date AND then insert one…