Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
1answer
31 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
26 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
438 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
52 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
71 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
25 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
33 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
34 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
28 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
22 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
23 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
30 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
35 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
32 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
35 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
96 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 ...
0
votes
2answers
52 views

Select first and last row based on each user id mysql

I want to select every first and last event of every user id, if no first event exists,then just the last event instead . tried using partition over , but i am getting first 2 events instead. Input: ...
1
vote
1answer
45 views

How to obtain all distinct items but limit each distinct to max n and ordered by date?

I have a table where I have uuid as PK, command_name string, count_successful integer and command_start date. I can grab all records by: select uuid, command_name, count_successful, ...
2
votes
3answers
68 views

Find all items where the 2 latest statuses meet condition

I have a table in a PostgreSQL database like this: ID|Item-FK |timestamp|status ============================= 1 | 123 | ... | EXPIRED 2 | 123 | ... | PENDING ... I want to query all ...
0
votes
3answers
62 views

SQL Query to select rows based on specific condition [duplicate]

Below is an example of childs daily activity. I want to select the next rows after the last 'Play' activity based on user id. id timestamp activity 567 1541300537817000 ...
0
votes
2answers
64 views

sql select only the last record for multiple values

I have a table called "sessions" with say 4 columns like below id name s_time f_time 01 abc 10.15 10.45 02 abc 11.05 11.55 03 abc 12.18 13.46 04 abc 15.12 16.53 05 def 10.01 12.58 06 ...
0
votes
1answer
74 views

Select only those rows based on specific condition of user id

Below is an example of child's activity table. I only want to see all rows after 1st Tv activity for each user id. I tried grouping but it is not working for me Table ID Timestamp ...
2
votes
1answer
63 views

How to only pick the first matched row in a one-to-many relationship? [closed]

I have two tables A and B with one-to-many relationship. For rows A1 and A2 in table A, the relationship is A1 -> [B11, B12, B13] A2 -> [B21, B22] When I join A and B I get A1,B11 A1,B12 A1,...
0
votes
2answers
34 views

Joining 3 tables while using MAX

Overview I am attempting to join 3 tables auction, bids, and users. I need several key pieces of information from each table based off the bids table. In the end I want a summary overview of the ...
0
votes
0answers
19 views

How to get the latest LastUpdate ,LocReqNo of each locationID of MySQL queryautoricardo.ch - Fahrzeugmarkt – Apps bei Google Play

As I want to retrieve the LastUpdate , LocReqNo of every location. I mean to say that in the mention table there is 3 locationId , i.e., A1,A2,A3. I want the output of the above table as follows: I ...
0
votes
1answer
101 views

Postgres Greatest Query not returning correct timestamp

