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

5
votes
3answers
180 views

Selecting one row from each group based on a Varchar field

Given a table Avenger whose data looks like this: id Avenger Type Power 1 Captain America foo 2 2 Captain America bar 3 3 Me foo 5 4 You ...
0
votes
2answers
53 views

How can I return top 10 rows from each state [duplicate]

How can I return the top 10 rows for each category? Say I have a table named xyz. It has a column called state, in the political unit sense of the word. I want the top 10 rows from each state. Do I ...
0
votes
1answer
30 views

Query the last records by date, where condition is true

A made this query, which is works perfectly now: SELECT t.DocID, t.RevID, t.EffDate FROM tblRev AS t INNER JOIN ( SELECT DocID, max(EffDate) AS MAXEffDate FROM tblRev GROUP BY DocID ) AS tm ...
4
votes
1answer
222 views

Partition by ascending and descending performance

I have a table, and for a given set of fields a, b and c, I need to get the first and last rows ordered by d and e, and am using ROW_NUMBER to get these rows. The relevant part of the statement is... ...
1
vote
0answers
27 views

Groupwise max then join

I have a votes table that has many subitems. I have a subitems action table that records what action was performed by which user on which group, on which subitem, on which vote Users can either ...
2
votes
5answers
125 views

MIN(column) with subquery

How do I query 3 tables (STUDENTS, MODULES, MARKS) such that I return the module name and the lowest mark score for student John Doe? SQL> DESC STUDENTS; Name Null? Type ...
3
votes
1answer
49 views

What indexes to use for GroupWise max

I have a user table and group table, and a many to many relationship between them. I have a vote table (one to many relation between group and vote) And a subitem table (one to many between vote and ...
0
votes
1answer
51 views

How to get top 5 rows of given Id? [duplicate]

I have the following code: DECLARE @Latest TABLE ( DomainId bigint, [date] datetime2 ) DECLARE @Rows int DECLARE @History int INSERT INTO @Latest (DomainId, date) SELECT DomainId, ...
0
votes
1answer
28 views

Returning only the row with maximum value from related rows

In the example below how do I get the rows where the swap id is the maximum value in relation to the reservationId? I am currently joining multiple tables to get customer, reservation, room etc ...
-4
votes
2answers
40 views

Highest value of sequence for each booking

I need to retrieve all the values of Booking_ID but with their highest individual respective 'Sequence' values. How can I do this? So the result should be something like this : BookingID ...
4
votes
2answers
147 views

Is there a way to SELECT n ON (like DISTINCT ON, but more than one of each)

I have a table us_customers that looks like this (with hundreds of thousands of rows): +----------+----------+ | id | us_state | +----------+----------+ | 12345678 | MA | | 23456781 | AL ...
3
votes
2answers
65 views

Group only certain rows with GROUP BY

Schema I have the following set-up in MySQL database: CREATE TABLE items ( id SERIAL, name VARCHAR(100), group_id INT, price DECIMAL(10,2), KEY items_group_id_idx (group_id), PRIMARY KEY ...
1
vote
1answer
37 views

Select entries based on a combination of columns

I have a table with a definition like this: CREATE TABLE [dbo].[Contact] ( ClientNumber [numeric] (20, 0) NULL, ContactDate [Date] NULL, Name [text] NULL, AdditionalInformation [text] ...
6
votes
2answers
125 views

Exists subselect vs inner join?

I'm moving up to the next level of my mystery query. It looks like there's a subselect inside of an exists, but on the same table. I think this could probably be simplified with an INNER JOIN higher ...
1
vote
0answers
34 views

Greatest N per Group in SQL Server [duplicate]

I want to find classical Greatest N per Group. I have two methods to solve this problem Dense_rank Over() method Min Over() method Both work flawlessly. Now I want to find which one is better ...
1
vote
2answers
68 views

Is there a way to do the following query with joins instead of a sub query?

I am using SQL Server 2014 and I want to eliminate having to use the following sub query so I can create an indexed view. The only thing similar to my problem is the greatest-n-per-group queries but ...
6
votes
3answers
354 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
82 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
84 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
44 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
34 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
34 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
64 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
52 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
45 views

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

I have the following table: tbScheduling +---------+----------------+----------+---------------------+ | id | code | customer | date | ...
0
votes
2answers
52 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
27 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
80 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
188 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
51 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
45 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
58 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
453 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
29 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
229 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
262 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
162 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
266 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
132 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
2k 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 ...
3
votes
1answer
1k 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
51 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
122 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
91 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
196 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
95 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
261 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
419 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 ...