Stack Overflow
Stack Overflow

Questions tagged [groupwise-maximum]

Questions regarding the common SQL problem selecting records holding the group-wise maximum of a certain column. Example: "For each article, find the dealer or dealers with the most expensive price."

212 questions
2
votes
3 answers

Selecting entries with nearest value less than given value

I want to select the entries with the nearest value less than a given value with SQL (No PL/SQL! or such thing). I got this far: select max(RUN_ID) from RUN_TABLE where KEY = 'TEST#33' and RUN_ID < 3 This returns the row with the highest value less…
MMM
  • 373
  • 1
  • 4
  • 12
2
votes
5 answers

SQL query for finding representative rows in a table

Let's say I have a payments table like so: CREATE TABLE Payments ( PaymentID INT, CustomerID INT, Value INT, PaidOn DATE ); INSERT INTO Payments VALUES (1, 1, 5, '2000-01-01'), (2, 1, 10, '2000-02-01'), (3, 2, 10, '2000-01-02'), (4, 2,…
Alex
  • 4,122
  • 5
  • 34
  • 40
2
votes
1 answer

Getting this group by and max query to work

I have the following table players with…
Bosiwow
  • 2,025
  • 3
  • 28
  • 46
2
votes
4 answers

Select only newest records from table and make this FAST, how?

Good day, I have a question I'm struggling with a lot, hope somebody already found a clever solution to this (I use MySQL). I have table like this: Table `log` ---------- id inserted message user_id My goal is to select last inserted record for…
artvolk
  • 9,448
  • 11
  • 56
  • 85
2
votes
1 answer

MySQL Returning Incorrect Values using Group Bu

I am trying to select the most recent RowID from an activities table for each user in an Account. But everytime i run the query, it returns the correct RowID but the other information seems to be selected at random as the Subject and Date are…
user2054962
  • 31
  • 1
2
votes
1 answer

Get 10 latest post

I prepare a sql query to get the most current topics of a simple forum, and I have the following provision: CREATE TABLE IF NOT EXISTS `f_categoria` ( `id_categoria` int(4) NOT NULL, `name` varchar(20) NOT NULL, `desc` varchar(50) NOT NULL, …
topolo
  • 23
  • 3
1
vote
3 answers

Complicated SQL join query - get newest row

My MySQL db has a table (cfg) with 4 fields: id, name, data, and location This one table is to service several subdomains (location). I need to get a list of the newest rows for each name for a given subdomain (location). Example data id name …
Nathan
  • 1,241
  • 3
  • 12
  • 17
1
vote
2 answers

Groupwise maximum record lookup for contracts and latest status

I'm looking for help as I can't seem to get a handle on a SQL query. I have two tables: Contracts - where the list of contracts is kept along with the associated partners CREATE TABLE `contracts` ( `id` varchar(5) NOT NULL, `partner`…
Paweł Banaś
  • 15
  • 6
1
vote
1 answer

Laravel Eloquent/MySQL how to get one last year records from last entry of each parent record?

Suppose I have the following relationship in a legacy system, that could not change it. provinces table (hasMany districts) districts table (hasMany villages) villages table (hasMany hospitals) hospital_types table (hasOne hospitals) hospitals…
jones
  • 1,423
  • 3
  • 35
  • 76
1
vote
1 answer

Is it possible to get only rn = 1 in ROW_NUMBER without getting the rest of the rn to increase query speed?

So I have a query: (ROW_NUMBER() OVER (PARTITION BY itm.storeID, itm.skuID ORDER BY itm.effectiveDate DESC)) AS rn, Then what I would do is use foreach in the the table then scan if rn is equal to 1, if equal then I will add to a value, so…
jove0610
  • 664
  • 1
  • 8
  • 16
1
vote
3 answers

Mysql query performing very slowly

I have a query I'm using in my CodeIgniter model to fetch the count of listings of products between particular days. This works fine when there are less items in my table, but there are more than 100,000 entries in my table and to just get the…
JJM50
  • 467
  • 1
  • 7
  • 20
1
vote
1 answer

Query (HAVING Clause) does not behave the same between Mysql Version 5.7 and 8.0

So recently I was asked to upgrade Mysql Version from 5.7 to Mysql 8. After the upgrade, I noticed a strange behavior where the results produced is completely different in both version. Below are the structure and sample data. Create Table DROP…
Ryan Tan
  • 344
  • 2
  • 11
1
vote
2 answers

how to retrieve latest data from mysql table out of duplicate records

I am trying to retrieve latest data from my sql table for each record. There will be duplicate data for each record with some data changes. I need to retrieve the latest timestamped data. Can someone suggest which is the optimum solution in terms of…
Keerthi
  • 15
  • 3
1
vote
2 answers

Select first largest value using TOP 1 in MySQL?

I'll try to explain by example what I want to achieve in MySQL. I have a table looking like this: pricelist_id valid_from 1 1610665200000 //15 Jan 2021 2 1610751600000 //16 Jan 2021 3 1610838000000 //17 Jan…
Wrapper
  • 794
  • 10
  • 25
1
vote
3 answers

How select greatest rows in each types

I have a table with PK("object", "type", "mid") and this structure: +-----+----------+-------+---------+-----+ | type| object | value | preview | mid | +-----+----------+-------+---------+-----+ | t1 | uid-0001 | 2 | p1 | 1 | | t1 |…
Сергей Рыбаков
  • 29
  • 3
1 2
3
…
14 15