Geek Answers Handbook

How to select the last rows in MySQL?

I want to select the last rows of a specific table containing 5 elements in MySQL. The table looks like this:

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

The data looks something like this:

|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 the select * WHERE to = 'user01'latest data (perhaps by "id" or "timestamp"). "From" can be numerous, but each of the "data" can appear only once.

In any case, 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

Can this be done? Thanks for taking the time to my question :)

+5
source share
7 answers
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
             )

, to, from timestamp.

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

... .

+2

, SELECT DISTINCT user_from FROM table WHERE user_to='user1' ORDER BY id DESC?

DISTINCT user_from.

+2

:

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

, , "" .

+1

from:

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

( , MAX MAX )

+1

:

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

, ,

SELECT * FROM <table> 
WHERE 
    CONCAT([to], [from], [timestamp]) 
    IN 
    (
     SELECT CONCAT([to], [from], MAX([timestamp])) FROM <table> 
     WHERE [to] = 'user01' GROUP BY [from]
    )
+1
SELECT * FROM `table_name` WHERE `to` = 'user01' ORDER BY `timestamp` DESC LIMIT 0,1
-1

More articles:


All Articles