help with SELECT statement, GROUP BY & DISTINCT ASP/VB

It's a logical issue really. Say you had 2 orders - 17 and 18, that both had
the same postcode,name and city. Which orderid would be used 17 or 18? You
can't have them both, that's why you get duplicates. I've an article that
outlines the confusion it can cause:

http://www.charon.co.uk/content.aspx?CategoryID=27&ArticleID=50

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004



Nathon Jones wrote:
> Hi Lionstone,
>
> Spent most of the night racking my brains over this one...again!
> I've created the following recordset:
>
> SELECT postcode, name, city
> FROM orderstable
> GROUP BY postcode, name, city
>
> This works! But...I need to also include the orderID number and, because
> the orderID is unique to each order, I can't seem to then use the GROUP BY
> clause.
>
> I'll admit that I'm going a little mad with all this. Any help you, or
> anyone, could offer would be very much appreciated. Thank you.
>
> Regards
> Nath.
>

Hi Lionstone,

Spent most of the night racking my brains over this one...again!
I've created the following recordset:

SELECT postcode, name, city
FROM orderstable
GROUP BY postcode, name, city

This works! But...I need to also include the orderID number and, because
the orderID is unique to each order, I can't seem to then use the GROUP BY
clause.

I'll admit that I'm going a little mad with all this. Any help you, or
anyone, could offer would be very much appreciated. Thank you.

Regards
Nath.

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:e8c4jm$ijo$1@forums.macromedia.com...
> If you just want a list of post codes, then
>
> SELECT postcode
> FROM orderstable
> GROUP BY postcode
>
> is equivalent to
>
> SELECT DISTINCT postcode
> FROM orderstable
>
> Either will give you that list.
>

Hi again. Appreciate you sticking with me, thanks.

How do I then populate the dynamic form on my page, from that Recordset, if
it can only display the postcode?
I'd like to include the name and city, as well as the postcode, in the
dynamic menu. This is what I'm struggling with, because I've used GROUP BY
on a single field before, and that has worked out fine. This time, I need
more data from the database, but still need to maintain the GROUP BY on the
postcode so that addresses aren't duplicated in the recordset.

Hope to hear from you,
Regards
Nath.

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:e8c4jm$ijo$1@forums.macromedia.com...
> If you just want a list of post codes, then
>
> SELECT postcode
> FROM orderstable
> GROUP BY postcode
>
> is equivalent to
>
> SELECT DISTINCT postcode
> FROM orderstable
>
> Either will give you that list.
>

If you just want a list of post codes, then

SELECT postcode
FROM orderstable
GROUP BY postcode

is equivalent to

SELECT DISTINCT postcode
FROM orderstable

Either will give you that list.

Thank you for replying Lionstone.

I'm not trying to find out how many addresses there are, I'm simply trying
to display a dynamic menu of addresses that doesn't repeat an address, based
on whether there are two, or more, identical postcodes in the recordset that
feeds the menu. In literal terms, saying "this postcode appears twice, or
more, in this recordset, so I'll only show one of them".

SELECT * FROM orderstable
GROUP BY postcode

This produces an error so I've tried including all of the fields, as you
suggest, in the GROUP BY clause:

SELECT orderID, customerID, address, city, postcode
FROM orderstable
GROUP BY postcode, orderID, customerID, address, city

However, this is still showing a duplicate? It isn't producing an error
though, so I guess that's progress - of sorts! :o)

Is GROUP BY even what I need then? Your expert advice would, again, be
greatly appreciated - oh wise one! :o)

Regards
Nath.

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:e8bffm$me4$1@forums.macromedia.com...
> GROUP BY doesn't work the way you're thinking. It does not create a group
> such as you'd get with Crystal Reports, with a header and detail rows. It
> doesn't give you groups of results, it turns your results into groups. In
> your case, grouping by postal code, you will end up with a list of postal
> codes. You could then count, sum, average, etc, to your heart's desire to
> find out how many addresses are in the post code, etc, but you cannot
> include ANY information from those columns unless you also group by those
> columns.
>
> In a nutshell, when you have a GROUP BY clause in your query, all columns
> in the SELECT clause must also be in the GROUP BY clause or a part of an
> aggregate function (SUM, MIN, MAX, AVG, COUNT, etc).
>
>
> "Nathon Jones" <sales@NOSHPAMtradmusic.com> wrote in message
> news:e8bbif$har$1@forums.macromedia.com...
>> Hi. Hope someone can help with my SELECT statement.
>>
>> I have a customers table:
>>
>> customerID
>> customername
>> address
>> ...etc
>>
>> An orders table:
>>
>> orderID
>> customerID
>> deliveryaddress
>> deliverypostcode
>> ...etc
>>
>> A customer can obviously submit several orders and each of these orders
>> may have different addresses. I want to be able to provide the customer
>> with the option to auto-complete the order form by choosing a previously
>> used delivery address from a drop down menu on the order form page.
>> However, I would like to GROUP the delivery addresses by postcode so that
>> the same postcode (address) doesn't appear twice in the menu.
>>
>> The problem I'm having is that I can only use GROUP BY on a recordset
>> that contains only one field - the field that I group by (the postcode!).
>> Yet I need ALL of the delivery address information in order to fill in
>> the form.
>>
>> I am very new to GROUP BY and someone also mentioned using DISTINCT but
>> I'm not sure how to do this.
>> I understand that I can create a query in my database that uses an INNER
>> JOIN to take related data from two separate tables/queries, but I'm not
>> entirely sure if that will work either on a GROUP BY or DISTINCT.
>>
>> Hope someone can point me in the right direction! Thanks.
>> Regards
>> Nath.
>>
>
>

GROUP BY doesn't work the way you're thinking. It does not create a group
such as you'd get with Crystal Reports, with a header and detail rows. It
doesn't give you groups of results, it turns your results into groups. In
your case, grouping by postal code, you will end up with a list of postal
codes. You could then count, sum, average, etc, to your heart's desire to
find out how many addresses are in the post code, etc, but you cannot
include ANY information from those columns unless you also group by those
columns.

In a nutshell, when you have a GROUP BY clause in your query, all columns in
the SELECT clause must also be in the GROUP BY clause or a part of an
aggregate function (SUM, MIN, MAX, AVG, COUNT, etc).


"Nathon Jones" <sales@NOSHPAMtradmusic.com> wrote in message
news:e8bbif$har$1@forums.macromedia.com...
> Hi. Hope someone can help with my SELECT statement.
>
> I have a customers table:
>
> customerID
> customername
> address
> ...etc
>
> An orders table:
>
> orderID
> customerID
> deliveryaddress
> deliverypostcode
> ...etc
>
> A customer can obviously submit several orders and each of these orders
> may have different addresses. I want to be able to provide the customer
> with the option to auto-complete the order form by choosing a previously
> used delivery address from a drop down menu on the order form page.
> However, I would like to GROUP the delivery addresses by postcode so that
> the same postcode (address) doesn't appear twice in the menu.
>
> The problem I'm having is that I can only use GROUP BY on a recordset that
> contains only one field - the field that I group by (the postcode!). Yet
> I need ALL of the delivery address information in order to fill in the
> form.
>
> I am very new to GROUP BY and someone also mentioned using DISTINCT but
> I'm not sure how to do this.
> I understand that I can create a query in my database that uses an INNER
> JOIN to take related data from two separate tables/queries, but I'm not
> entirely sure if that will work either on a GROUP BY or DISTINCT.
>
> Hope someone can point me in the right direction! Thanks.
> Regards
> Nath.
>