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
1 vote
0 answers
83 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
  • 11
0 votes
2 answers
53 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
541 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
56 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
49 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
34 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
84 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
288 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
193 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
74 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
98 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
553 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
28 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
3 votes
2 answers
1k 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,447
1 vote
2 answers
38 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
70 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
55 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
37 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
52 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
87 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
442 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
45 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
59 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
37 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
87 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
167 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
172 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
46 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
106 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
793 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
395 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
152 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
140 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
701 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
73 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
48 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
39 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
302 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
1k 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
  • 111
0 votes
1 answer
2k 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
3k 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
  • 606
0 votes
1 answer
57 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
2k 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
3 votes
1 answer
138 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 ) ...
samuel02's user avatar
  • 133
1 vote
1 answer
3k 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)...
Héctor's user avatar
  • 287
1 vote
1 answer
3k 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/...
no name sry's user avatar
0 votes
1 answer
1k 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 ...
Googlebot's user avatar
  • 4,491
3 votes
2 answers
511 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 ...
Declan's user avatar
  • 133
0 votes
1 answer
5k 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 ...
randomprogramming's user avatar

1
2 3 4 5
8