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.

learn more… | top users | synonyms

6
votes
3answers
281 views

The fastest way to get the most recent records [duplicate]

I am looking for the fastest way to return the most recent record for each reference number. I quite liked the solution from BrentOzar.com, but it does not seem to work when I add a third condition ...
1
vote
1answer
60 views

Select ONE most recent post for each author

I'm sure its a simple question and I suppose it was asked many times, but I just can't figure it out from other answers, sorry. I use recent versions of PostgreSQL and MySQL. I have 2 tables: CREATE ...
0
votes
1answer
76 views

How do I write this query?

I have a table that looks like this: CREATE TABLE `message` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `grp_id` int(10) unsigned NOT NULL, ...
0
votes
1answer
36 views

Return the most recent event in a series of same title events [closed]

I need to be able to return the most recent event of a occurrence that may or may not have occurred more than once. For instance: Each time a product is moved from position A to Position B, it is ...
3
votes
1answer
26 views

Extract latest entry for each currency type from table [duplicate]

Solution required on Microsoft SQL Server. Here is a example of a table I have: --------------------------------------------------------------------- | Currency Code | Starting Date | ...
1
vote
1answer
30 views

Three table query to find the most recent item based on a distinct value [duplicate]

I am attempting (without success thus far) to write a query that will join 3 tables and return the most recent record based on a distinct value. Table A itemnum | itemname -------- | -------- 123 ...
2
votes
1answer
62 views

Get max url from PostgreSQL statement

I have a very complex query which generates a view in PostgreSQL. Now the query returns duplicated rows, well almost duplicated: one field, the "url" column is different. Now we still want to group ...
1
vote
2answers
47 views

Displaying a value associated with a maximum value

