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
2 answers

MySQL Select from Multiple Tables and most recent record

I'm having issues with a select query and can't quite figure out how to fix. I have two tables: TABLE_students |--------|------------|--------| | STU_ID | EMAIL | NAME | |--------|------------|--------| | 1 | a@e.com | Bob | | …
Jason
  • 1,105
  • 3
  • 16
  • 30
1
vote
3 answers

Select Only the row with smallest id

suppose I have the following table X: Id Type Name 1 1 Jane 2 2 Mary 3 3 Rose 4 4 Rachel 5 4 Darren 6 4 Jay What will be the select statement to generate: Id Type Name 1 1 Jane 2 2 Mary 3 3 Rose 4 4 …
yeungcase
  • 383
  • 2
  • 3
  • 12
1
vote
3 answers

SQL query not returning the max date

Can someone please review my query and let me know why is it not pulling the max(latest date)? I want the cst_at by lastest dts_srv_ts(datetime). Here is my code: SELECT dbo.TPB900_CHG_CODE_MST.chg_cod_ext_id,…
Milan Pk
  • 77
  • 5
1
vote
2 answers

Select most recent record grouped by 3 columns

I am trying to return the price of the most recent record grouped by ItemNum and FeeSched, Customer can be eliminated. I am having trouble understanding how I can do that reasonably. The issue is that I am joining about 5 tables containing hundreds…
Alan
  • 2,046
  • 2
  • 20
  • 43
1
vote
0 answers

Select unique columns with max value

I'm sorry for a bit fuzzy title. Don't know how to describe it better. I have table like this CREATE TABLE foo ( id INT NOT NULL, version INT NOT NULL, data VARCHAR(200) NULL, PRIMARY KEY (id, version) ); With data…
lmaq
  • 71
  • 3
1
vote
1 answer

Fetch latest row for a specific identifier

I have a table that looks like this ID | identifier | data | created_at ------------------------------------ 1 | 500 | test1 | 2011-08-30 15:27:29 2 | 501 | test1 | 2011-08-30 15:27:29 3 | 500 | test2 | 2011-08-30 15:28:29 4…
TheNiceGuy
  • 3,462
  • 8
  • 34
  • 64
1
vote
2 answers

Constructing an Oracle SQL query

Given a data table like: +-----+-------------+--------+ | id | end | status | +-----+-------------+--------+ | a | 07-FEB-2018 | 1 | | a | 08-FEB-2018 | 2 | | a | 08-FEB-2018 | 3 | | b | 06-MAR-2018 | 2 | | b |…
Sam King
  • 90
  • 1
  • 5
1
vote
2 answers

Finding row of "duplicate" data with greatest value

I have a table setup as follows: Key || Code || Date 5 2 2018 5 1 2017 8 1 2018 8 2 2017 I need to retrieve only the key and code where: Code=2 AND Date > the other record's date So based on this data…
user3007447
  • 380
  • 3
  • 15
1
vote
1 answer

Can't figure out correct SQL query for historical table records

I'm trying for hours to find out the correct SQL query to select the latest historcal record from a table (in MySQL). In my application I'd like to keep a history of every data modification. So my idea was, instead to make an UPDATE to an existing…
Lars
  • 13
  • 3
1
vote
3 answers

Retrieve the record with the newest change per group

I've got a table locations: user | timestamp | state | other_data ------------------------------------- 1 100 1 some_data 1 200 1 some_data 1 300 0 some_data 1 400 0 …
dv02
  • 333
  • 1
  • 6
  • 17
1
vote
1 answer

mysql get user highest bid from a table

I have a table of bids for an auction system: BIDS id user_id bid auction_id 1 3 1 1 2 4 2 1 3 4 1 1 4 3 3 1 5 3 2 1 6 3 2 1 I need to get the…
karlosuccess
  • 843
  • 1
  • 9
  • 25
1
vote
1 answer

Need advice with a Query to list Top 5 rated games

Hi i am helping my friend create a website that shows ratings for games. My table and columns are: Games game_id title genre Ratings user_id game_id rating timestamp Users user_id gender age Occupation zip code I would need help creating a SQL…
Jessica
  • 15
  • 3
1
vote
0 answers

get top n most viewed photos in a given period of time

I want to display 3 most viewed photos for 7 days. Let's say that I have 4 images, A, B, C and D. Number of total views: A - 300 B - 305 C - 310 D - 400 Number of views in the last 7 days from now: A - 100 B - 90 C - 95 D - 45 So the 3 most viewed…
feerlay
  • 2,286
  • 5
  • 23
  • 47
1
vote
0 answers

SELECT only TOP value using WHERE clause

In my SQLFiddle Here I'm trying to SELECT ROWs WHERE i.id = 1 with the highest value, Then i.id = 2 and so on. So here in my Query SELECT i.`item`, v.`nom`, v.`value` FROM `items` i LEFT JOIN `values` v ON v.`item_id` = i.`id` WHERE i.`id` IS NOT…
Toleo
  • 764
  • 1
  • 5
  • 19
1
vote
1 answer

Fetch records with MAX values between specific timestamp in accurate MAX order

I am having some challenges spooling out some database records. I need to get the rows with MAX value for a specific column and these records must fall between two timestamp values. Here is the SQL query SELECT id, MAX(amount), created FROM…
Ous
  • 71
  • 1
  • 8
1 2 3
…
99 100