CODEWITHSUNDEEP
mysql
Apic
Apic

Reputation: 61

How to select one row with lowest numeric value of one column?

What would be the query to select one row with lowest numeric value of one column?

Upvotes: 6

Views: 22206

Answers (5)

nycdan
nycdan

Reputation: 2839

To select all rows that match the minimum value for one column (in SQL Server)

SELECT T.col1, T.col2  
From Table T  
Where T.col1 = (select MIN(col1) from Table)

To select only one row, you could modify the first line to be:

Select Top 1 T.col1, T.col2

and you can always add 'Order by colx' to the end (multiple columns, comma separated work too).

Hope that helps.

Upvotes: 1

Mike Hillyer
Mike Hillyer

Reputation: 113

try this 

SELECT * FROM `table` ORDER BY `column` ASC LIMIT 1;

Upvotes: 5

ughoavgfhw
ughoavgfhw

Reputation: 39905

You can use it 

SELECT * FROM `YourTable` ORDER BY YourColumn LIMIT 1

Upvotes: 12

Sergio del Amo
Sergio del Amo

Reputation: 78096

I was looking for a simliar solution. I wanted the complete row with a minium value of a table group by another column. Here is the solution I came across. I sorted the table by the minium column with a order_by clause and the feed the subquery to a query with ORDER BY which catches the first row which appears which is the sorted row. 

id  bigint(20)      
commission  decimal(5,4)
door_price  decimal(19,2)
event_id    bigint(20)
min_commission  decimal(19,2)
price   decimal(19,2)
visible_to_public

SELECT * FROM (SELECT       
        price_bundle.id as id,
        event_id,
        price_bundle.price + (case when ((price_bundle.commission * price_bundle.price) >  price_bundle.min_commission) then (price_bundle.commission * price_bundle.price) else price_bundle.min_commission end) AS total
     FROM price_bundle
 WHERE price_bundle.visible_to_public = 1
 ORDER BY total asc
) AS price_bundle_order_by_total_price_asc GROUP BY event_id

Upvotes: 0

Jon Black
Jon Black

Reputation: 16559

select
 f.*
from
 foo f
inner join
(
 select min(id) as id from foo
) m on m.id = f.id;

Upvotes: 2

Related Questions