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…
I need a database with two tabels. I need to JOIN them, group the records and then display top 5 rows from each group. Here is my initial query without top N records: SELECT customerId, itemId, count(itemId) as num FROM…
In a ColdFusion application I have a query with several joins and I need one MAX number from one of the tables. I am getting all tangled up in 'only_full_group_by'. I cannot disable it in etc or in the Administrator,…
I have the following orders table: (1, 2, '2021-03-05', 15, 'books'), (1, 13, '2022-03-07', 3, 'music'), (1, 14, '2022-06-15', 900, 'travel'), (1, 11, '2021-11-17', 25, 'books'), (1, 16, '2022-08-03', 32, 'books'), (2, 4, '2021-04-12', 4, 'music'), (2, 7, '2021-06-29', 9,…
I have two tables, from which I need to select the latest purchase of each customer from the US. The query must present the user_id and the purchased item columns: (I left out some of the records from both tables)…
I would like to add a relative percentage to an SQL output Here is the code: SELECT customer_status.status AS "Status of Customer", COUNT (customer_bio), FROM customer_status JOIN customer_bio ON customer_status.status_id=customer_bio.status_id GROUP BY status Here is the output Status Number Switched…
I have a table like this: Table "*wallet" amount balance timestamp 1000 1000 2023-01-25 21:41:39 -1000 0 2023-01-25 21:41:40 200000 200000 2023-01-25 22:30:10 10000 210000 2023-01-26 08:12:05 5000 215000 2023-01-26 09:10:12 And here is the expected result: (one row per…
I have a table with an item, its cost and the date it was added. CREATE TABLE item_prices ( item_id INT, item_name VARCHAR(30), item_price DECIMAL(12, 2), created_dttm DATETIME ); INSERT INTO item_prices(item_id, item_name, item_price, created_dttm) VALUES (1, 'spoon', 10.20 ,…