Stack Overflow
Stack Overflow

Questions tagged [greatest-n-per-group]

Query the row with the greatest/least value per group.

One of the most frequent stumpers for SQL programmers is to query rows that match the greatest (or least) value over a given group. For example, all details about the City with the largest population per state. You can't do it simply with GROUP BY because the other columns will be ambiguous.

A variation that appears occasionally is to query the top n rows from each group (see the ).

Make sure to include the database tags as well (, , etc.) because depending on the database, the optimal query might be different.

4224 questions
1
vote
4 answers

Select row with least value in multiple columns without ROW_NUMBER

I want to get the row per group with the min value of two columns. I have a table that has listings for items I want, as well as their cost and distance from me. mytable: item | cost | dist -----+------+--------- 1 | $2 | 1.0 1 | $3 |…
M. McCrary
  • 35
  • 3
1
vote
1 answer

MySQL subquery select first row for each group

I need to create a MySQL stored procedure it selects each User SUM of all the Points they've earned. The query should group Game by StartTime and only select the first row of each group ordered by Points. I'm trying to ignore duplicate StartTime…
Nicholas Bastarache
  • 71
  • 1
  • 6
1
vote
1 answer

How do I get multiple maximum column values for multiple rows with the same id?

I need to select the maximum amounts in one column for a common id in another column. There could be several id's in the report_id column that have the same, maximum last_update amounts. Data…
absentx
  • 1,397
  • 4
  • 17
  • 31
1
vote
2 answers

amazon-redshift : select id, first(a),sum(b) group by id

In mysql/SparkSQL we have first function. which doesn't exist in Redshift. I have to change code SELECT product_id, first(product_code) as product_code, first(product_name) as product_name, first(time_date) as time_date, max(price_max)…
Mithril
  • 12,947
  • 18
  • 102
  • 153
1
vote
2 answers

6 top min average elements postgresql

I need to count average price and group them by 2 columns. Then select top 2 values (PostgreSQL 10.1). E.g, I have the following structures: ------------------------------------------------------------------------------------------ category …
user565447
  • 969
  • 3
  • 14
  • 29
1
vote
1 answer

SQL - Max value from a group by when creating a new field

I have a database with a table called BOOKINGS containing the following values main-id place-id start-date end-date 1 1 2018-8-1 2018-8-8 2 2 2018-6-6 2018-6-9 3 3 …
Time Rift
  • 17
  • 6
1
vote
1 answer

Select second latest record for each group in the table

I have the data as below in my table in SQL Server database. Query select * from mytable order by UpdatedDate Desc returns: ID COL1 UPDATED DATE 37 NULL 2018-06-25 18:20:50.210 37 NULL 2018-06-24 18:22:31.230 38 NULL 2018-04-03…
Phanindra Gopishetty
  • 161
  • 1
  • 8
1
vote
5 answers

Find the row with the highest value in SQL

According to the schema, the Orders table has the following columns: OrderId integer, CustomerId integer, RetailerId integer, ProductId integer, Count integer I'm trying to figure out what product has the highest number of orders for each…
Sam Fischer
  • 1,442
  • 19
  • 35
1
vote
2 answers

SQL Select rows with max difference on a column

I have two Postgres tables such as below called client and order. id | name ------------ 41 | james 29 | melinda 36 | henry ... id | date | volume | client_id ------------------------------ 328 | 2018-01-03 | 16 | 41 411 | 2018-01-29 | 39 | 29 129…
user10598526
1
vote
1 answer

Select top X records for each group or default

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 1 - Video 1' 2 1 2 'Group 1 - Video…
Luis Aguilar
  • 4,331
  • 6
  • 36
  • 55
1
vote
1 answer

Oracle SQL: Get top 3 results per "category"

I have an Oracle-db-table with multiple sportsteams and sports. Now I need to get the 3 teams (Or less if less than 3 teams) with the lowest id for each sport (based on their id). (In my example Soccer: Barcelona, Real Madrid, PSG / Hockey: NY…
SomeRandomGuy
  • 13
  • 2
1
vote
1 answer

Alternative to using max() in where-section?

I am pretty new to SQL and am trying to only display the one category which has the most expensive book. My first idea was to remove max(books.purchase_price) from select and add max(books.purchase_price) = books.purchase_price to the where section.…
Burton
  • 407
  • 1
  • 6
  • 19
1
vote
1 answer

Consult records for certain values of an attribute

I have a table with the following scheme (idMovie, genre, title, rating). How can I make a query that returns the ten films with the best rating for each genre? I think it could possibly be solved using 'ORDER BY' and also 'LIMIT' to get the top…
Donato
  • 73
  • 3
  • 6
1
vote
2 answers

Group and count by another columns value

I have a table like below: CREATE TABLE public.test_table ( "ID" serial PRIMARY KEY NOT NULL, "CID" integer NOT NULL, "SEG" integer NOT NULL, "DDN" character varying(3) NOT NULL ) and data looks like this: ID CID SEG DDN 1 1 1 …
James De Souza
  • 648
  • 1
  • 7
  • 20
1
vote
1 answer

MySQL: Greatest n per group with joins and conditions

Table Structure I have a table similar to the following: venues The following table describes a list of businesses id name 50 Nando's 60 KFC rewards The table describes a number of rewards, the venue it corresponds to and the number of…
Yahya Uddin
  • 26,997
  • 35
  • 140
  • 231
1 2 3
…
99 100