I think I might be writing this query incorrectly. Essentially, I want to find the object with the latest timestamp across four possible values (in this example, user_id is 1). SELECT *, GREATEST(...
1
vote
1answer
32 views

Getting the first 3 children(items) for each owner in a list of primary keys

I have two tables Item an Owner, an a Foreign Key from Item to Owner, owner_id. I'm using PostgreSQL 10. For each owner I want to get the first 3 items if exist. select "items_item"."name" FROM "...
2
votes
0answers
47 views

How to get a single row per group where MAX(column) or first if none?

I have the following schema: users: id email 1 'user.one@test.com' 2 'user.two@test.com' video_group: id title 1 'Group 1' 2 'Group 2' videos: id group_id rank title 1 1 1 'Group ...
2
votes
2answers
61 views

Delete one in two rows in an ordered result set

Is there a way to delete one in two rows in a result set using a single query? E.g. How to turn: +-------------+--------+-------+-------------+ | name | gender | state | date | +------...
1
vote
1answer
48 views

Delete duplicates and reroute referencing rows to new master

Given three tables: A users table with id's: ╔════╗ ║ id ║ ╠════╣ ║ 1 ║ ╟────╢ ║ 2 ║ ╟────╢ ║ 3 ║ ╚════╝ A purposes table with user_id's and name's: ╔════╤═════════╤══════════╗ ║ id │ user_id │ ...
0
votes
1answer
44 views

Optimizing a query that retrieves five rows for each letter of the alphabet

A site that I'm currently working on will execute a query 26 times but only changes a letter within the where clause. I am looking to make this more efficient by running only one query. Here is an ...
0
votes
1answer
56 views

join two tables, include one row from the parent table for each primary key

I've got a simple join over a primary and foreign key... but I need to include a row from the parent table in the results for each primary key as a sort of header row for the children. For instance, I ...
1
vote
1answer
74 views

How to query closest date and display the specific data

guys, I have a problem displaying data from my query code. I want to display data from the closest date today. Below are my sample data and my output data that I want to display. Landing Area data. |...
1
vote
2answers
38 views

For each value in field A, select the row with some special value in field B, while respecting further criteria

Disclaimer: While the basis of my question—the greatest-n-per-group question—has been asked and also answered so many times before, I am facing a seemingly hard issue due to extra conditions that I ...
0
votes
1answer
31 views

Query to filter out data based on two logic

This is a sample table: sample_id | timestamp | p_id ============================================ 62054 | 2018-09-25 10:18:15 | 2652 62054 | 2018-09-27 16:44:57 | 966 ...
0
votes
2answers
29 views

select newest update record among similar record

I have 2 tables segments and summaries. I want to list the shows in a date interval and just show the latest updated segments (in bold) since it is the freshest copy of the segment for that person. ...
1
vote
0answers
33 views

How to create subquery on multiple result columns?

I have two tables, both sharing the following columns: table inventory: id, key, timestamp table stocks: id, key, timestamp I want to select all occurrences of the key from inventory table together ...
2
votes
1answer
82 views

Get row with max date from a joined table

I have this schema of dossiers having 1 to N pages, and each page has a date: CREATE TABLE dossiers (id INT AUTO_INCREMENT, code VARCHAR(50), PRIMARY KEY (id) ) ENGINE=MyISAM; CREATE TABLE pages ...
0
votes
1answer
21 views

Comparison Clarification

I would to hear the solution for the below requirement. I have table A with key number and Fruits, table B with Fruits and rating with the below data. I have to retrieve result with least rating ...
0
votes
1answer
27 views

Retrieve latest 3 values from a large table

Engine MySQL 5.6.37 I have a continuously growing table (around 10M rows) which keeps the values from devices along with a timestamp. Below is the table structure: CREATE TABLE `meterreadings` ( `...
1
vote
1answer
323 views

Oracle SELECT FIRST?

I have a table of data invoices invoice | vendor | date 0 | 0 | 08/01/2018 1 | 0 | 08/02/2018 2 | 1 | 08/05/2018 3 | 2 | 08/10/2018 4 | 2 | 08/...
0
votes
1answer
316 views

MariaDB order by before Group by

I have looked around at other solutions, but none seems to work quite right. What I am trying to do is to get the latest result (sorted by date) from a selection, grouped together by sample. Here is ...
2
votes
1answer
801 views

How to get last and first occurrence of items into separated columns?

I do have the fallowing table: id name value 1 Daniel 3 2 Daniel 7 3 Daniel 2 4 Carol 9 5 Carol 4 6 Carol 9 7 Ray 5 8 Charles 1 I would like to get the first and the ...
6
votes
1answer
5k views

Left join without duplicate rows

I have two tables called record and record_history. For each record, there could be more than one history. They can be joined by id and record_id. I want to get all the record's entries with recent ...
1
vote
2answers
49 views

MySQL 5.6.27: How to get latest pairs for different points in time in a single query?

I use the following query to get pairs of latest s1.value for given group ids and some fixed date. Now if I want to query pairs for different dates I have to run this query several times with ...
2
votes
1answer
85 views

Aggregate additional values to min and max

This is a follow-up question to: Make custom aggregate function easier to use (accept more input types without creating variants) This SQL statement is given: SELECT c.name AS commodity_name, ...
3
votes
2answers
65 views

Make custom aggregate function easier to use (accept more input types without creating variants)

Recently I wrote a custom aggregate function in postgres that would return a specific column for the row that matches the max/min aggregate using a different column. While the code in itself works ...
2
votes
2answers
63 views

MySQL: Reference aggregate function in conditional select

I'd like to reference aggregate function like this: Sample Data: +-----------+----------+-------+ | member_id | group_id | score | +-----------+----------+-------+ | 1 | 1 | 12 | | ...