SQL grouping

Report · Dec 16, 2007

I have a table that has data like this:

EVENT - DATE
meeting - 12/1/07
party - 12/4/07
meeting - 12/9/07

I want to query the table and return all items by event type, sorted by
date.

So, the above should return:

meeting - 12/1/07
meeting - 12/9/07
party - 12/4/07

How can I do that? I'm using TSQL and thought the group by clause is what I
need, but that gives me all sorts of errors unless I include every SELECT
column in my GROUP BY statement, which seems odd and messy.

Am I missing something obvious?

-Darrel

Report · Dec 16, 2007

.oO(Darrel)

>I have a table that has data like this:
>
>EVENT - DATE
>meeting - 12/1/07
>party - 12/4/07
>meeting - 12/9/07
>
>I want to query the table and return all items by event type, sorted by
>date.
>
>So, the above should return:
>
>meeting - 12/1/07
>meeting - 12/9/07
>party - 12/4/07
>
>How can I do that?

Maybe I'm missing something, but what about a simple

ORDER BY type, date

?

Micha

Report · Dec 17, 2007

You will get errors if you do not include all the columns from your SELECT
in the GROUP BY. That is the nature of a GROUP BY. The only columns you need
not include in the GROUP BY are aggregate functions like SUM(), COUNT() etc.

Pat.

"Darrel" <notreal@nospam.com> wrote in message
news:fk4de2$558$1@forums.macromedia.com...
>I have a table that has data like this:
>
> EVENT - DATE
> meeting - 12/1/07
> party - 12/4/07
> meeting - 12/9/07
>
> I want to query the table and return all items by event type, sorted by
> date.
>
> So, the above should return:
>
> meeting - 12/1/07
> meeting - 12/9/07
> party - 12/4/07
>
> How can I do that? I'm using TSQL and thought the group by clause is what
> I need, but that gives me all sorts of errors unless I include every
> SELECT column in my GROUP BY statement, which seems odd and messy.
>
> Am I missing something obvious?
>
> -Darrel
>

Report · Dec 17, 2007

> Maybe I'm missing something, but what about a simple
>
> ORDER BY type, date

That's what I'm doing, and is probably what I'll stick with.

The catch is that I want the types ordered by the dates within. Does that
make sense?

So if there is an event next month for type A, and one for next week for
type B, I want the types sorted as such:

B
- event next week
- event whenever
A
- event next month
- event whenever
- event whenever

-Darrel

Report · Dec 17, 2007

> You will get errors if you do not include all the columns from your SELECT
> in the GROUP BY. That is the nature of a GROUP BY. The only columns you
> need not include in the GROUP BY are aggregate functions like SUM(),
> COUNT() etc.

The problem with that, though, is that I have TEXT fields in the DB that
then gives me errors (can't group by text fields).

-Darrel

Report · Dec 17, 2007

.oO(darrel)

>> Maybe I'm missing something, but what about a simple
>>
>> ORDER BY type, date
>
>That's what I'm doing, and is probably what I'll stick with.
>
>The catch is that I want the types ordered by the dates within. Does that
>make sense?
>
>So if there is an event next month for type A, and one for next week for
>type B, I want the types sorted as such:
>
>B
> - event next week
> - event whenever
>A
> - event next month
> - event whenever
> - event whenever

OK, so "inside" of each type they are ordered by date, but the types
themselves should also be in order with the most recent events coming
first? Or in other words: The most recent event of each type should
determine the order in which the event types are shown.

Hmm, I could probably do it with two queries or maybe a subquery ...
It takes some thinking. ;)

Micha

Report · Dec 17, 2007

.oO(Michael Fesser)

>OK, so "inside" of each type they are ordered by date, but the types
>themselves should also be in order with the most recent events coming
>first? Or in other words: The most recent event of each type should
>determine the order in which the event types are shown.
>
>Hmm, I could probably do it with two queries or maybe a subquery ...
>It takes some thinking. ;)

I have a working example for that ... Only problem is: it's MySQL and
will probably not work that way on your server because of the GROUP BY
issue with text fields, but anyway:

