How can I select first record in a table?

What is the Select sintax to select only first record in a table
ordering by CREATION_DATE?
For example, the table is;

ID_PLANT NAME PRICE CREATION_DATE
1 Mammillaria 3,50 2007-01-06
2 Echinopsis 1,00 2007-01-07
3 Ferocactus 3,00 2007-01-08

The result must be:
3 Ferocactus 3,00 2007-01-08

Thanks! :)

> What is the Select sintax to select only first record in a table
> ordering by CREATION_DATE?

select FIELDLIST from TABLE_NAME order by FIELD_NAME LIMIT 1

HTH

--
Kev

Kevin Stone wrote:

>>What is the Select sintax to select only first record in a table
>>ordering by CREATION_DATE?
>
>
> select FIELDLIST from TABLE_NAME order by FIELD_NAME LIMIT 1
>
> HTH
>

Given:

[quote]
For example, the table is;

ID_PLANT NAME PRICE CREATION_DATE
1 Mammillaria 3,50 2007-01-06
2 Echinopsis 1,00 2007-01-07
3 Ferocactus 3,00 2007-01-08

The result must be:
3 Ferocactus 3,00 2007-01-08
[end-quote]

Given your data and the result you expect...

select ID_PLANT,NAME,PRICE,CREATION_DATE from tablex
where creation_date=(select max(creation_date));

But, if this is a bidding system, you do not want to use just the date - you
want to use TIMESTAMP. And based on your initial data set and the expected
results you posted, is that not the LAST record when ordered by date?

Therefore we can also make a minor correction to Kevin's query to:

select FIELDLIST from TABLE_NAME order by FIELD_NAME DESC LIMIT 1;

With a very small data set such as has been provided, the "order by ... limit 1"
may be faster than determining the max(date) using a seperate sub-select (see my
testing below). In a table with 1M+ records and there is an index on the date
field, then the max(date) may be faster as it does not need to do a sequential
read of the data as the "order by ... limit 1" would.

my test:

mysql> create table a (a int, b date,c char(3), char(3);

mysql> insert into a values (1,CURDATE()-3,'ABC','DEF');
Query OK, 1 row affected (0.37 sec)

mysql> insert into a values (2,CURDATE()-2,'ABD','DEG');
Query OK, 1 row affected (0.25 sec)

mysql> insert into a values (3,CURDATE()-1,'ABE','DEH');
Query OK, 1 row affected (0.24 sec)

mysql> select * from a;
+------+------------+------+------+
| a | b | c | d |
+------+------------+------+------+
| 1 | 2007-01-09 | ABC | DEF |
| 2 | 2007-01-10 | ABD | DEG |
| 3 | 2007-01-11 | ABE | DEH |
+------+------------+------+------+
3 rows in set (0.01 sec)



mysql> select a,c,d,b from a where b= (select max(b) from a);
+------+------+------+------------+
| a | c | d | b |
+------+------+------+------------+
| 3 | ABE | DEH | 2007-01-11 |
+------+------+------+------------+
1 row in set (0.52 sec)

mysql> select a,c,d,b from a order by b desc limit 1;
+------+------+------+------------+
| a | c | d | b |
+------+------+------+------------+
| 3 | ABE | DEH | 2007-01-11 |
+------+------+------+------------+
1 row in set (0.01 sec)

--
Michael Austin.
Database Consultant