SEO

#1 (**permalink**) 03-17-2008, 06:13 AM

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.

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.

i don't know where is my thinking mistake.I really thought if I
make the group by month the 'group by' shouldn't be touched by days
but this is not true anyhow. I don't know, what to do.

thanks for help

bye
Matt

#2 (**permalink**) 03-17-2008, 06:13 AM

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.
--
Rik Wasmus

#3 (**permalink**) 03-17-2008, 06:13 AM

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.

i hope I formulate verything so that you can understand it.

thanks for help

bye
Matt

#4 (**permalink**) 03-17-2008, 06:13 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

still problems mit group by.