For each row in table line there can be many rows in table line_version. I need to find only N (let's say 2) "latest" rows for each line row. Latest here means the largest IDs. For example, that's how line_version…
My table is: create table transactions transaction_id integer not null, transaction_timestamp integer not null, input_index smallint, output_index smallint not null, from_id integer, to_id integer not null, input_value real, output_value real not null, constraint unique_transactions unique (transaction_id, from_id, to_id) ); with…
I have the following query: SELECT DISTINCT FIRST_VALUE(business_id) OVER (PARTITION BY b.sub_category_id ORDER BY AVG(stars) desc, COUNT(*) DESC) business_id, sub_category_id FROM purchase_experience pe JOIN businesses b ON b.id = pe.business_id AND b.status = 'active' AND b.sub_category_id IN (1010 ,1007 ,1034…
This is my message table. Now I am trying to write an query to group records by collection_id (only first instance) if type multi-store else by message.id . id | collection_id | type | affiliation_id | status | scheduled_for_date --------------------------------------+--------------------------------------+----------------+----------------+--------------+--------------------…
I'm learning SQL from a website and they have this question for which my attempt below isn't sufficient and needs input. Summary of question: "Find the difference in days between the last post and the first post of the year…
I am trying to get the category of specific students health inspection for each month. However, sometimes there are multiple entries for a month. How do I get the latest entry for the month using SQL? Below is the SQL…
I want to have a result of unique row of id, date_col1, with max (or last) of date_col2, and the value contained in the same row as max(date_col2). My table is like this sample: id date_col1 date_col2 value 1 2023-01-02…
I have the next records from the table 'data' date_post date_complete 2023-11-28 2023-12-04 2023-11-28 2023-12-04 2023-11-28 2023-12-05 2023-11-28 2023-12-05 2023-11-29 2023-12-06 2023-11-29 2023-12-06 2023-11-29 2023-12-07 2023-11-29 2023-12-07 it's posible to select something like this? date_post date_complete 2023-11-28 2023-12-05 2023-11-28 2023-12-05…
I am working with the ml100k dataset to write a query that can fetch me the most rated movie for every age. Here's how my tables are defined. users id | age | gender | occupation | zipcode ratings userid…
I have a query to get all penalty data with latest prices. In prices table there are many prices related to a product. To take the valid price I need to get the latest price. Here is the query. SELECT…