?
Solved

Help with SQL Query

Posted on 2009-02-14
18
Medium Priority
?
285 Views
Last Modified: 2013-12-12
Hi,

I am struggling with this query.

I have a table in database with three columns:
weight       uk       international
220              0      3.20
240              0      3.50
260              0      3.70
280              0      3.90

I need a query that can take the weight value and return me that row in table. For example if weight is 223, it shud return me row with 3.20

Thanks
0
Comment
Question by:khawaib
  • 5
  • 4
  • 4
  • +2
18 Comments
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 23640708
SELECT * FROM TableName WHERE weight='220'
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 23640711
Err sorry, typo...

SELECT * FROM TableName WHERE weight='223'
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23640714
if the data type of the field weight is numeric, do not use quotes.
for the rest, the query is correct, although you should avoid using SELECT *, instead specify the columns you need instead
SELECT uk, international FROM TableName WHERE weight= 220

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 25

Accepted Solution

by:
reb73 earned 800 total points
ID: 23640718
Try -
DECLARE @weight int
SELECT @weight = 220
 
SELECT * FROM table1
WHERE Weight =  (SELECT MAX(weight) from table1 WHERE Weight <= @weight)

Open in new window

0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 200 total points
ID: 23640754
....

SELECT TOP 1 weigth, uk, international FROM TableName WHERE weight <= 223 ORDER BY weigth DESC

Open in new window

0
 

Author Comment

by:khawaib
ID: 23640771
@angelIII and yodercm:
Queries wont work as there is no row 223 in table and I do not have value 220.

@reb73:
I will try your solution and seems it will work.
Can you please explain what does (SELECT MAX(weight) from table1 WHERE Weight <= @weight) do?

By the way I am using it with PHP.

Thanks
0
 

Expert Comment

by:Prasenjit_Dutta
ID: 23640776
Angels this answer is right

SELECT TOP 1 weigth, uk, international FROM TableName WHERE weight <= 223 ORDER BY weigth DESC
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 23640785
@angel ... if the data field is numeric, the quotes shouldn't affect anything.  Is there a reason you suggest it matters?

@khawaib ... angeliii is correct that it's slightly more computer-efficient to specify each field, I use * most of the time because it's more programmer-efficient, and because it doesn't require modification and introduces fewer bugs when you need an additional field.

Please remember to share the points among all the helpful/correct answers, not just give them to the first person who posted :)
0
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 23640794
Ah, I see, you stated that you wanted the row with value 223, which might not exist, not that you wanted the row with the next larger value.

Let me just correct the spelling in AngelIII's query

SELECT TOP 1 weight, uk, international FROM TableName WHERE weight <= 223 ORDER BY weight DESC
0
 
LVL 25

Expert Comment

by:reb73
ID: 23640799
"Can you please explain what does (SELECT MAX(weight) from table1 WHERE Weight <= @weight) do?"

This subquery locates the maximum value from the field weight where the value is less than or equal to the parameter(which seems to be your requirement) and passes this value to the outer query which fetches the correct row..
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23640820
>@angel ... if the data field is numeric, the quotes shouldn't affect anything.  Is there a reason you suggest it matters?

MySQL will refuse to work correctly, and Oracle will eventually ignore indexes on the field if you specify data with the wrong data type: '223' is text, and not numeric.

now, with the MySQL zone specified (which I had not seen before), the TOP syntax is wrong..
it would then be:

SELECT weight, uk, international FROM TableName WHERE weight <= 223 ORDER BY weight DESC LIMIT 1

Open in new window

0
 
LVL 25

Expert Comment

by:reb73
ID: 23640829
^ AngelIII

I wasn't sure if the TOP would work in MySQL either, that why I used the subquery in my first post.. ;-)
0
 
LVL 25

Expert Comment

by:reb73
ID: 23640853
TOP and LIMIT may be efficient if there is an index on the weight column, but will force a sort in the execution plan which can be more costly than a tablescan in a table with numerous rows..

I just ran an equivalent query in a table with 337000 rows, the execution plan (SQL 2000 though and not MYSQL) showed the TOP 1 syntax was 81% more costly than the subquery  syntax..
0
 

Author Comment

by:khawaib
ID: 23640880
Query with TOP was returning me empty.


This seems to be working for me. Will close the question after more teting.
$query= "SELECT * FROM `postage` WHERE `weight` = (SELECT MAX(weight) from `postage` WHERE weight <= '".$weight_total."')";

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23641023
TOP cannot work on mySQL. so, please also try my version with LIMIT.

but of course, both versions will profit from an index on the weight column
0
 

Author Comment

by:khawaib
ID: 23641206
Sorry my skills are not very good with SQL. By index you mean making wight primary key?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23642100
>By index you mean making wight primary key?
no, index means index.
"primary key" is a special form of index, aka a unique index (unique constraint).

now, in mysql, when you say "key", that's an index.
so, a "key" is not a primary key.


0
 

Author Comment

by:khawaib
ID: 23642236
thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question