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
18 views

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

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
25 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 ...
-1
votes
1answer
17 views

getting last entry with ORDER BY with inner join not working [closed]

I just realized that this statement is no longer getting the last entry from attributes. I was working before I believe I had added the inner join. But adding this inner join I no longer get the ...
2
votes
2answers
23 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
37 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
33 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
31 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, '...
0
votes
1answer
38 views

Why a query takes too long in statistics thread state in AWS Aurora MySQL?

The following query execution too long in statistics state and I couldn't figure out why. DB engine - 5.7.mysql_aurora.2.07.2 DB Size - db.r5.4xlarge Sample Query Profile output +----------------------...
-2
votes
2answers
27 views

query to get records that have most recent date [closed]

How can I make data that looks like this: Job_Id Monitor_Date B119 3/11/2010 B119 3/07/2013 B119 15/05/2020 B121 03/05/2015 B167 3/12/2010 B167 3/07/2013 B167 15/10/2015 B189 ...
1
vote
1answer
58 views

How to get multiple “last values” for different criteria from a single column?

Our interest_log table stores events of users expressing interest on foo. CREATE TABLE interest_log ( id bigint NOT NULL PRIMARY KEY, user_id bigint REFERENCES foo_user(id), foo_id ...
0
votes
1answer
40 views

Display Top X results for each unique value

I am trying to figure out how I can output any machine that has had the latest 3 backups be larger than 10GB. My SQL Table looks similar to the below table: ComputerID | BackupImageName | ...
7
votes
2answers
432 views

SELECT DISTINCT ON, ordered by another column

Please consider the following table test: CREATE TABLE test(col1 int, col2 varchar, col3 date); INSERT INTO test VALUES (1,'abc','2015-09-10') , (1,'abc','2015-09-11') , (2,'xyz','2015-09-12') , (2,...
0
votes
1answer
48 views

List top N rentals for each month

I'm trying to work out the SQL to list the top 5 rentals for each month on MySQL or SQL Server. The database is the Sakila Sample database and the table is rental. Here are the first several rows in ...
0
votes
2answers
21 views

Optimize MariaDB query with table that self-joins

I have a query that I think is taking way too long (as in I've not yet seen it finish!) but I can't figure out why. The query (which is generated by an application that would be difficult but not ...
0
votes
2answers
31 views

Mysql horizontalizing grouping

I have a table that essentially has rows with: sessionId date/time event event can be (to make it simple, "start" and "end") The same sessionId however can be reused, so, simply I have sessionId ...
0
votes
2answers
35 views

How to select the most recent entry based on a field in MySQL

My table has multiple entries by individual customers, These customers have made multiple purchases over the years from any of several suppliers listed in my database. I need to run a query to select ...
0
votes
1answer
21 views

MySQL cursor always exits out of loop

The cursor query and select value query returns rows if I run it in mysql but when in a cursor it always exits out of loop. Anything wrong here? I've added "BEFORE LOOP", "EXIT" and "IN LOOP" so it ...
0
votes
0answers
35 views

MySQL 8: Most performant query to obtain latest version of each record

We have a database with over 7 million entries, and we are struggling to find the best SQL query to obtain the latest entries for each a & b & c combination on each version. For instance: ...
0
votes
0answers
16 views

Select last transaction and “pivot” results

There are two tables, billing and billing_details containing all pertinent details. I am attempting to produce a list of the most recent transaction per each customer with certain details of the ...
1
vote
1answer
37 views

Get one row between two duplicate rows with one different value in a column [closed]

I have a table with data entered manually by the customer, there are some data which is duplicated but just a value in one column is different. For example +------------+-------+--------+----------+ |...
0
votes
3answers
57 views

How to group rows while filtering out values per each group?

Imagine you have the following setup: CREATE TABLE namechanges ( id text, new_name text, change_date timestamp ); INSERT INTO namechanges VALUES ('x', 'alice', '2020-03-01'), ('y', 'Bob T.'...
0
votes
1answer
51 views

Select one item per matching ids(value)

I have a mysql table(trips) with records about cars. I wish to query the data and return one item per carId. But am unable. Below is the query example getting 50 records, which should also have 50 ...
16
votes
3answers
11k views

SQL Server - Select most recent record from each group when performance is critical

I run a SQL Server 2016 database where I have the following table with 100+ millions rows: StationId | ParameterId | DateTime | Value 1 | 2 | 2020-02-04 15:00:000 | 5....
0
votes
0answers
34 views

Improve MySQL query efficiency for first row in a group

I've written the query below in MySQL to get the top 10 top landing pages across all browser sessions. Reading other similar posts about how to access the first row in a group, it seemed like the ...
0
votes
1answer
20 views

first row and last row fields value from where data changed [closed]

Assume I have a table like this: I'm trying the have a output like following picture How best to achieve this?
1
vote
0answers
17 views

Deduplicate table based on createdate

I'm new to SQL and I'm trying to get my head round a lot of stuff but so far with little success. So I'm trying to get some help, here goes I have a table "Userdata" Email | FirstName | LastName | ...
0
votes
1answer
12 views

How can I Get Value of Max(col) in a greatest n per grouped by into Declared variable. MS SQL sever

I am looking for how to set the value of @MaxItemSeqNum to the returned result of the column MAX(dbo.[Table1].[Item_Seq_number]) in my select statement. I need this in order to insert the next record ...
1
vote
1answer
27 views

Optimizing a query that gets the opening balance

I have a query like this select org, `Balance` from oss_collection_history och2 where och2.`DateFrom` = (select min(`DateFrom`) from oss_collection_history och3 where och3.org = och2.org) It ...
1
vote
2answers
41 views

Filtering down specific criteria for all rows and return only one row for each column with the same value

I have house_leases and house_lease_terms (see table schemas below). A house_lease can have multiple house_lease_terms however there can only be one "active" term at a time. Table Definitions: ...
0
votes
0answers
31 views

SQL Syntax to get one row for each Employee ID and max date

I joined a few tables in order to get data pertaining to certification by employees. I have a query that gives me the employee id, name, certification start date JPM_DATE_3, certification end date ...
0
votes
1answer
33 views

Filter the query result

I have one query by which I am getting duplicate ComapnyID and I want to filter that and remove duplicate ComapnyID but don't want change the order by report. table 1 reports Table 2 Company Here ...
0
votes
0answers
21 views

(MySQL) Finding the first, second, …, nth movie directed by a group of directors [duplicate]

There's a table titled "movies". The table can be found here. (Please scroll down to the bottom of the page to see the table). I want to find the list of first movies directed by each director in the ...
-2
votes
2answers
33 views

Limit join to one per row

Say I have many players, each with a numerical rating, stored in a ratings table that keeps track of both current and historical ratings (i.e. it has player_id, rating, and date columns). How would I ...
0
votes
2answers
95 views

SELECT last date from multiples values

I have this table: I want to make a query that allows me to show the last value in fecha_gestion grouped by NitSec. It would be something like this: SELECT id_log, fecha_gestion, nitsec FROM table ...
0
votes
1answer
30 views

Gereralise the query for all user_id

I have a mysql query select sum(duration),app_name,app_id,user_id from timesheet where user_id=164 group by app_id order by sum(duration) desc limit 5; The result is sum(duration), app_name, ...
0
votes
1answer
37 views

Joining two select statements

Im using MySql 8.0 and I've been exercising a lot of quries but this one I cant seem to figure it out. I need to join somehow two select statements that I've written and they display the correct ...
-2
votes
1answer
39 views

What alternative sql method can I use to see more records, GROUP BY id only shows one row per unique id

What I want is per map id I want to be able to see the top highest scoring gangs (also I should be able to say from which date or a specific single day), (i thought of using GROUP BY ID, but it would ...
-1
votes
1answer
50 views

Update flag based on max last updated date within in the group

Say for example I have three records: Name Email Group ID Last update date Master Record dinesh.s dinesh.s@xyz.com 10 1 Apr 2019 dinesh.s dinesh.s@...
0
votes
1answer
33 views

Mysql - query optimization

I have a DB schema as such, SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='...
-1
votes
1answer
60 views

Return the last purchase for each item in a group

I'm trying to calculate the cost to make one product, for this I have a table with the data sheet to build each product. So, for example, to make the Product A I'll use item a, item d and item g. ...
0
votes
1answer
33 views

how do i get rows for each month which contain the store with the highest number of purchases?

how do i get rows for each month which contain the store with the highest number of purchases? my code is: SELECT DATE_FORMAT(buyDate, '%M') as month, count(id) as numPurchases, store from ...
1
vote
2answers
132 views

How to get the oldest values over each id in a PostgreSQL table efficiently?

How can PostgreSQL return a list of the oldest timestamp values over a table of sensor id measurements? Let me explain the situation with a sample table: CREATE TABLE sensor_data( sensor_id INTEGER, ...
1
vote
1answer
62 views

Improve query performance of subquery with MAX()

I am looking to migrate our support database from MS SQL Server to Postgres and most of it has gone OK. However, some of the queries seem to be slower on PG than on the SQL Server. We have a ticket ...
3
votes
2answers
100 views

Why max(id) with join not working or how to optimize it with join?

Below is my MESSAGES_THREAD table: CREATE TABLE `messages_thread` ( `id` varchar(64) NOT NULL, `creator` mediumint(9) NOT NULL, `other` mediumint(9) NOT NULL, `time` datetime DEFAULT ...
0
votes
1answer
24 views

distinct description

I have the following database: id | remote_id | title | description ------------------------------------- 1 | 12341234 | test | test-description 2 | 4535234 | blah | testestetetst 3 | ...
0
votes
1answer
154 views

Mysql query to find last entry for each of the last 7 days

I have a table that contains a timestamp and a number for each row. This data is logged every 15mins on the quarter hour, 24 hours a day, but it does sometimes get missed or is delayed. I'd like to be ...
0
votes
1answer
62 views

How to select distinct? not select duplicated row

I have 2 table select DISTINCT (B.MAIN_ID), A.NAME, A.NAME_ID, A.CREATED_DATE from FAMILY A, ADDRESS B where B.F_ID = A.N_ID result: MAIN_ID | Name | NAME_ID | CREATED_DATE 100 | ...
1
vote
2answers
84 views

How to Speed Up my SQL Query?

I have a query that is taking much longer than I expect it should, and I can not figure out how to speed it up. For a little bit of background on the table structure, these are audited records. There'...
0
votes
3answers
43 views

How to get last inserted rows where a condition is satisfied

table1: esps -------------- A B C -------------- table2: id _name esp ------------------------------ 1 Ana A 2 Ana B 3 Mario A 4 Jorge A 5 ...

1
2 3 4 5 6