SQL - getting a specific value from a row and then using that value

Question

I have a table of data with a Name, a Grade, and a Point. For example, "John" got the grade B, which has a point value of 3.0.

How can I select entries that are less than a grade of "B"?

So I somehow need to do something like this

Value = select Point from MyTable where Grade="B"

and then

select * from MyTable where Point < value

but obviously SQL has to be one statement...

score 2 · Answer 1 · answered Oct 24 '12 at 21:23

2

You can nest selects and add a subquery:

SELECT realtable.* 
FROM (SELECT Point FROM MyTable WHERE Grade="B" LIMIT 1) subquery, MyTable realtable
WHERE subquery.Point > realtable.Point

answered Oct 24 '12 at 21:23

Abraham P

this is also very helpful. although I have to give the answer to the other one since it was first and did answer the question. THANKS!! – CodeGuy Oct 24 '12 at 21:40

Robert · Accepted Answer · 2012-10-24T21:31:20.293

1

Try it use in subquery as below:

select * 
from MyTable 
where Point < (select Point 
               from MyTable 
               where Grade="B")

but if you subquery returns more than one row try to use aggregate funcion for example min

select * 
from MyTable 
where Point < (select min(Point)
               from MyTable 
               where Grade="B")

or with LIMIT and join:

select * 
from MyTable mt
join (select Point from MyTable 
      where Grade="B"
      order by Point
      LIMIT 1) mt2 on mt.Point < mt2.Point

edited Oct 24 '12 at 21:31

answered Oct 24 '12 at 21:15

Robert

2

Won't work. You need to limit the subquery to one only value. – Clodoaldo Neto Oct 24 '12 at 21:17
The aggregate works but is expensive. Just add a `limit 1` to the subquery. – Clodoaldo Neto Oct 24 '12 at 21:20
@Clodoaldo [Here](http://stackoverflow.com/questions/2856397/mysql-subquery-limit) says you can. – Robert Oct 24 '12 at 21:23
That is a subquery in a join. In the case here it won't do. – Clodoaldo Neto Oct 24 '12 at 21:24

2 Answers2