SQL why can't I simply select max

Question

I don't understand why I can't I simply SELECT MAX(Weight).

This is my code:

CREATE TABLE Table1
(
    "ID" varchar(8), 
    "ItemName" varchar(12), 
    "Floor" varchar(11), 
    "Weight" int
);
    
INSERT INTO Table1 ("ID", "ItemName", "Floor", "Weight")
VALUES
    ('6DC85C23', 'Item one', 'first floor', 0),
    ('6DC85C24', 'Item two', 'first floor', 2),
    ('6DC85C25', 'item ten', 'first floor', 3),
    ('B2B4DC7B', 'item another', 'first floor', 0);

SELECT
    tb.Id, tb.ItemName, tb.Floor, MAX(Weight) 
FROM
    Table1 tb
GROUP BY
    tb.Id, tb.ItemName, tb.Floor

I need to return only rows that has MAX weight for every Id. It should return just two rows like this:

Id	ItemName	Floor	(No column name)
6DC85C25	item ten	first floor	3
B2B4DC7B	item another	first floor	0

But it returns this data instead - why? What am I doing wrong it just a simple table! Why max function does not work for me as expected?

Id	ItemName	Floor	(No column name)
6DC85C23	Item one	first floor	0
6DC85C23	Item two	first floor	2
6DC85C23	Item ten	first floor	3
B2B4DC7B	item another	first floor	0

Here is fiddle

I also tried this

select top 1 with ties
   ID
  ,ItemName
  ,Floor
  ,Weight
from Table1
order by row_number() over (partition by ID order by Weight desc)

From this question, but this does not work, I don't know why.

Your four rows have 4 distinct IDs. It's not clear why or how you think there should only be 2 rows when you say you want the max for each ID. You need to articulate clearly to us as well as to the database system what rule you use to combine those distinct IDs. — Damien_The_Unbeliever, Aug 09 '22 at 07:00
Yes I have 2 Id for 4 items. But each ID has item with max weight for ex. 6DC85C25 has 3. So I need to select only raws that have max weight for each id — Syngltake, Aug 09 '22 at 07:02
No, read your own question again. You have 4 IDs present in your sample data. If you consider 6DC85C25 and 6DC85C23, for example, to be equal, please *explain* the rule, because most people would consider them to be different. — Damien_The_Unbeliever, Aug 09 '22 at 07:06
Ooops. Ive prepared sample data in excel so it incremented ids. Thanx a lot. Now The code Ive taken from another question is working. But why cant i just use MAX(Weight) — Syngltake, Aug 09 '22 at 07:14
Because the MAX query doesn't do anything to distinguish the `Id` column (by which you wish to partition the data) from the `ItemName` and `Floor` columns (that you in fact want to come from the same row that produced the MAX value, and ignoring for the moment that there might be multiple rows with the MAX value) — Damien_The_Unbeliever, Aug 09 '22 at 07:20

SQL why can't I simply select max

0 Answers0