I got table t1 and i want to join it with table t2 below on columns a, b and c +———+———+———+ |a |b |c | +———+———+———+ |473200 |1 |1.-1-…
Tag: greatest-n-per-group
SQL group by a field and only return one joined row for each grouping
Table data What I want to do is group by the name, then for each group choose one of the rows with the earliest required by date. For this data set, I would like to end up with either rows 1 and 4, or rows 2 and 4. Expected result: OR I have something that returns 1,2 and 4 but
Mysql how to select only row in same table and same identifier but different value
I have this dataset : UserPlans table : how to only select this row (with ID 3) since this data has been deleted but doesn’t have any data with deletedAt = NULL with same userId ? Answer You seem to want rows where userid has no other row whose deletedAt is null. If so, you can use not exists and
Fetch multi-participant conversations with last message for each
I am trying to create a simple chat application database schema, and query the conversations. My current table setup is the following: So basically each conversation has its own title, and multiple participants. What I would like to fetch my conversations sorted by the date of the latest message in the conver…
How do I return only the most recent record on a date field split into two
Scenario: Person A takes test B three times in the span of two year. There will be three entries for that person. However, I need to write a query that tells me the number of Persons that have taken a test(just one, the latest test). The problem with this is I have a column labeled, Test_Month (xx) and Test_y…
Suggest SQL query for given use case
Original Table Expected Table I want all the distinct ids who have status as F but time should be maximum, if for any id status is T for given maximum time then that id should not be picked. Also only those ids should be picked who have at-least one T. For e.g 4 will not be picked at it doesn’t
SQL nested query and use of MAX to extract most recent transaction and/or comment
We have a SQL database table recording customer comments (ARCMM). I want to extract the most recent comment for each customer. Some customers do not have any comments (i.e. no entries in ARCMM). The most recent comment for a customer will have the most recent date (field DATEENTR) and, for that date, the high…
Remove duplicate values by taking latest data load
I’m working with enterprise data that looks like this. The issue is that the company has bad data practices and changes/reuses IDs, but only updates the load_number field. How do I construct my sql query to pull the latest loaded data like so: Basically every field will match except the id and the load_…
How to find maximum and value of column with the same column in postgres?
I want to find maximum value of the has_sold column in an specific province.For example in all rows with province = Tehran which row has the most has_sold value? This is my table: I had tried this SELECT DISTINCT city, max(has_sold) FROM fp_stores_data_test GROUP BY city, but I’m not sure it’s rig…
How to SELECT two records for each unique column value as one row in MySQL?
I have a MySQL table like this : +—-+—–+——-+——+——+——-+———————+ | ID | GID | Name | p1 | p10 | p100 | createdAt | +—-+—–+——-+——+——+—-…