How do I select the latest rows in MySQL?

I want to select the latest rows of a specific table that has 5 items in MySQL. The table looks like:

• id (auto increase)
• to
• from
• time stamp
• text

The data is something like:

|id | to     | from   | time stamp | text
| 1 | user01 | user02 | 2011-09-01 | text1
| 2 | user01 | user02 | 2011-09-02 | text2
| 3 | user02 | user01 | 2011-09-02 | text3
| 4 | user01 | user03 | 2011-09-03 | text4
| 5 | user01 | user04 | 2011-09-03 | text5
| 6 | user01 | user03 | 2011-09-04 | text6
| 7 | user03 | user01 | 2011-09-05 | text7

I want to select * WHERE to = 'user01' and the latest开发者_如何学C data (maybe by "id" or "time stamp"). The "from" can be numerous but each same "from" data can appear just once.

Anyway, the selected data will be:

| 2 | user01 | user02 | 2011-09-02 | text2
| 5 | user01 | user04 | 2011-09-03 | text5
| 6 | user01 | user03 | 2011-09-04 | text6

Could it be done? Thanks for taking time reading my question :)

SELECT t.* 
FROM
      TableX AS t
  JOIN
      ( SELECT DISTINCT `from` AS f
        FROM TableX
        WHERE `to` = 'user01'
      ) AS df
    ON 
      t.id = ( SELECT tt.id
               FROM TableX AS tt
               WHERE tt.`to` = 'user01'
                 AND tt.`from` = df.f
               ORDER BY tt.`timestamp` DESC
               LIMIT 1
             )

It's better to avoid naming tables and fields with keywords like to, from and timestamp.

SELECT * FROM tablename WHERE to = 'user01' ORDER BY timestamp DESC LIMIT 1

...will give you the newest entry.

I think you're looking for SELECT DISTINCT user_from FROM table WHERE user_to='user1' ORDER BY id DESC?

DISTINCT will only return ONE row for each user_from value.

If you would like the latest row per unique from user:

SELECT `from`, MAX(`id`), `to` FROM `tablename` WHERE `to`='user01' GROUP BY `from`

(I think getting the MAX of id might be faster than MAX of time stamp)

You can do this like this:

SELECT to, from, max(timestamp) FROM <table> WHERE to = 'user01' GROUP BY from

To get the associated text also you can also do it like this, but it is not very efficient

SELECT * FROM <table> 
WHERE 
    CONCAT([to], [from], [timestamp]) 
    IN 
    (
     SELECT CONCAT([to], [from], MAX([timestamp])) FROM <table> 
     WHERE [to] = 'user01' GROUP BY [from]
    )

One possibility would be something like that:

SELECT * FROM tbl WHERE id IN (
    SELECT MAX(id) FROM tbl
    WHERE to = 'user01'
    GROUP BY from
);

But I do not know if it matches your requirement, that each "from" could only appear once.

SELECT * FROM `table_name` WHERE `to` = 'user01' ORDER BY `timestamp` DESC LIMIT 0,1