CODEWITHSUNDEEP
sqlsqlite
BoyUnderTheMoon
BoyUnderTheMoon

Reputation: 771

SQL Selecting the minimum value after a WHERE clause

I'm a beginner to SQL so this is quite possibly a very simple question. I have a table with postage service information like so:

ID Service              Max_Weight Cost
----------------------------------------
1  SecondClassStandard  0.10       0.95

2  SecondClassStandard  0.25       1.19

3  SecondClassStandard  0.50       1.51

4  SecondClassStandard  0.75       2.05

5  SecondClassStandard  1.00       2.80

How can I perform the following query (as an example):

SELECT * FROM table WHERE Service = 'SecondClassStandard' AND Max_Weight >= 0.075;

and from the result, get one service that will satisfy the weight requirement. In other words, select the one result that is suitable - for example, from the query above, it should only return:

1|SecondClassStandard|0.10|0.95

However, if I was to do:

SELECT * FROM table WHERE Service = 'SecondClassStandard' AND Max_Weight >= 0.105;

It should return:

2|SecondClassStandard|0.25|1.19

Upvotes: 1

Views: 81

Answers (3)

eddiecjc
eddiecjc

Reputation: 222

SELECT * FROM table 
WHERE Service = 'SecondClassStandard' 
AND Max_Weight >= 0.075 
ORDER BY Max_Weight ASC Limit 1;

Upvotes: 0

Rahul
Rahul

Reputation: 77876

I think you meant to use LIMIT clause like

SELECT * FROM table 
WHERE Service = 'SecondClassStandard' 
AND Max_Weight >= 0.075 
LIMIT 1;

Upvotes: 2

sqluser
sqluser

Reputation: 5672

You need to use LIMIT to just get the first row

SELECT * FROM table WHERE Service = 'SecondClassStandard' AND Max_Weight >= 0.075 LIMIT 1;

SELECT * FROM table WHERE Service = 'SecondClassStandard' AND Max_Weight >= 0.105 LIMIT 1;

Upvotes: 1

Related Questions