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

0
votes
0answers
14 views

get minimum from result with group by mysql

i have table it store hierarchy data in mysql this table store stable relation but if each user les than 1000 buy removed and user User a lower level replace thia is my code and work fine, after qroup ...
0
votes
0answers
34 views

Writing query for sorting based on foreign attribute

I am creating a voting system. My database looks like . Vote is a 'wrapper' for subitems. A vote has many subitems. When an action on that wrapper is performed (create/update/edit),a row is added in ...
0
votes
1answer
20 views

Get the highest value [duplicate]

I have the following data: account_id note_id size id ---------- ------- ------ -------- 48 1554 196036 28229509 48 1554 18777 28229588 48 1554 4861 28229566 ...
1
vote
1answer
58 views

Delete the maximum ID row [duplicate]

How can I find duplicate rows and for each group of duplicate rows delete the max ID row, in MySQL? Using this query, I can find duplicate rows: SELECT * , COUNT( awb_no ) c FROM tbl_cod_rpt_COPY ...
3
votes
3answers
78 views

Get last two IDs from rows for last two days

I have a table that looks like this: +------------+------------+--------+----------+--------------+ | terminalid | date | toolid | toolname | workcenterid | +------------+-----------...
2
votes
2answers
167 views

Select the maximum aggregate value for a limited set of chunked/grouped rows in a large database?

I work on WordPress plugin which implements sitemap functionality. One of the steps is to build an index of sitemap pages for post entries. The logic required is roughly: determine last modified time ...
3
votes
2answers
42 views

Count rows with max per group and additional condition

I have the following table: horse_main_id | race_id | horse_name | rating100 | race_result ---------------+---------+-------------------+-----------+------------- 23 | 159 | ...
0
votes
2answers
48 views

CASE with max()

I know how to do this in SQL Server but Postgres is different. I want to return the max employee pay. Select b.enumber, b.bday, case when Max(c.id) then c.pay ELSE c.pay End As "Current Pay" From ...
1
vote
0answers
12 views

Efficiently get records with latest timestamp per group [duplicate]

I have a table which roughly looks like this uuid uuid, id id, data text, last_updated timestamp Each row has an associated unique uuid. However, the key of interest is the id (indexed). ...
2
votes
3answers
86 views

Join between different frequency tables

Assume that I have two tables, DailyTable and QuarterlyTable, where each have 3 columns: ID, Date, and Value. As the names indicate, the frequency of data stored in DailyTable is daily, while the ...
4
votes
2answers
215 views

Optimize query to find top N users who commented on a post

Problem Trying to find the most efficient query to retrieve the top N (5 in the examples) users who have commented on a post, where a user is considered 'top' if they have the most followers. The ...
1
vote
1answer
41 views

Display last date grouped by warehouse and product

I have a table called daily_stock with the following fields: +-----------+----------+------------+----------+ | warehouse | product | date | stock | +-----------+----------+------------+----...
5
votes
3answers
237 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
61 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
31 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 ...
6
votes
1answer
245 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
30 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 cast/...
2
votes
5answers
128 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
54 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
52 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, MAX(Date)...
0
votes
1answer
29 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
41 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
156 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
66 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
39 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
130 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
435 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 (...
2
votes
1answer
106 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, `body`...
0
votes
1answer
47 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
35 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
35 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
67 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
53 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 name,...
0
votes
0answers
48 views

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

I have the following table: tbScheduling +---------+----------------+----------+---------------------+ | id | code | customer | date | +---------+----------------+-------...
0
votes
2answers
56 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
93 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
232 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
22 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
549 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
33 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
249 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
418 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
167 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 ...