only smallest

[BubikolRamios]

if my query returns

col1
1
1
3
3


or


col1
3
3
5
5


how to extra limit result to: only records that have smallest value at 'col1' ?
Obviously I dont know in advance which will be the smallest value.

[Fumigator]

min()

[Old Pedant]

Fumigator meant:
SELECT MIN(col1) FROM yourtable


But...

But all that does is indeed return one record with that smallest of values.

You *said*
how to extra limit result to: only records that have smallest value at 'col1' ?

So I'm guessing that you mean you want *ALL* records that have that same smallest value??

If so:

SELECT * FROM table
WHERE col1 = ( SELECT MIN(col1) FROM table )

[BubikolRamios]

so I was playing around with one complicated query, and what popped out !?

if I have data:


id id_1
1 25
2 25
3 25



select id, id_1 from tab
where id_1 = 25
group by id_1


result is:


1 25


so I get min automaticaly with this, should I count that this
will work for ewer or this is some internal order by mysql thing ?

[Old Pedant]

First of all, what "popped" up was *NOT* just what you showed.

If you had dumped *ALL* the records returned from that query, you would surely have seen
1 25
2 25
3 25

Yes, it is just a semi-coincidence. I am assuming that the ID field in that table is an auto_number field, yes? And also the primary key? So when you don't give an ORDER BY, MySQL *tends* to return records in primary key order.

But there is NO GUARANTEE that this will happen. You should *always* use an ORDER BY clause if you need anything from a query in a given order.

Note: Your GROUP BY clause is doing nothing whatsoever in that query, since you already limited the selection to only those records with the given id_1 value.

Now...

Yes, actually, a variation on that query makes much more sense then either my answer or Fumigator's:

SELECT id, id_1
FROM tab
WHERE id_1 = 25
ORDER BY id
LIMIT 1;

Now you indeed have an explicit ORDER BY and don't need to rely on a possibly quirky ordering. And the LIMIT 1 means you indeed will get only one record, the one with the lowest id value.