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
1answer
47 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` ...
0
votes
2answers
27 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 ...
0
votes
1answer
46 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)) ...
0
votes
2answers
51 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 | ...
0
votes
1answer
39 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 ...
1
vote
2answers
78 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 ...
3
votes
1answer
59 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 ) ...
1
vote
1answer
69 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)...
1
vote
1answer
55 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/...
0
votes
1answer
62 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 ...
3
votes
2answers
78 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 ...
0
votes
1answer
57 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 ...
-1
votes
1answer
34 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 ...
-3
votes
1answer
26 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 ...
0
votes
3answers
54 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 ...
0
votes
1answer
141 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 ...
1
vote
2answers
98 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. ...
0
votes
3answers
62 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....
2
votes
1answer
58 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 ...
-1
votes
2answers
410 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`.`...
2
votes
2answers
65 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....
0
votes
1answer
136 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 | ...
1
vote
2answers
116 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....
1
vote
1answer
19 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 ...
0
votes
1answer
54 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 ...
4
votes
2answers
63 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] | ...
0
votes
0answers
18 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 ...
3
votes
2answers
60 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, ...
1
vote
3answers
98 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 ...
0
votes
2answers
59 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 ...
0
votes
0answers
111 views

Correct way to account for MySQL session variables using JDBC

I am trying out a neat solution for getting top N rows per each group. The solution makes use of two session variables @country_rank and @current_country. The solution works well while directly ...
1
vote
1answer
34 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 ...
-1
votes
1answer
25 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 ...
0
votes
1answer
32 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 ...
1
vote
1answer
50 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:...
0
votes
1answer
38 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 ...
-1
votes
3answers
39 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 ...
1
vote
3answers
124 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 ...
0
votes
1answer
43 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 ...
0
votes
2answers
60 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 --------------------------...
1
vote
2answers
63 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, ...
0
votes
1answer
41 views

Selecting a value from a row where another column is max

I have the following SQL query: SELECT bug.`id`, Max(report.`date`), Count(report.`id`), Max(version.`code`), bug.`id` FROM `bug` LEFT OUTER JOIN `stacktrace` ...
0
votes
1answer
67 views

How do I select row which has a max value in other column - PostgreSQL?

I'm just starting with PostgreSQL and I bet there is a simple answer for my qs. I have a view like: county - candidate - votes Abbeville - John Kasich - 157 Abbeville - Ben Carson - 305 Abbeville - ...
0
votes
1answer
142 views

Get top 5 students for each class in many-to-many relationship [duplicate]

So I'm doing this in MySQL. I have the table students and classes. There is a many-to-many relationship between which I track through the student_class_rel table. Below are the tables and their ...
2
votes
1answer
43 views

Deleting all records on a table except the latest 10 for each user

I have an activities table that has a foreign key reference to a users table. A user can have many activities, and I have a job that will populate their activities on a daily basis. After that job ...
2
votes
2answers
39 views

Count rows for each latest status of tickets

ticket_number | datetime | status ---------------+----------------------------+--------- 1 | 2020-08-03 03:52:58.048196 | replied 1 | 2020-08-02 19:55:49....
0
votes
0answers
53 views

how to create a MySQL View to select the smallest and largest Records?

i have a table contains items units details (st_items_units) like :- ------------------------------------------------------- id ------ stitems_ID ------unit------ stitemsu_UnitNum --- unit price -----...
1
vote
4answers
130 views

Find timestamp for highest number of concurrent users by date

I have a table including these columns: Sample data: concurrent_users concurrent_timestamp concurrent_date 3 2020-06-24 07:20:00 UTC 2020-06-24 7 2020-06-24 ...
1
vote
2answers
50 views

Sum all unique values from previous dates

Let's say, for simplicity sake, I have the following table: id amount p_id date ------------------------------------------------ 1 5 1 2020-01-01T01:00:00 2 10 ...
0
votes
1answer
15 views

Query finds no values when sharppend

I am trying to join two datasets and find the most recent certification date I the following 2 datasets Certifications (1, 'Judge Level 1', 'Judge', 10, 2), (2, 'Judge Level 2', 'Judge', 20, 2), (3, '...

1
2 3 4 5
7