CODEWITHSUNDEEP
mysql
Will Jamieson
Will Jamieson

Reputation: 926

Get second smallest number(s) in sql column

I currently have the code below and it works for getting me the 2 smallest number, but I want to get all of the 2nd smallest numbers and link them to their name as opposed to just one of them. lets say the numbers in the tables was made up of this:

Name| number
----|------
w     2 
a     8 
s     2
e     2
z     3

I would want to get 

w 2
s 2
e 2

and now I am just getting w 2

SELECT MAX(col) FROM table WHERE col NOT IN (SELECT MAX(col) FROM table);

Upvotes: 1

Views: 3191

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

If this code gets you the second smallest number (what you want):

SELECT MAX(col) FROM table WHERE col NOT IN (SELECT MAX(col) FROM table);

Then simply do:

select *
from table
where col = (SELECT MAX(col) FROM table WHERE col NOT IN (SELECT MAX(col) FROM table));

Upvotes: 2

Mihai
Mihai

Reputation: 26784

http://sqlfiddle.com/#!2/9ca94/11

SELECT name,(SELECT MAX(gunsize) FROM ships s2 
WHERE s2.Name=s.name and gunsize not in (select max(gunsize) from ships))as gunsizes
FROM ships s

Something like this?

Upvotes: 0

Minoru
Minoru

Reputation: 1730

I didn't understand well, but if you're using LIMIT 1,1, you will only get 1 row or none. Just use what @491243 commented on your question.

SELECT * FROM tablename WHERE number = (SELECT MIN(number) FROM tableName);

Forget it, now I understood the question. Try this:

SELECT * FROM tablename WHERE number =
    (SELECT number FROM tablename WHERE number != 
        (SELECT MIN(number) FROM tablename) ORDER BY number LIMIT 1);

Hope this helps.

EDIT: Using the SQLFiddle table:

SELECT * FROM ships WHERE gunsize = (
    SELECT gunsize FROM ships WHERE gunsize != 
        (SELECT MIN(gunsize) FROM ships) ORDER BY gunsize LIMIT 1);

Upvotes: 0

Related Questions