help with GROUP BY clause?

[Nathon Jones]

Hi,

In my events table (Access DB), I have a field called "eventdate" which is
in short date format.

I have created a Recordset in DW in which I do the following:

SELECT eventdate
FROM qryEvents GROUP BY eventdate

Now, this shows the dates in order, and they are grouped correctly.
However, when I create a dynamic hyperlink from this to a results page
(showing Events that occur on the selected date), it is working for all of
the March dates, but only 2 or 3 of the April dates!! It seems completely
random.

What makes it more confusing is that the Group By clause is picking dates
from the Events query table (qryEvents), which only lists events that are
actually happening.

So why would March work, but not April!

Perplexed and starting to become dis-illusioned with computers.
Nath.

[RichMcCrea]

this may be similar to a problem I had with order by datefield against a SQL
server database. My query was very much like yours select * from mytable
order by somedate the query correctly ordered the data in query analyzer but
when ran in an ASP.NET (C#) environment, it sorted dates like strings (sorting
1/1/2004, then 11/1/2004, 12/1/2004 and then 2/1/2004). Never figured out why
but the fix was using datepart(yy, mytable.somedate) ,datepart(m,
mytable.somedate) ,datepart(d, mytable.somedate) try pulling the date apart
by year, month and day in your query's group by and see if you get the correct
behavior. Hope this helps.

[middletree]

I'm not sure why you even need the GROUP BY with that statement.


"Nathon Jones" <nathonjonesNOT@kirkmoor.com> wrote in message
news:cvl5ma$9h9$1@forums.macromedia.com...

> Hi,
>
> In my events table (Access DB), I have a field called "eventdate" which is
> in short date format.
>
> I have created a Recordset in DW in which I do the following:
>
> SELECT eventdate
> FROM qryEvents GROUP BY eventdate
>
> Now, this shows the dates in order, and they are grouped correctly.
> However, when I create a dynamic hyperlink from this to a results page
> (showing Events that occur on the selected date), it is working for all of
> the March dates, but only 2 or 3 of the April dates!! It seems completely
> random.
>
> What makes it more confusing is that the Group By clause is picking dates
> from the Events query table (qryEvents), which only lists events that are
> actually happening.
>
> So why would March work, but not April!
>
> Perplexed and starting to become dis-illusioned with computers.
> Nath.
>
>
>

[Nathon Jones]

Hi,

Because several events appear on the same date, and I wanted to have a menu
that offered visitors the chance to select a date from a menu, or a list of
dates as links.

I therefore want to group the dates, so that the user only sees one of each
date, in order to make a selection.

I'm still stuggling with it.
Nath.

"middletree" <middletree@htomail.com> wrote in message
news:cvlcsc$kov$1@forums.macromedia.com...

> I'm not sure why you even need the GROUP BY with that statement.
>
>
> "Nathon Jones" <nathonjonesNOT@kirkmoor.com> wrote in message
> news:cvl5ma$9h9$1@forums.macromedia.com...

>> Hi,
>>
>> In my events table (Access DB), I have a field called "eventdate" which
>> is
>> in short date format.
>>
>> I have created a Recordset in DW in which I do the following:
>>
>> SELECT eventdate
>> FROM qryEvents GROUP BY eventdate
>>
>> Now, this shows the dates in order, and they are grouped correctly.
>> However, when I create a dynamic hyperlink from this to a results page
>> (showing Events that occur on the selected date), it is working for all
>> of
>> the March dates, but only 2 or 3 of the April dates!! It seems
>> completely
>> random.
>>
>> What makes it more confusing is that the Group By clause is picking dates
>> from the Events query table (qryEvents), which only lists events that are
>> actually happening.
>>
>> So why would March work, but not April!
>>
>> Perplexed and starting to become dis-illusioned with computers.
>> Nath.
>>
>>
>>

>
>

[middletree]

That's not what GROUP BY is for. If you want to order by date, then use the
ORDER BY clause. If you want to only have one of each date, when there are
multiple rows returned, then use DISTINCT, and it will only return one row
per date.

[Nathon Jones]

Hi,

I see. So does that become:

SELECT eventdate
FROM qryEvents DISTINCT eventdate

Thanks.
Nath.

"middletree" <middletree@htomail.com> wrote in message
news:cvle0n$me9$1@forums.macromedia.com...

> That's not what GROUP BY is for. If you want to order by date, then use
> the
> ORDER BY clause. If you want to only have one of each date, when there
> are
> multiple rows returned, then use DISTINCT, and it will only return one row
> per date.
>
>

[CMBergin]

SELECT DISTINCT blah blah

