sort ascending using Group By??

NoviceMe
10-14-2004, 05:11 PM
Hi,

I am trying to get the table that results from the following statement to sort the outcomes column in descending order. Can DESC be included in GROUP BY? If so, where does it go? I have tried ORDER BY outcomes DESC, but I get an error message saying I need the group by. I am using Sybase SQL Anywhere, and am very new to SQL...

SELECT DISTINCT name1, place, event_type,
DateFormat(date_of_event,'yyyy') As year,
SUM (outcomes) As outcomes
FROM event_results
GROUP BY outcomes, name1, place, event_type, DateFormat(date_of_event,'yyyy');

koncept
10-14-2004, 05:20 PM
use the "order by" key word so like this

SELECT DISTINCT name1, place, event_type, DateFormat(date_of_event,'yyyy') As year, SUM (outcomes) As outcomes
FROM event_results
GROUP BY outcomes, name1, place, event_type, DateFormat(date_of_event,'yyyy')
order by col desc, col asc, col;

NoviceMe
10-14-2004, 05:43 PM
koncept,
I still can't get it to work, have I done it right?


SELECT DISTINCT name1, place, event_type, DateFormat(date_of_event,'yyyy') As year, SUM (outcomes) As outcomes
FROM event_results
GROUP BY outcomes, name1, place, event_type, DateFormat(date_of_event,'yyyy')
order by outcomes desc;
[/QUOTE]

The table is created, but it does not sort it in desc order, any further clues as to where I am going wrong?

koncept
10-14-2004, 08:10 PM
stupid question does it work with out the order by statement

if yes then copy the working code to a query in access and paste it in under sql view, then goto design view and set the order there try running it and if its good go back to sql view n copy it outa there

NEOLLE
10-14-2004, 09:16 PM
Hi NoviceMe,
Whats the error message?

Perhaps you could try this one

SELECT name1, place, event_type, DateFormat(date_of_event,'yyyy') As year, SUM (outcomes) As outcomes
FROM event_results
GROUP BY outcomes, name1, place, event_type, date_of_event
ORDER BY outcomes desc;

NoviceMe
10-15-2004, 02:53 AM
koncept,

thanks, a great idea, I did as you said, and with another minor adjustment, got it to work as follows:


SELECT DISTINCT name1, place, event_type, DateFormat(date_of_event,'yyyy') As year, SUM (outcomes) As outcomes1
FROM event_results
GROUP BY outcomes, name1, place, event_type, DateFormat(date_of_event,'yyyy')
order by SUM(outcomes1) desc;


:D

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum