MySQL Abfrage mit TOP und CONCAT

[Nemesis]

Hi,
ich habe hier 2 simple Tabellen, und möchte eine Abfrage darüber machen, die mir die 2 aktuellsten einträge ausgibt:

+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| ID    | int(11)       | NO   | PRI | NULL    | auto_increment |
| DATUM | date          | NO   |     | NULL    |                |
| NEWS  | varchar(7999) | NO   |     | NULL    |                |
+-------+---------------+------+-----+---------+----------------+

+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| Number | int(11)     | NO   | PRI | NULL    |       |
| Monat  | varchar(15) | NO   |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

mein Ansatz:

SELECT CONCAT(DATE_FORMAT(DATUM, '%d. '), Month.Monat, DATE_FORMAT(DATUM, ' %Y') ) AS Datum, News.NEWS as News from News INNER JOIN Month ON Date_format(DATUM, '%m.')=Month.Number ORDR BY 1 ASC;

aber dieser gibt mir alle Datensätze aus, ich hätte aber gerne nur die 2 aktuellsten.
Ich habe es mit TOP schon an allen möglichen Stellen probiert, aber es will nicht.

Wie macht man das ?

THX!

 

[sim4000]

Hänge an das Query mal

LIMIT 0,2

an.

 

[Nemesis]

das geht in die richtige richtung, nur hätte ich gerne nur die 5 letzten Datensätze ... die ersten 5 ist nicht schwehr mit limit.

Nachtrag:
Die Reihenfolge der Datensätze soll sich nicht ändern ... erster oben nach letzter unten.

 

[sim4000]

Hm okay... Kurzer Problemwechsel...

SELECT
   t.*
FROM
   test as t
   INNER JOIN (SELECT * FROM test ORDER BY date DESC LIMIT 0,5) AS vt ON vt.date=t.date
ORDER BY t.date ASC

Die ganze Tabelle sieht bei mir so aus:

+------------+----------+----------+---------------------+
| date       | incoming | outgoing | last_edit           |
+------------+----------+----------+---------------------+
| 2009-09-12 |   286.86 |    20.83 | 2009-09-12 23:58:02 |
| 2009-09-13 |  2979.04 |  1095.85 | 2009-09-13 23:58:01 |
| 2009-09-14 |    75.05 |   407.87 | 2009-09-14 23:58:03 |
| 2009-09-15 |    12.13 |   154.08 | 2009-09-15 23:58:02 |
| 2009-09-16 |     7.64 |   110.58 | 2009-09-16 23:58:07 |
| 2009-09-17 |     9.17 |   117.74 | 2009-09-17 23:58:02 |
| 2009-09-18 |    12.37 |   145.37 | 2009-09-18 23:58:07 |
| 2009-09-19 |        0 |        0 | 2009-09-19 23:54:01 |
| 2009-09-20 |    10.28 |   154.57 | 2009-09-20 23:58:02 |
| 2009-09-21 |    10.75 |   138.87 | 2009-09-21 23:58:01 |
| 2009-09-22 |     13.4 |   156.52 | 2009-09-22 23:58:02 |
| 2009-09-23 |    10.86 |   160.69 | 2009-09-23 23:58:02 |
| 2009-09-24 |    10.47 |    144.2 | 2009-09-24 23:58:02 |
| 2009-09-25 |     9.19 |   140.35 | 2009-09-25 23:58:02 |
| 2009-09-26 |     6.81 |    102.4 | 2009-09-26 23:58:01 |
| 2009-09-27 |     9.43 |    157.5 | 2009-09-27 23:58:02 |
| 2009-09-28 |     7.91 |     87.3 | 2009-09-28 23:28:02 |
| 2009-09-29 |    14.28 |   150.33 | 2009-09-29 23:58:02 |
| 2009-09-30 |     8.98 |   124.32 | 2009-09-30 23:58:02 |
| 2009-10-01 |     7.01 |   102.74 | 2009-10-01 23:58:02 |
| 2009-10-02 |    22.16 |   127.46 | 2009-10-02 23:58:02 |
| 2009-10-03 |    13.63 |   112.91 | 2009-10-03 23:58:02 |
| 2009-10-04 |      9.3 |   103.33 | 2009-10-04 23:58:02 |
| 2009-10-05 |    10.12 |   148.36 | 2009-10-05 23:58:02 |
| 2009-10-06 |     8.59 |   138.06 | 2009-10-06 23:58:02 |
| 2009-10-07 |       10 |   110.85 | 2009-10-07 23:58:03 |
| 2009-10-08 |    14.96 |    99.16 | 2009-10-08 23:58:02 |
| 2009-10-09 |   379.45 |    87.61 | 2009-10-09 23:58:01 |
| 2009-10-10 |  1425.92 |  1028.16 | 2009-10-10 23:58:01 |
| 2009-10-11 |     7.76 |     87.1 | 2009-10-11 23:58:02 |
| 2009-10-12 |    12.77 |    77.44 | 2009-10-12 23:58:02 |
| 2009-10-13 |    10.83 |    65.48 | 2009-10-13 23:58:02 |
| 2009-10-14 |    13.88 |    71.41 | 2009-10-14 23:58:02 |
| 2009-10-15 |     6.67 |     51.5 | 2009-10-15 23:58:02 |
| 2009-10-16 |    10.11 |   102.18 | 2009-10-16 23:58:02 |
| 2009-10-17 |     8.14 |    95.69 | 2009-10-17 23:58:02 |
| 2009-10-18 |    30.19 |   114.72 | 2009-10-18 23:58:01 |
| 2009-10-19 |    18.09 |      175 | 2009-10-19 23:58:02 |
| 2009-10-20 |     9.43 |     71.1 | 2009-10-20 23:58:02 |
| 2009-10-21 |    85.73 |    61.93 | 2009-10-21 23:58:02 |
| 2009-10-22 |     12.9 |    78.13 | 2009-10-22 23:58:01 |
| 2009-10-23 |    16.15 |    85.73 | 2009-10-23 23:58:02 |
| 2009-10-24 |    11.99 |   143.04 | 2009-10-24 23:58:01 |
| 2009-10-25 |    13.45 |   105.16 | 2009-10-25 22:28:02 |
+------------+----------+----------+---------------------+
44 rows in set (0.04 sec)

Ergebnis des Querys ist dann:

+------------+----------+----------+---------------------+
| date       | incoming | outgoing | last_edit           |
+------------+----------+----------+---------------------+
| 2009-10-21 |    85.73 |    61.93 | 2009-10-21 23:58:02 |
| 2009-10-22 |     12.9 |    78.13 | 2009-10-22 23:58:01 |
| 2009-10-23 |    16.15 |    85.73 | 2009-10-23 23:58:02 |
| 2009-10-24 |    11.99 |   143.04 | 2009-10-24 23:58:01 |
| 2009-10-25 |    13.45 |   105.16 | 2009-10-25 22:28:02 |
+------------+----------+----------+---------------------+
5 rows in set (0.00 sec)

//edit
Die Schriftart im Codetag ist total unglücklich. Devilz...?

 

[Nemesis]

ich habs so gelöst:

select * from 
(Select concat(Date_format(DATUM, '%d. '), Month.Monat, Date_format(DATUM, ' %Y') ) as Datum, News.NEWS as News 
from News INNER JOIN Month ON Date_format(DATUM, '%m.')=Month.Number 
order by 1 desc limit 2) as NEWS 
order by 1 asc;

dann kommt auch das richtige raus

 

[sim4000]

*grml*
Zu langsam gewesen...

 

[Nemesis]

trotzdem mal danke