Questions tagged [greatest-n-per-group]

Use this for problems that involve returning one or more rows from each group in a data set. Despite the name, this tag is suitable for *minimum-* and *maximum-* per group queries.

Filter by
Sorted by
Tagged with
0 votes
1 answer
27 views

counting minimum sent_time for everyday

Postgres 14 db fiddle updated - Link EDIT: It seems like I need 1 more row: CREATED AT - specifies when conversation was created. SELECT DISTINCT ON (date_trunc('day', sent_time), conv_id, user_id) ...
user avatar
-1 votes
1 answer
39 views

Getting first messages only for every day in a specific date range

Table messages: conv_id user_id content sent_time 1 001 1st_msg 01-01-1990 00:00:00 2 002 2nd_msg 02-01-1990 00:00:00 How do we select the first message and the first reply sent in a conversation (...
user avatar
2 votes
2 answers
60 views

How to remove duplicates and sort in a subquery?

I have records with a "title" column that I am splitting up by space and performing a full text search with each word. I am storing the result in a materialized view. This works, but I get ...
user avatar
  • 123
2 votes
1 answer
38 views

Previous Row based on double where condition and group max sum

I have such a table and I would like to get these values: +───────────────────+────────+──────────+──────────────────────────────────────────────────────+ | TS | Value | ValueA | ...
user avatar
  • 23
1 vote
1 answer
47 views

Query hierarchical file permissions

I have a table representing some files with their paths and another table with file sharings by user and files. Below is Table files, simplified. Example: I have a directory A (path /A) containing ...
user avatar
  • 13
0 votes
1 answer
39 views

Find the youngest customer grouped by province

SELECT a.province, c.birth_date, c.name FROM customer c JOIN address a ON (c.cust_id = a.cust_id) GROUP BY a.province ORDER BY birth_date DESC; I want to find the youngest customer in each ...
user avatar
2 votes
1 answer
82 views

Backfilling data using generate time series

I have a table like this: Date Group name value 2022-01-01 A 1.0 2022-01-15 A 0.5 2022-01-31 A 0.2 but this contains only 3 days. I need a table with full-calendar dates, a row for every day of ...
user avatar
  • 73
0 votes
1 answer
24 views

interpreting an explain statement for a slow query on MySQL 5.7.36

I have a question about some performance problems we are having that are a bit beyond me. When we do this query on about 2M reviews, it takes about 20 seconds but seems like it should be hitting ...
user avatar
  • 1
0 votes
2 answers
42 views

Add Foreign Key to the Table as a column

I have two tables as below: Table name: table_1 ID studentId Date enrolled parent name parents annual income 1 12345 2022/01/13 14:39:15 enrolled ABC 12345 2 12345 2022/01/13 14:39:06 not-enrolled ...
user avatar
0 votes
0 answers
23 views

Get top users with user type by amount

i have tried to find top retailer and top distributor with below query. SELECT (SELECT SUM(amount) AS total_amount FROM transactions tm LEFT JOIN user_master um ON um.p_id = tm.user_id ...
user avatar
  • 101
0 votes
1 answer
84 views

Pick specific column value in a group of rows based on condition

I am working with mariadb server version 10.6.4. This is my table definition: CREATE TABLE `tmp_dba` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `case_id` bigint(20) DEFAULT NULL, `client_id` ...
user avatar
0 votes
2 answers
153 views

Merge duplicates on condition

I have the following table in a PostgreSQL db id | value_1 | value_2 ----------------------------- 1 | 100 | 10 1 | NULL | 20 2 | 100 | 20 3 | 100 | 12 4 | 100 | 16 5 | NULL ...
user avatar
  • 111
0 votes
1 answer
439 views

ORDER BY datetime makes the query very slow

I have Visitor Management System - time spent at work by specyfic user. When I add ORDER BY a added_on it return the results after at least 18 seconds (even though there is pagination (32 per page)) ...
user avatar
0 votes
2 answers
115 views

How to get the second highest value per group?

Getting the second highest value from a table has been solved many times, but I'm looking for the second highest value in each group. Given this table: +----+-----+ | A | 10 | | A | 20 | | A | ...
user avatar
  • 475
0 votes
1 answer
50 views

How to get the maxium row value for a similar values

In my table below, I have id and job_id fields with some of the job_id fields being duplicates. I want to get the MAXIMUM value of the id field corresponding to each unique job_id. I have now tried ...
user avatar
1 vote
2 answers
228 views

Delete old rows from a big table, keeping one per given time interval

I am trying to delete some rows from a table in which I have saved timestamps only (data type timestamptz): The usual operation to delete from DB/table is very simple such as I want to delete all ...
user avatar
3 votes
1 answer
77 views

How do I aggregate related rows when there's no group key?

I'm using MariaDB 10.6 and have a table: CREATE TABLE velocities ( `state` int(8) NOT NULL, `timestamp` datetime NOT NULL, `velocity` decimal(5,2) NOT NULL, `name` varchar(15) NOT NULL ) ...
user avatar
  • 133
1 vote
1 answer
592 views

SELECT rows from table one by one, comparing one column values with values in an array

I have an array of timestamps in a PLPGSQL code block: timestamp_array. For each element of timestamp_array I want to get the element itself (timestamp_element) and some columns from a table (my_table)...
user avatar
  • 181
1 vote
1 answer
90 views

Group by latest date with conditions

I need your help! Let's say I have this table: Instance Date MetricID Value Marc 09/14/21 1 5 Marc 09/14/21 2 2 Marc 09/14/21 3 1 John 09/14/21 1 10 John 09/14/21 2 1 John 09/14/21 3 1 Marc 09/...
user avatar
0 votes
1 answer
111 views

SELECTing n rows for each GROUP with WHERE condition

I SELECT n rows for each ArticleID from TagMap table SELECT a.ArticleID,a.TagID FROM TagMap a LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID GROUP BY a.ArticleID,a.TagID ...
user avatar
  • 4,169
3 votes
2 answers
128 views

Apply nth occurrence number on each distinct value in a column

I have a table with a column of values where each value occurs a variable number of times (i.e., one value may occur 1 time, and another value may occur 3 times). I need to add a column that ...
user avatar
  • 133
0 votes
1 answer
414 views

Joining only one row of a table?

I have this query which is supposed to find the latest uploaded pids and reps of a user. The query looks like this: SELECT usr.id, up.*, (SELECT COALESCE(state, 'processing') AS pidState FROM ...
user avatar
-1 votes
1 answer
69 views

Multiple columns in one single row mysql [closed]

My returned query looks like this Name Type 1 Type 2 Type 3 John 1 John 3 John 5 Ronn 11 Ronn 9 Ronn 7 but I want them to be in a single row that looks like this, what functions should I ...
user avatar
  • 1
-3 votes
1 answer
252 views

How to get the top record from each set of group [duplicate]

This question is for Oracle SQL DEV What I am trying to accomplish is to pull the top record for each person (last name + First name + Dob), using their most recent travel date. To help illustrate my ...
user avatar
0 votes
3 answers
99 views

Get the most recent 2 entries for each group based on timestamp

I am using MySQL 5.7 database with the following columns item_symbol source_date price I have composite primary keys which are a combination of item_symbol and source_date. I have around 100k ...
user avatar
0 votes
1 answer
608 views

Selecting all columns from row with specific max value [duplicate]

Imagine that I have the following table: ItemCode WhsCode MinStock MaxStock AvgPrice 123 01 1 4 600 123 02 0 1 200 512 01 0 2 100 489 01 0 6 50 489 02 2 7 72 489 03 5 10 80 489 04 1 4 65 258 ...
user avatar
1 vote
2 answers
117 views

What did I miss in EXPLAIN that hinted at server crippling loads?

I reviewed EXPLAIN ANALYZE output for a query and found a couple subqueries were scanning the whole table. This is done to get only the most recent record in those tables related to an appointment. ...
user avatar
  • 133
0 votes
3 answers
64 views

Find Max() updated time for a group of users

Database is MariaDB 10.3.25 I have 2 columns that are relevant: UserID and LastUpdate UserID consists of user@domain LastUpdate is a date field. Here is my issue - I have the current query: select a....
user avatar
2 votes
1 answer
60 views

How to write a query to get wine pairing for a pizza based on pairing weight

I am working on a query to pair drinks to pizzas. Currently I am able to show the pizza_name, beverage and the pairing_weight being the amount of toppings on a pizza that the beverage pairs with. From ...
user avatar
-1 votes
2 answers
781 views

MYSQL: Query the last conversion on chat messages and sort by latest message

I am having issue on query sorting of last convo messages of each users. Here is my current record on my 2 tables: I want to achieve below result: My current query is: SELECT `messages`.*, `users`.`...
user avatar
2 votes
2 answers
93 views

Select maximum of a count in a grouped clause

I have the following tables: Vehicles(v͟i͟n͟, model,category) Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date) vehicleSold(saleID,v͟i͟n͟,salePrice) When I join these tables using: select YEAR(Sales....
user avatar
0 votes
1 answer
194 views

SELECT max value for each group of values order by grouped by MYSQL 8.x

I need to get the max age of a group of records, even is the age is duplicate ORDER BY the age DESC Person | Group | Age --- Bob | 1 | 32 Jill | 1 | 34 Shawn| 1 | 42 Jake | 2 | ...
user avatar
1 vote
2 answers
188 views

GROUP BY, but use only one row per user

I've spent several hours to make a "simple" SELECT with GROUP BY in Postgres without success. The GROUP BY clause is giving me problems. I have the table cities with columns user_id and city....
user avatar
  • 113
1 vote
1 answer
21 views

Variation on greatest-n-per-group where whole table is needed with indicator, not just greatest rows per group

I have a query that returns some rows having a grouping on a column and dates. I would like to have that query indicate in a column which of each group has the greatest date. There are a vast number ...
user avatar
0 votes
1 answer
63 views

Capture row with highest value from each percentile

So far I've tried doing this: SELECT MAX(buckets.position_id), NTILE FROM ( SELECT position.position_id,NTILE(100) OVER (ORDER BY position.position_id) FROM position WHERE activity_id = 8 ...
user avatar
4 votes
2 answers
92 views

Unnest ids from arrays based on priority with no duplication

Here is the sample schema for my database: Priority | Productive | UnProductive | Neutral | ------------------------------------------------- High | [1, 2] | [] | [4, 5] | ...
user avatar
  • 209
0 votes
0 answers
20 views

How to query with maximum number of rows with same value?

In Mariadb 10.5.8 I have a table with some repeating value e.g. Item , qty shoes 3 pants 2 pants 2 pants 4 shoes 3 pants 2 shoes 6 pants 2 shoes 3 pants 2 I want to query up to 3 occurrences of each ...
user avatar
  • 207
3 votes
2 answers
79 views

Select rows with the highest value for one ID, after grouping by two IDs

It is given in the problem that occurrences has to happen more than once, but the person (PID) should only be selected with the (agent) AID having the highest BustCount. SELECT I.AgentID, I.PersonID, ...
user avatar
1 vote
3 answers
284 views

Get top two rows per group efficiently

Currently, I use a query with DISTINCT ON to get a list of recent posts for each author. But how can I get previous recent posts for each author? In other words, how to make DISTINCT ON return second ...
user avatar
  • 149
0 votes
2 answers
165 views

Postgresql get rows based on minimum value

Having this table: warehouse_id destination_id days 1 1 2 1 1 3 2 1 3 2 1 4 3 1 5 3 2 5 1 2 2 2 2 3 I'd like to get the warehouse_id and days for each value of warehouse_id, where the row ...
user avatar
  • 105
1 vote
1 answer
35 views

Latest value of each GROUP with INNER JOIN

I'm using sensors to get temperature and humidity every X minutes. I've one table for sensors and one table for their results. tb_sensors: tb_results: I want to get the latest measurement for every ...
user avatar
-1 votes
1 answer
47 views

SELECT MIN(column) LIMIT to the top 5 lowest values [closed]

I need to get the top 5 MIN(column) entries from a table 'bids'. table bids: I tried using SELECT * FROM bids ORDER BY bid ASC LIMIT 5; this is the result: but the problem is that if 'bid' wont ...
user avatar
0 votes
1 answer
33 views

Getting only 4 rows per idea from ideas table [closed]

i have a table of ideas that stores responses from evaluators and stores as one row per evalautor For ex : Evalautor 1 evaluated idea A then in table record gets added as IDEA A, COMMENTS , EVALUATOR ...
user avatar
1 vote
1 answer
72 views

MySql Set Column on First Appearance of Value

I have a select that returns something that looks like the following: PeopleId Timestamp FavoriteNumber 1 2020-11-04 17:40:33 14 2 2020-12-06 21:19:41 59 3 2020-12-10 18:46:11 14 4 2020-12-15 00:...
user avatar
  • 113
0 votes
1 answer
41 views

Using multiple GROUP BYs to get fewer records, not more

I feel like this is a simple thing to do, but I've been playing around with trying to achieve the desired result and the only way I'm coming up is with 2 nested subqueries, which doesn't seem great ...
user avatar
-1 votes
3 answers
43 views

Displaying the id of the customer with the greatest amount of purchased products

I have this db https://pastebin.com/WWRxApWn I did this --SELECT a.id --FROM ( SELECT customer.id,customer.firstname,customer.lastname,SUM(quantity) as sum FROM ( orders INNER JOIN ...
user avatar
1 vote
3 answers
219 views

SQL only the first row of each value

MYSQL version 5.7.31 How do I make it show the distinct rows of the value/column mainthreadid with the highest value of date? Note that the data is filtered using WHERE approved = 1 AND section != 150 ...
user avatar
0 votes
1 answer
46 views

Rewriting query to move subquery out of FROM clause

Suppose I have an entries table that looks something like this: | id | category | text | user_id | |----|----------|------|---------| | 1 | A | z | 22 | | 2 | B | y | 23 ...
user avatar
0 votes
2 answers
150 views

Limiting join to top 1 row for each row

I'm trying to join two tables and filter join results but I couldn't manage it and I'd appreciate any help. I have these row sets: Table A id | userid | targetid | start_date --------------------------...
user avatar
  • 101
1 vote
2 answers
69 views

Find the greatest difference between each unique record with different timestamps

I have a PostgreSQL table with the following schema and data: CREATE TABLE IF NOT EXISTS T( id uuid PRIMARY KEY, username varchar(15), person varchar(10), tweets int, ...
user avatar

1
2 3 4 5
7