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

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
13 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
10 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
23 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
37 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
23 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
30 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
16 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
31 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
88 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
28 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
31 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
27 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
37 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
32 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
57 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
27 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
70 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
51 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
98 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
20 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
63 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
53 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
77 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 ...
0
votes
2answers
79 views

How to display the dates without DUPLICATE and ORDER by MAX DATE

I just want to ask that how can I display the null values along with the MAX date. SELECT MEMB.MEMB_N, MEMB.LAST_M, PrintDate FROM MEMB INNER JOIN tblPrint ON MEMB.MEMB_N = tblPrint....
0
votes
1answer
134 views

MySQL Simple pivot table with MAX(), GROUP BY and only_full_group_by

I'm having trouble using GROUP BY with only_full_group_by. Here is my employee_job table: +----+-------------+--------+------------+ | id | employee_id | job_id | created_at | +----+-------------+---...
0
votes
1answer
23 views

MySql Query for Max(Date) not Today

I have the following table: Name - Date Adam - 7-13-19 Stan - 7-13-19 Adam- 7-11-19 Bob - 7-10-19 Stan- 7-10-19 Eric- 7-10-19 Lucy- 7-09-19 Eric- 7-09-19 Bob - 7-09-19 Lucy - 7-08-19 I ...
0
votes
3answers
92 views

SQLite: Leave only n rows for each field

I have a table +-----------------------------+---------------+-----------+ | Painter | Painting | Rate | +-----------------------------+---------------+-----------+ | ...
0
votes
1answer
28 views

Query to filter a table based on a comparison applied to the same table?

Case & Problem: Consider a corporate building with some rooms and departments that need authorization to enter. Employees have to scan their badges to be authorized. If the employee is inside (...
0
votes
0answers
27 views

Order by not working in subquery

I have a table with comments related to articles. entity_id | parent_id | comment | created_at 1 | 4 | AAAA | 2018-10-18 12:28:03 2 | 5 | BBBB | 2018-10-19 12:28:...
1
vote
1answer
1k views

Limit left join for multiple rows

Since MySql doesn't support main alias reference in subquery is a bit challenging to create query with multiple rows where you have to limit rows of left join tables. Let's overview simple example. ...
0
votes
1answer
44 views

Selecting N values from each group

I have 2 tables which have to be joined to get needed data. Table1 is articles, Table2 is for channels/categories. For the sake of simplicity, both tables only have 2 fields. Basically, I need ...
1
vote
1answer
78 views

Multiply by value from related row with latest date

I have two tables, one with staff time entries, another with staff rates starting from a particular date: time_table id | staff_id | entry_date | hours ---------------------------------- 1 | 1 ...
7
votes
2answers
465 views

Can I combine SELECT TOP() with the IN operator?

I was wondering if there is a way for a SELECT query to be made on each matched value found within IN. Consider that the table below contains 100 records for each of the values included referenced by ...
1
vote
1answer
60 views

Select most recent event for each plate on a property unless the most recent is “Out” - self-join?

My cameras are positioned on the entry and exit gates of a parking garages. As cars pass the cameras, they capture the license plate and other attributes and load it into a database in real time. I ...
0
votes
3answers
103 views

How to SELECT “closest” rows from another table?

I have two tables, map1 and map2, there are multiple possible combinations between the columns map1.id1 and map2.id2. I have tried the below query: SELECT map1.id1, map2.id2, MIN(...
0
votes
1answer
74 views

Select only last row from table for list of values

As example we have a table: CREATE TABLE t1 ( a BYTEA NOT NULL, r_pointer BIGINT, s_pointer BIGINT, data BYTEA, PRIMARY KEY(a, r_pointer) ) PARTITION BY HASH (a); In case we I want ...
0
votes
1answer
134 views

Is MySQL syntax GROUP BY … ASC/DESC removed or not?

I would like to ask if MySQL syntax GROUP BY ... ASC/DESC was removed or not? By this worklog task it should be removed but it seems that it is not truth. There are no details in which version it was ...
1
vote
1answer
76 views

Top 10,20,30 in Group

Morning all! I have a program and database I created that pulls down data from a website (player cost for MLB teams for a game) and I aggregate that data and use it for various things. One of the ...
0
votes
1answer
37 views

specify the correct element to pick in JOIN mysql

i Have three tables: --------------------- | PRODUCTS | --------------------- | id | product | --------------------- | 1 | super prod 1 | | 2 | super prod 2 | | 3 | super prod 3 | |...
0
votes
1answer
24 views

Long loading query

i have a query that loads ~1600 lines. I use in HeidiSQL to run querys (but they are coming from php/mysqli app). On my test base it takes ~12 seconds to load and on client ~40 seconds (In php id ...
0
votes
1answer
40 views

MySQL Conditional Counter Based Other Columns?

I have a query that returns purchases for all customers in a store across a date range. It works fine, but now I've been asked to modify the results so only the first purchase per customer per day is ...
0
votes
1answer
116 views

mysql greatest n per group: latest message content and time for each conversation

I have two tables, one for topics, and one for messages. In terms of relationship, messages are posted within a topic, so each topic has many messages. I am trying to make a query that would: - list ...
2
votes
2answers
455 views

First order by importance then group by section

In this table scheme http://sqlfiddle.com/#!9/101a9c/9/0 id section rev importance 2 1 b 6 1 1 a 5 3 2 c 9 5 3 e 6 4 ...
0
votes
1answer
51 views

Last value in two levels of group by in MySQL

I have the following data set: 2018-01-02 Oranges 1479 1480 2018-01-03 Oranges 1478 1504 2018-01-04 Oranges 1507 1494 2018-01-05 Oranges 1510 1551 2018-01-02 Apples 2150 2196 ...
1
vote
1answer
45 views

How to correctly use group by for getting the record with the closest time for each rec_id

Background I have a table records which stores rec_id, rec_time and rec_value. A record is stored for each rec_id only when its rec_value changes. I would like to query the value for a bunch of ...
0
votes
2answers
35 views

How to get the best time from submited values?

I have a MySQL table, and i'm trying to figure how can i get the first result for each user. For example: Email Time DateSubmission usera@email.com | 0:14 | 28/2/2019 00:10 usera@...
4
votes
1answer
427 views

Slow left join lateral in subquery

I have three tables: units (apartment units), leases, and line_items (a better name would have been recurring rents). A unit has many leases and lease has many line items (associated with each rent ...
0
votes
0answers
34 views

“GROUP BY” to keep only the first 2 rows based on date

I have a list of online robots conversation, with the time indicating when a client started a conversation and when the robot answered. I am trying to measure how long does the robot take to provide ...