what is the correct way to select the highest value from column not primary key

Question

I have a table (report) consist of several records and one of them about int values (column) I am trying to get the highest number of the fall_value column the id only primary key, the table as following:

id (P)	fall_value	date
3	1.2	2021-01-29
4	1.5	2021-01-30
5	1.6	2021-01-30
6	1	2021-01-31
7	5	2021-01-31
8	1.5	2021-01-31
9	1.5	2021-01-31
10	14	2021-01-31
11	15	2021-01-31

expected result: 15

I have tried the following inquiry:

SELECT max(fall_value) from report;

I got an unexpected result: 5

and also I got a message saying:

Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available

If the `id` is the primary key, why is the value repeated? Duplicates are not allowed. — Gordon Linoff, Jan 28 '21 at 19:00
the id is only the primary key the rest not. so the rest of the column values can be dublicted. — hallsskd, Jan 28 '21 at 19:02
re the message you got, see https://stackoverflow.com/q/18922503/17389 — ysth, Jan 28 '21 at 19:14

score 3 · Accepted Answer · edited Jan 28 '21 at 19:12

3

It sounds like fall_value is a string, not a number, and the string "5" is indeed greater than the string "15".

Try converting to a number. A convenient way is to use implicit conversion:

SELECT max(fall_value + 0) 
FROM report;

edited Jan 28 '21 at 19:12

ysth

92,097
6
117
207

answered Jan 28 '21 at 19:01

Gordon Linoff

1,198,228
53
572
709

what is the correct way to select the highest value from column not primary key

1 Answers1