RE: Another SQL Question

[Alan Shore <AlanShore@xxxxxxxx>]

Roger - another BRILLIANT being awarded here.
I did not know you could do that. Now that is something worth remembering.

Thanks


Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx

midrange-l-bounces@xxxxxxxxxxxx wrote on 12/04/2006 04:23:27 PM:

> If your OS version does not support CTEs, You can also substitute place
> numbers for labels in the order by statement:
>
> > SELECT SKUNO, DIVISION, sum(PRICE), avg(price), count(*) FROM FileA
> > GROUP BY SKUNO, DIVISION ORDER BY count(*) desc, SKUNO, DIVISION
>
> SELECT SKUNO, DIVISION, sum(PRICE), avg(price), count(*)
>     FROM FILE A
>     GROUP BY SKUNO, DIVISION
>     ORDER BY 5 DESC, 1, 2
>
> HTH,
> Roger Mackie
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Chris Payne
> Sent: Monday, December 04, 2006 3:15 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: Another SQL Question
>
> You can wrap your statement in a CTE, which lets you treat the results
> of a query like a regular table.
>
> With Prices_and_counts as(
> SELECT
>  SKUNO,
>  DIVISION,
>  sum(PRICE) as total_price,
>  avg(price) as average_price,
>  count(*) as count_of_things
> FROM
>  FileA
> GROUP BY
>  SKUNO,
>  DIVISION
> )
>
> Select
>  SKUNO,
>  DIVISION,
>  total_price,
>  average_price,
>  count_of_things
> from
>  prices_and_counts
> order by
>  count_of_things desc,
>  SKUNO,
>  DIVISION
>
> Chris
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
> unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
> moment to review the archives at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.