Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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
0 answers
31 views

MySQL 8 window functions optimization with multiple greatest-n-per-group

I'm struggling to make efficient this sort of query: with a as ( select -- the battery cycle cycles_bat_1, -- interested on a single result row per cycle, so using ...
Michele Mariotti's user avatar
-1 votes
0 answers
18 views

mysql quer to get ten parent comments and child replies for each parent comments

here is my table i want to get parent comments with ten child replies for each parent comment Comment_id Comment_details Parent_comment_id Post_id 1 Aaaaaaaaa 0 1 2 Bbbbbbbb 0 1 3 Ccccccccccc 0 1 ...
justmasif's user avatar
1 vote
1 answer
31 views

How to get last N rows of each group from group by in MySQL

I have a table with isin (test), date (Date), and amount (int). I am trying to get the amount of the last 2 dates per isin. Someone can tell me the trick please?
Nicolas Rey's user avatar
1 vote
3 answers
49 views

Last cell value in a grouped by query

I have a table related to game results with more than 300 000 records, where each row keeps player attack. This is simplified table example. I keep an attack id, date, when attack was done, unique ...
anton's user avatar
  • 117
0 votes
1 answer
105 views

Why doesn't Postgres apply limit on groups when retrieving N results per group?

I've come across many questions/answers for Greatest/Top N per group type problems that explain how to solve the problem - generally some variation of row_number() or CROSS JOIN LATERAL, but I'm ...
Seanvm's user avatar
  • 101
1 vote
1 answer
21 views

Identify sequences of events starting at well-known start locations, end locations not predetermined, and WITH MYSQL 5.x

I'm working within the limitations of MySQL 5.x. Several security guards are responsible to walk around a property and visit checkpoints. Two specific gates permit entry and each guard must begin a &...
Newbuntudude's user avatar
0 votes
0 answers
127 views

Moving from MySQL to MariaDB, slower performance with some query's

recenty we've have moved all of our server to new hardware and to MariaDB . I've installed a new server with VirtualMin for managing all of our company's applications. at a first glance most of the ...
Stefx's user avatar
  • 9
0 votes
2 answers
71 views

How do I optimize this query that gets the newest entry of each group?

I have a query, that finds the newest item of each group based on the ID returned by a subquery. The problem is, that the table has a bit over 5000 entries and even with only this amount of entries ...
nyoatype's user avatar
2 votes
3 answers
598 views

Conditional selection in a GROUP BY clause