mysql> SELECT * FROM events;
+------+------------+
| type | date |
+------+------------+
| A | 2008-01-10 |
| A | 2008-02-20 |
| A | 2008-03-30 |
| B | 2007-12-31 |
| B | 2008-02-10 |
| C | 2008-01-05 |
+------+------------+
6 rows in set (0.00 sec)

mysql> SELECT e1.type, e1.date
-> FROM events e1
-> INNER JOIN events e2 USING (type)
-> GROUP BY e1.type, e1.date
-> ORDER BY MIN(e2.date), e1.date;
+------+------------+
| type | date |
+------+------------+
| B | 2007-12-31 |
| B | 2008-02-10 |
| C | 2008-01-05 |
| A | 2008-01-10 |
| A | 2008-02-20 |
| A | 2008-03-30 |
+------+------------+
6 rows in set (0.00 sec)

The idea was simply to add a third column (not returned in the result)
to each record, which contains the minimum date for the record's event
type and functions as the primary sort criterion.

Micha

Report · Dec 17, 2007

> OK, so "inside" of each type they are ordered by date, but the types
> themselves should also be in order with the most recent events coming
> first? Or in other words: The most recent event of each type should
> determine the order in which the event types are shown.

Yes. Exactly.

> Hmm, I could probably do it with two queries or maybe a subquery ...
> It takes some thinking. ;)

Good. That means it's not as simple as I had thought and now don't feel so
dumb. ;o)

Thanks for the group-by example! Yea, not sure if it'll work in TSQL, but
I'll give it a shot. I need to better understand the Group By concept as it
is, so this will be good to play with.

-Darrel

Report · Dec 17, 2007

On 17 Dec 2007 in macromedia.dreamweaver.appdev, darrel wrote:

>> You will get errors if you do not include all the columns from your
>> SELECT in the GROUP BY. That is the nature of a GROUP BY. The only
>> columns you need not include in the GROUP BY are aggregate
>> functions like SUM(), COUNT() etc.
>
> The problem with that, though, is that I have TEXT fields in the DB
> that then gives me errors (can't group by text fields).

Just grab the straight data set (SELECT ... FROM ... ORDER BY field1
ASC, field2 ASC). Then use Tom Muck's Simulated Nested Repeat Region
[1]. Or it's not that hard to program yourself:

DIM showHeader
showHeader = ""

' start of repeat region code goes here
' Now we want to print a header if this is a new type
IF showHeader <> Recordset1.Fields.Item("type").Value THEN
' Insert a new header here
Response.Write("<h2>Category: " & Recordset1.Fields.Item("type").Value
& "</h2>" & vbCrLf)
END IF
' Repeat region for dates goes here
showHeader = Recordset1.Fields.Item("type").Value
' End repeat region

The data still looks like:
CatA Date1 whatever
CatA Date2 whatever
CatB Date1 whatever

Basically all the code above is doing is to print the category only
when it changes.

[1] http://www.tom-muck.com/extensions/help/simulatednestedregion/
--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

Report · Dec 17, 2007

Ah! The dredded TEXT datatype..................

"darrel" <notreal@nowhere.com> wrote in message
news:fk657i$4jg$1@forums.macromedia.com...
>
>> You will get errors if you do not include all the columns from your
>> SELECT
>> in the GROUP BY. That is the nature of a GROUP BY. The only columns you
>> need not include in the GROUP BY are aggregate functions like SUM(),
>> COUNT() etc.
>
> The problem with that, though, is that I have TEXT fields in the DB that
> then gives me errors (can't group by text fields).
>
> -Darrel
>

Report · Dec 17, 2007

> Basically all the code above is doing is to print the category only
> when it changes.

I have the program logic working fine...it's the ordering of the data in the
dataset I'm trying to tweak.

Ultimately, I think "sort by a, b" will suffice just fine, but do want to
explore the group by option a bit more.

-Darrel

Report · Dec 18, 2007

On 17 Dec 2007 in macromedia.dreamweaver.appdev, darrel wrote:

> Ultimately, I think "sort by a, b" will suffice just fine, but do
> want to explore the group by option a bit more.

Just remember the KISS principle...

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php