Now, go visit:
[url]www.sqlcourse.com[/url]
[url]www.sqlcourse2.com[/url]


"Nathon Jones" <nathonjonesNOT@kirkmoor.com> wrote in message
news:cvlen9$nfa$1@forums.macromedia.com...

> Hi,
>
> I see. So does that become:
>
> SELECT eventdate
> FROM qryEvents DISTINCT eventdate
>
> Thanks.
> Nath.
>
> "middletree" <middletree@htomail.com> wrote in message
> news:cvle0n$me9$1@forums.macromedia.com...

> > That's not what GROUP BY is for. If you want to order by date, then use
> > the
> > ORDER BY clause. If you want to only have one of each date, when there
> > are
> > multiple rows returned, then use DISTINCT, and it will only return one

row

> > per date.
> >
> >

>
>

[middletree]

What CMBergin says is correct. GROUP BY only comes into play if you're doing
math with one of the fields you are selecting.


Here's an example:

=======
SELECT T.TKT_TICKETID, MAX(H.TIMESTMP)AS HistTime
FROM dbo.TKT_TICKET T
WHERE T.StatusID = '"&strStatus&"' "
GROUP BY T.TKT_TICKETID
ORDER BY " & strSort & ""
=======

Note that I have a couple of variables in there, that start with str*.
Ignore that. Point is, the MAX I have on that one field means that I want
the date with the highest value (the latest date). Since MAX is considered a
math function, I have to tell the query to do a GROUP BY for all the other
fields. In this simple example, there only happens to be one other field.





"Nathon Jones" <nathonjonesNOT@kirkmoor.com> wrote in message
news:cvlen9$nfa$1@forums.macromedia.com...

> Hi,
>
> I see. So does that become:
>
> SELECT eventdate
> FROM qryEvents DISTINCT eventdate
>
> Thanks.
> Nath.
>
> "middletree" <middletree@htomail.com> wrote in message
> news:cvle0n$me9$1@forums.macromedia.com...

> > That's not what GROUP BY is for. If you want to order by date, then use
> > the
> > ORDER BY clause. If you want to only have one of each date, when there
> > are
> > multiple rows returned, then use DISTINCT, and it will only return one

row

> > per date.
> >
> >

>
>

[CMBergin]

Well, there is one other situation in which you'd use group by: when you
want to filter based on a group instead of individual records. For
instance, if you wanted all dates that had more than 1 event, you'd use
GROUP BY and HAVING to get the right results.

"middletree" <middletree@htomail.com> wrote in message
news:cvlj27$h2$1@forums.macromedia.com...

> What CMBergin says is correct. GROUP BY only comes into play if you're

doing

> math with one of the fields you are selecting.
>
>
> Here's an example:
>
> =======
> SELECT T.TKT_TICKETID, MAX(H.TIMESTMP)AS HistTime
> FROM dbo.TKT_TICKET T
> WHERE T.StatusID = '"&strStatus&"' "
> GROUP BY T.TKT_TICKETID
> ORDER BY " & strSort & ""
> =======
>
> Note that I have a couple of variables in there, that start with str*.
> Ignore that. Point is, the MAX I have on that one field means that I want
> the date with the highest value (the latest date). Since MAX is considered

a

> math function, I have to tell the query to do a GROUP BY for all the other
> fields. In this simple example, there only happens to be one other field.
>
>
>
>
>
> "Nathon Jones" <nathonjonesNOT@kirkmoor.com> wrote in message
> news:cvlen9$nfa$1@forums.macromedia.com...

> > Hi,
> >
> > I see. So does that become:
> >
> > SELECT eventdate
> > FROM qryEvents DISTINCT eventdate
> >
> > Thanks.
> > Nath.
> >
> > "middletree" <middletree@htomail.com> wrote in message
> > news:cvle0n$me9$1@forums.macromedia.com...

> > > That's not what GROUP BY is for. If you want to order by date, then

use

> > > the
> > > ORDER BY clause. If you want to only have one of each date, when

there

> > > are
> > > multiple rows returned, then use DISTINCT, and it will only return one

> row

> > > per date.
> > >
> > >

> >
> >

>
>

[middletree]

Good point.

Of course, Nathon's situation still doesn't call for GROUP BY

[CMBergin]

Nope.
I wonder if some database books teach GROUP BY as a method for getting
distinct records, though, because there have been a lot of people on the
forum that do that. Oh, well.

"middletree" <middletree@htomail.com> wrote in message
news:cvnahk$ffu$1@forums.macromedia.com...

> Good point.
>
> Of course, Nathon's situation still doesn't call for GROUP BY
>
>