On Sat, 15 Mar 2008 17:26:17 +0100, Matthias Roeder <info@vmind.de> wrote:

> Rik Wasmus schrieb:
>
>> On Sat, 15 Mar 2008 16:48:33 +0100, Matthias Roeder <info@vmind.de>
>> wrote:
>>
>>> Hi,
>>>
>>> I'm really lust. this is my query.
>>>
>>>
>>> SELECT DAYOFMONTH( `datum` ) AS Tag, MONTH( `datum` ) AS `Monat` ,
>>> YEAR( `datum` ) AS `Jahr`
>>> FROM `news`
>>> GROUP BY Jahr, Monat
>>> ORDER BY Jahr DESC , Monat DESC
>>>
>>> this is the table.
>>>
>>> CREATE TABLE `news2` (
>>> `newsid` int(11) NOT NULL auto_increment,
>>> `datum` date NOT NULL,
>>> `kommentar` text collate latin1_german2_ci NOT NULL,
>>> `ueberschrift` varchar(200) collate latin1_german2_ci NOT NULL
>>> default
>>> PRIMARY KEY (`newsid`)
>>> )
>>>
>>> the problem is that some months are work fine and some month are
>>> don't work. the qury just gives out the first one of the datas the
>>> rest
>>> is ignored.
>> Why do you only group by year & month if you want to have all dates?
>> Why the group by actually?
>> SELECT DAYOFMONTH( `datum` ) AS Tag, MONTH( `datum` ) AS `Monat` ,
>> YEAR( `datum` ) AS `Jahr`
>> FROM `news`
>> ORDER BY datum DESC
>>
>>> I don't understand what is going on. if I take the 'group by' part
>>> out off the query the query give me all out.there is not the mistake.
>> Yes that seems to be, at least as far as I understand what you want
>> in your result.. Could you formulate more carefully what data you
>> actually want in your result? Some sample data & desired outcome would
>> be most helpfull.
>
>
> I need the last day of every month. for the links in the archiv
>
> Archiv
>
> february (2008)
> january (2008)
> december(2007)
>
> it works fine with december. i get the last dayof the entrie it is
> 29.12.2007
>
> for january i only get the 1 day of the entrie the rest of the entries
> are ignored
> the same by feb.i get the first entry and rest are ignored.
>
> if I take the Group by out off the quuery, I get them all,
> but all 10 for jan and all of feb etc.,
>
> iwant it like I show it in the example.
> only one link per month. there should be the last day with an entry of
> the month.

SELECT DAYOFMONTH(MAX(`datum`)) AS Tag, MONTH( `datum` ) AS `Monat`,
YEAR( `datum` ) AS `Jahr`
FROM `news`
GROUP BY Jahr, Monat
ORDER BY Jahr DESC , Monat DESC
--
Rik Wasmus