Professional Web Applications Themes

How can I select first record in a table? - MySQL

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! :)...

  1. #1

    Default 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! :)

    rokko4ever Guest

  2. #2

    Default Re: How can I select first record in a table?

    > What is the Select sintax to select only first record in a table 

    select FIELDLIST from TABLE_NAME order by FIELD_NAME LIMIT 1

    HTH

    --
    Kev


    Kevin Guest

  3. #3

    Default Re: How can I select first record in a table?

    Kevin Stone wrote:
     
    >
    >
    > select FIELDLIST from TABLE_NAME order by FIELD_NAME LIMIT 1
    >
    > HTH
    >[/ref]

    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

    Michael Guest

Similar Threads

  1. Replies: 0
    Last Post: October 10th, 07:37 PM
  2. How to select record on one table but not the other
    By Rustywater in forum Coldfusion Database Access
    Replies: 1
    Last Post: September 11th, 11:15 AM
  3. Replies: 7
    Last Post: July 20th, 06:00 PM
  4. How can i retrieve record ONLY From 300 - 400 in amillion Record Table?
    By keonglah in forum Coldfusion Database Access
    Replies: 6
    Last Post: June 13th, 02:53 AM
  5. move record from 1 table to another table
    By Jason Frazer in forum Microsoft Access
    Replies: 6
    Last Post: August 4th, 02:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139