I have a PostgreSQL database for a supermarket (it's a toy problem) and I need to find which outlet has sold the most copies of each product and display it in a query which also shows the product ...
0
votes
0answers
41 views

Why the MAX function (MYSQL) does not work as expected?

I have the following table: tbScheduling +---------+----------------+----------+---------------------+ | id | code | customer | date | ...
0
votes
2answers
43 views

Limiting rows from a joined table

I have 2 tables Part and PartDtl. PartDtl contains one row for each order that has been placed for that part. PartNum | Description | ... 1234 | 1234 desc | ... 2345 | 2345 desc | ... ...
0
votes
0answers
25 views

Picking top-n within ranges of a joined table

I have a joined table where I need to pick the one field from a joined table when another field is the greatest value within a range from the primary table. I am writing a query to calculate ...
1
vote
1answer
65 views

get unique records from last 6 months

i am facing the below issue Table structure : some_table CREATE TABLE `some_table ` ( `wallet_id` int(11) NOT NULL AUTO_INCREMENT, `wallet_amount` double(10,2) NOT NULL, `actual_wallet_amount` ...
4
votes
2answers
152 views

Get the last 5 distinct values for each ID

I'm working with PostgreSQL 9.4. I have a table that contains the following entries: id | postcode | date_created ---+----------+----------------- 14 | al2 2qp | 2015-09-23 14:46:57 14 | al2 ...
0
votes
1answer
21 views

How can I select a field value in PostgreSQL based to the date computation within the table?

I have a PostgreSQL table that have these values: dollar_rate; end_date ------ 1. 46.77; 2015-09-16 11:03:03.4082 2. 46.70; 2015-09-17 11:27:00.2446 3. 46.67; 2015-09-17 15:09:15.0354 Now I have ...
2
votes
1answer
46 views

Tuning a query with: index, window function?

Description Environment: Centos 6-x64, Postgres Plus Advanced Server 9.3. I have a table with 4 columns with data as below: id code_id effective_date name 24 12 ...
5
votes
1answer
43 views

How to properly implement compound greatest-n filtering

Yep, more greatest-n-per-group questions. Given the a table releases with the following columns: id | primary key | volume | double precision | chapter ...
4
votes
2answers
50 views

PostgreSQL Get top-k minimum per range

Assume I have the following data: | f1 | f2 | f3 | |----|----|----| | 1 | 1 | 1 | | 1 | 1 | 5 | | 1 | 2 | 3 | | 1 | 2 | 6 | | 1 | 3 | 4 | | 1 | 3 | 7 | | 2 | 1 | 2 | | 2 | 1 | ...
2
votes
2answers
323 views

Adding a 'last time occurred' flag column

I need to add a column into a table that contains 3 columns: GroupKey - Shows the number of an item. Status - the number of the status that the group was according the insert date. The status ...
2
votes
1answer
25 views

Selecting closest, non expired value [duplicate]

I'm trying to select multiple rows where the value is closest to the end date but not passed, and return only those rows. Here's a sample of some data: School Code | School Year | Semester | Term | ...
5
votes
2answers
194 views

Nested aggregation and grouping on multiple columns in MySQL

Consider the following product table (which is highly trimmed down): `id` int AUTO_INCREMENT `category_id` int `subcategory_id` int `vendor_id` int `price` decimal(6,2) `inserted_at` timestamp For ...
2
votes
1answer
175 views

select n values per group

Lets say I have the following table ----------------------------- | user_id | comment | ----------------------------- | 2 | thats cool | | 2 | awesome | | 3 ...
1
vote
2answers
143 views

Minimum value on most recent date per account MS ACCESS SQL

I have a MS ACCESS DB table Riskscore with the three fields: CHACCTBS, CHRQDATE, CHSCORE and want to find the lowest CHSCORE on the most recent CHRQDATE per CHACCTBS where it is possible to have ...
1
vote
3answers
250 views

Remove duplicate values from query result

I am using Postgres 9.3.9. I have 2 tables : f_agent : CREATE TABLE f_agent ( f_agent__id bigserial NOT NULL, f_agent__total_users integer NOT NULL, f_agent__dim_time_id integer NOT NULL, ...
3
votes
3answers
114 views

How to select a row based on the maximum value of a column, when ties are expected

SUBSCRIBER DATE TIME VALUE aaa 23-04-2015 04:04:07 10 aab 23-04-2015 12:04:32 5 bbb 23-04-2015 01:04:05 20 bbb 23-04-2015 ...
1
vote
4answers
755 views

How to select first row from a join that returns multple rows on the primary key

This is related to this question: Joining multiple tables results in duplicate rows I have two tables that I am joining. They share a key. The person table has one name per primary key but the ...
1
vote
1answer
851 views

Is it possible to select distinct values in a json document using PostgreSQL?

I have a column that is using the JSON type. I would like to perform a query to select all distinct records for a particular field in the JSON string: I.e. given these three documents { id: 1, s: ...
0
votes
1answer
46 views

mysql Quiz leaderboard filter by points, time taken

I have a quiz report table which shows a report for every quiz a user takes. I need to create a leaderboard from this, which shows the top users best score, filtering by points and then time taken. ...
3
votes
1answer
118 views

How to index for timestamp queries

I have a products table where I insert around 150,000 records a day. Most of them are redundant, but I need to keep them because of the new expiration date. I get product feeds from about 5 vendors a ...
1
vote
4answers
86 views

Getting an aggregate (here: min) for a group of values [closed]

Example table: ID | value A | value B ---------------------- 1 | abc | 3 1 | def | 5 1 | ghi | 1 2 | cba | 9 2 | fed | 4 I want the ...
0
votes
1answer
137 views

Select records as groups based on sequence and date

I have data stored as: CompID | Seq | Udate | Utime --------+-----+-----------+------- mx433 | 1 | 1/1/2012 | 13:54 mx433 | 1 | 1/2/2012 | 13:43 mx433 | 2 | 1/4/2012 | 14:23 mx433 ...
1
vote
2answers
91 views

Speed up SELECT with WINDOW, (PARTITION … ORDER) with compound index

I have the following table: CREATE TABLE ranking_points ( id serial NOT NULL, store_id integer, book_id integer, list_name character varying(255), rank integer, created_at timestamp ...
0
votes
1answer
204 views

What is the best query to get the latest row version based on time?

I'm wondering what can be the most efficient query to get the latest rows from the following table. CREATE TABLE AGE_DATA ( INSERTED_DAY TIMESTAMP(6), HOSTNAME VARCHAR2(255 CHAR), CONCEPT ...
2
votes
3answers
146 views

Aggregate query with min

Having a product_stock table with following structure: product_id warehouse_id stock price Which has bunch of records (same product can be in different warehouses): 1, 1, 0, 500 1, 2, 5, 505 1, 3, ...
4
votes
1answer
398 views

Subselect of MAX(Version) takes many minutes, though there are only ~20K records

I have a pretty complicated data model. I can't understand most SQL examples without an explanation of what's being modeled, so I'll try to explain. Mainlines -> Releases -> Overlays -> Calibrations ...
1
vote
1answer
238 views

Select all most recent records

I have this two tables (and their main fields): Target: Message: Id IdMessage Id IdMain IdReply DatePosted ================ ...
1
vote
2answers
251 views

Efficient query to get last row group by multiple columns

I have a table like the following: spread_id serial NOT NULL, game_id integer NOT NULL, sportsbook_id integer NOT NULL, spread_type integer NOT NULL, spread_duration integer NOT NULL, home_line ...
3
votes
2answers
82 views

Selecting minimum value in top grouping only

I have a query built in Oracle 10 which produces a set of data like this: EMP_ID WORK_DT TIME_TYPE ---------- --------- --------- 12345 19-DEC-14 10 12345 18-DEC-14 10 ...
1
vote
1answer
233 views

How to Select the First (or last) entry for a specific day

I have a table "AttendanceRecords" with entries like this EntryID UUID PRIMARY KEY, StafID UUID REFERENCES staffmembers(ID) NOT NULL, ArrivalTime TIMESTAMP NOT NULL, DepartureTime TIMESTAMP For one ...
46
votes
6answers
7k views

Retrieving n rows per group

I often need to select a number of rows from each group in a result set. For example, I might want to list the 'n' highest or lowest recent order values per customer. In more complex cases, the ...
1
vote
2answers
8k views

How to select top 10 records from each category [duplicate]

I have a table with fields Hour, PathId, Duration, Event, CellId, Channel I have 50+ CellId. Each CellId has four PathId (i.e. 0, 1, 2, 3). Each PathId has many Events and Durations. Now I want to ...
0
votes
1answer
34 views

Query with column data for a max

In Postgres, I have a table like this: Column | Type | Modifiers ...
0
votes
2answers
76 views

Find the last visit by teacher and show the rest of columns in MySql

I would like to group the teachers by the last visit and show the other values corresponding to the last visit. CREATE TABLE IF NOT EXISTS `visits` ( `FIELD1` int(5) NOT NULL, `teacher` varchar(9) ...
0
votes
3answers
243 views

Get latest status of an item in the database

I have this table: ID|Item-FK |timestamp|status ============================= 1 | 123 | ... | OK 2 | 123 | ... | FAILED ... I want to query get all items which last status was FAILED. ...
2
votes
2answers
8k views

Join 3 tables and return only distinct values for max datetime

I've been trying to do this the whole 2 weeks but seems like to no avail. 3 tables involved here. but am expecting the result would be distinct case no. for the same date & user. otherwise, it ...
2
votes
1answer
199 views

Selecting most recent date when dates are stored in another table

Working in a MySQL database, I have tables set up like this: Table song_instances has song_id (int), meeting_instance_id (int) The meeting_instances table has columns meeting_instance_id (int), ...
0
votes
1answer
176 views

Select latest record for each ID

I have three tables that contains data as below: Users Id Name Other_Columns --------------------------- 1 John Blah 2 Ricky Blah 3 Stella Blah 4 Bob Blah Saldo Id ...
6
votes
3answers
10k views

GROUP BY one column, while sorting by another in PostgreSQL

How can I GROUP BY one column, while sorting only by another. I'm trying to do the following: SELECT dbId,retreivalTime FROM FileItems WHERE sourceSite='something' GROUP BY seriesName ...
2
votes
1answer
8k views

How to select the line with the max(date)?

the data looks as following: id group date 1 a 2013-01-01 1 b 2014-01-01 2 a 2012-01-01 2 b 2013-01-01 My database is mysql, for each id, I need to select the line with ...
3
votes
2answers
2k views

Are ORDER BY clause & ROW_NUMBER duplicating functionality when I need top n queries

I have a table that tracks violations for a student. I want to count the number of violations and select top 2 violators from each class. The query would look like this SELECT * FROM ( SELECT ...
2
votes
1answer
222 views

Why am I getting different results from a query vs. a view

The base problem I'm facing is that I need the latest record for each item. A little setup... MySQL 5.6.14. I need to create two views (because MySQL won't let me have a subquery in a view). My ...