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
11 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
29 views

SQLite: Leave only n rows for each field

I have a table +-----------------------------+---------------+-----------+ | Painter | Painting | Rate | +-----------------------------+---------------+-----------+ | ...
-1
votes
0answers
12 views

Mysql: how to get unique values in column whose time is latest? [duplicate]

I want to query all rows where B is a distinct number for example: A B time ----- 1 2 20190612 1 2 20190613 1 2 20190614 1 1 20190614 1 1 20190613 Should give: 1 2 20190614 1 1 20190614
0
votes
1answer
25 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
25 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
42 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
38 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
41 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
454 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
55 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
80 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
42 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
38 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
46 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
31 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
27 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
38 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
95 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
36 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
38 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
137 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
55 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
69 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
64 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
68 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
76 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
67 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
40 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
109 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
48 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
63 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
50 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
79 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
98 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
40 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
32 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
116 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
497 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
447 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 ...