python (12.9k questions)

javascript (9.2k questions)

reactjs (4.7k questions)

java (4.2k questions)

java (4.2k questions)

c# (3.5k questions)

c# (3.5k questions)

html (3.3k questions)

Questions - greatest-n-per-group

Select rows with Max(Column Value) for each unique combination of two other columns

I'm working with a sample table like below. A Dataset has multiple groups, and each time a write to the table occurs, the RunNumber increments for the dataset, along with data for each group and the t...
test-img

javarookie

mysql

sql

max

greatest-n-per-group

Votes: 0

Answers: 1

Latest Answer

In MySQL 5.x you can use a sub-query. SELECT * FROM your_table WHERE (`Group`, Dataset, RunNumber) IN ( SELECT `Group`, Dataset, MAX(RunNumber) AS MaxRunNumber FROM your_table GROUP BY `G...
test-img

LukStorms

Mysql get latest record/row based on another columns latest/max

I have a table like this id name version ref_id deleted 1 a 1 1 1 2 b 3 1 0 3 c 2 1 1 4 a 3 2 1 5 bb 1 2 0 6 cc 2 2 0 what I would like to achieve is to select the rows with the ...
test-img

Carlo Jimenez

mysql

sql

greatest-n-per-group

Votes: 0

Answers: 1

Latest Answer

In MySQL 8 or later you can use row_number window function: with cte as ( select *, row_number() over (partition by ref_id order by version desc) as rn from t ) select * from t where rn = 1 F...
test-img

Salman A

SQL Query slows down when more IDs are added

Hello so I am facing an issue with my SQL query. I need to get the average location for each ID on the maximal day. While the query below gives me the correct result it seems that the run time grows e...
test-img

Manuel

sql

sql-server

greatest-n-per-group

Votes: 0

Answers: 2

Latest Answer

You can give window functions a try: WITH cte AS ( SELECT *, RANK() OVER (PARTITION BY id ORDER BY date DESC) AS rn FROM t WHERE id IN ('1', '2', '3') ) SELECT id, max(date), min(...) FROM...
test-img

Salman A

How do I get rows with max values across two columns

I would like to know how to filter down a table by getting the max values from two columns. Here is a simplified input table. I want one row per person which has the max(year) and max(version) Nam...
test-img

Saif Ahmed

sql

aggregate-functions

greatest-n-per-group

netezza

Votes: 0

Answers: 1

Latest Answer

Performance aside, this generic SQL should do it: SELECT * FROM t WHERE NOT EXISTS ( -- select rows for that person-year-version -- where year is greater or (year is same and version is greate...
test-img

Salman A

Posts

Questions

Blogs

Jobs