I am an SQL beginner and ran into the following problem. Consider a table with data on countries, cities within that country and their population: CREATE TABLE cities ( country VARCHAR(20), ...
dzejkob's user avatar
  • 121
0 votes
1 answer
89 views

The most recent rows in for each type/agent

I have a table (still on MySQL 5.7) with lots of data. This table is written by some scripts. It works like a "checkin", where each agent is checking in in some period of time. It looks like ...
Marek's user avatar
  • 13
-2 votes
1 answer
146 views

Oracle SQL - Selecting First Row for each ID

I am using Oracle SQL Developer and I am trying to pick the first entry of the student population. So, that I have one row for each ID. ID DEGREE AWARDED ACADEMIC PERIOD 147 GR MS 2022 147 UG BS ...
DataAnalyst's user avatar
1 vote
1 answer
42 views

Finding the latest status

I have 2 tables events and event_status. The event_status table is the history table, and latest being the current one. The events and event_status have a one-to-many mapping. I need a optimized query ...
Anwesh Budhathoki's user avatar
0 votes
1 answer
113 views

How do I query out the largest value using an index

I have a table with several terabytes of event data in a very simple (id, bucket_id, data, created_at) schema and there is an index like so create index index_events_on_created_at_and_bucket_id on ...
Segfault's user avatar
  • 157
0 votes
1 answer
2k views

Optimal way to get a total count of rows in a paged query in Postgres?

I need to improve the performance of a paged query for customer orders in a Type2 Postgres db (always insert a new record with a new ID, the newest ID is the current version of the record). Changing ...
jcollum's user avatar
  • 207
0 votes
1 answer
503 views

How can I speed up my query that involves `max(timestamp)` in a huge table? I already added an index to every field

I have a huge table that has fields ip, mac, and timestamp. Neither of the three fields is unique, but the combination of all three is. Table is automatically populated, with newer records added all ...
Granny Aching's user avatar
1 vote
2 answers
80 views

Fetch latest of 2 fields(columns) from multiple records by most efficient way

I am trying to fetch the latest email and contact from multiple records. Each record has a date of creation. This row could or could not have an email/contact. The table looks like this : +------------...
Akash Varde's user avatar
0 votes
1 answer
149 views

Slow COUNT DISTINCT on a large table in PostgreSQL

In PostgreSQL 14, I have a table with around 10 million of rows with this structure: CREATE TABLE search_stats ( id bigserial NOT NULL, search_date timestamp NOT NULL, user_id varchar NULL,...
drew458's user avatar
0 votes
1 answer
1k views

Left Join with only one of multiple matches in the right table

I have a database, where every entry may have multiple names, therefore I made a name database. While all names will be displayed otherwhere I need a list of all main entries with one representative ...
Benito's user avatar
  • 1
-1 votes
1 answer
29 views

sql - how do I pick up all services that are open and latest of those ended - complicated

I am having issue capturing all rows that I need. A client can have number of services including same service again if that is an additional help, all services ended will have an end date and all open ...
Annie's user avatar
  • 1
4 votes
2 answers
2k views

PostgreSQL equivalent to Oracle's ANY_VALUE(...) KEEP (DENSE_RANK FIRST/LAST ORDER BY ...)

There's a technique in Oracle SQL that can be used to simplify aggregation queries: Aggregate on a particular column, but get information from a different column, using a simple calculated column in ...
User1974's user avatar
  • 1,467
1 vote
2 answers
39 views

Combining Queries into 1 Query

I have 3 queries... Query 1 Gives the Opening Balance at the Start of the Month SELECT AccountBalance As 'OpenBalance', MONTHNAME(DateTime) as 'Month', MIN(DateTime) as 'Open' FROM Table1 GROUP BY ...
Paulmcf1987's user avatar
0 votes
2 answers
95 views

Selecting last value to be entered each month

I am looking to pull a closing balance from the database for each month. I have tried SELECT CloseBal As 'Balance', MONTHNAME(DateTime) as 'Month', DateTime FROM Table1 WHERE MAX(DateTime) Group By ...
Paulmcf1987's user avatar
1 vote
1 answer
57 views

Getting ID when sorting by joined table using group also

I have small query to getting products based on variants (colors, materials), like: SELECT ANY_VALUE(id) AS id, parent_id, COUNT(id), color, material, MIN(price) AS priceMin FROM ( ...
kicaj's user avatar
  • 123
2 votes
1 answer
38 views

How to get the most recent result sets when grouping key and date are in separate tables

Good evening! I'm having some difficulty. I'm trying to obtain the most recent row from a few tables using SQL. I can do this using a loop in code, but it requires a lot of round trips to the database ...
jeremysawesome's user avatar
2 votes
1 answer
58 views

How to retrieve grouped records with specified last record columns

id user_id category subcategory 1 243 credit purchase 2 243 credit renewal 3 243 credit purchase 4 243 credit renewal 5 243 revenue recognition 6 245 credit purchase 7 245 credit renewal 8 248 ...
Julius Gitonga's user avatar
1 vote
1 answer
90 views

Return each product with the earliest instance of its latest change

I'm trying to find the earliest created_at date of the latest category for all products. I've attached a sample table below: CREATE TABLE IF NOT EXISTS `products` ( `id` int(6) UNSIGNED NOT NULL, `...
Sophie Barnes's user avatar
5 votes
3 answers
569 views

Can Postgres scan indexes backwards?

We use an Amazon RDS instance with PostgreSQL 11.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit I have a simple classic top-1-per-group query. I need to ...
Vladimir Baranov's user avatar
1 vote
0 answers
48 views

How do I show only the value of the first orderdetail in MySQL

MYSQL version 5.7.38-google-log I have orders and orderdetails table. Follow this https://dbfiddle.uk/bdosg8aa SAMPLE DATA CREATE TABLE orders ( orderID int, orderAmount int ); INSERT INTO orders(...
Lee Harry's user avatar
0 votes
0 answers
67 views

selecting the date to be used in calculating percentage change

I have a table named stock_info with the following columns: dt, stock, close_price. I would like to make a query that would calculate the rolling three month price change for each stock. The previous ...
jomx99's user avatar
  • 21
0 votes
0 answers
39 views

Get only three rows for each type value

Here is my query: SELECT su.userid AS uid, su.display_name AS username, su.user_type, Ifnull(a.totalinvites, 0) AS invites, ...
Aayush Dahal's user avatar
0 votes
0 answers
22 views

Apply comparision within group of rows to get one row per group [duplicate]

I have a table ordered by fruit and date resulting from a join such as the following: +--------+------------+ | fruit | date | +--------+------------+ | apple | 2002-02-02 | | apple | 2003-03-...
loris's user avatar
  • 103
-1 votes
2 answers
115 views

How to write query to select highest value from same date

please help me write this query for MySQL. I want to select highest value from same datetime. So if there are multiple equal datetime value, return only one row with highest price. For summary, one ...
Sahasrar's user avatar
0 votes
1 answer
291 views

How to join two tables A and B and have the most recent status from B?

I have two tables: A and B A provider_id date 111 date 222 date 333 date B provider_id status date 111 initialized date 111 released date 222 failed date The result I want provider_id ...
srgbnd's user avatar
  • 103
1 vote
0 answers
185 views

Return back only top N rows for each ID - SQL

I have written this SQL after browsing different topics. The topic I got the solution from -> Limit Records MySQL version: 5.6 SELECT op.* FROM (select op.*, (@rn := if(@s = order_id, @...
olliesyke's user avatar
0 votes
1 answer
48 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) ...
33a5G7s9t1e3L's user avatar
-1 votes
1 answer
116 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 (...
33a5G7s9t1e3L's user avatar
2 votes
2 answers
905 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 ...
Jared's user avatar
  • 123
2 votes
1 answer
479 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 | ...
DeepSea's user avatar
  • 123
1 vote
1 answer
177 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 ...
Olivier's user avatar
  • 13
0 votes
1 answer
182 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 ...
Omar Hassan's user avatar
2 votes
1 answer
781 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 ...
sparkle's user avatar
  • 73
0 votes
1 answer
77 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 ...
ibpix's user avatar
  • 1
0 votes
2 answers
50 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 ...
NewToCoding's user avatar
0 votes
0 answers
46 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 ...
noushid p's user avatar
  • 101
0 votes
1 answer
380 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` ...
Stefanie's user avatar
0 votes
2 answers
2k 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 ...
gbox's user avatar
  • 121
0 votes
1 answer
3k 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)) ...
Mantykora 7's user avatar
1 vote
2 answers
4k 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 | ...
RonJohn's user avatar
  • 647
0 votes
1 answer
59 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 ...
Ernest Elikem's user avatar
1 vote
2 answers
3k 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 ...
Junaid Farooq's user avatar

1
2 3 4 5
8