SELECT FIRST 20 SUM(V.vnd_total) as TOTAL, (SELECT SUM(I.item_qtde) FROM
items_venda I WHERE I.vnd_cod=V.vnd_cod) AS QTDE, C.cli_nome FROM VENDAS
V JOIN clientes C ON (C.cli_cod=V.cli_cod) WHERE V.vnd_data BETWEEN
'2006-03-01' AND '2006-03-31'
GROUP BY C.cli_nome, V.vnd_cod
ORDER BY 2 DESC

This will order by the second column in the result set, descending.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Fabio Gomes
Sent: Wednesday, March 29, 2006 3:07 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: help with a select, i m out of
ideas.

Thanx, it was very simple ^^

Anyway, now i have another problem, my query is working, but i need to
have an option to order by the Item_qtde and vnd_total, with vnd_total
it works fine, but with item_qtde i got this:

SELECT FIRST 20 SUM(V.vnd_total) as TOTAL, (SELECT SUM(I.item_qtde) FROM
items_venda I WHERE I.vnd_cod=V.vnd_cod) AS QTDE, C.cli_nome FROM VENDAS
V JOIN clientes C ON (C.cli_cod=V.cli_cod) WHERE V.vnd_data BETWEEN
'2006-03-01' AND '2006-03-31'
GROUP BY C.cli_nome, V.vnd_cod
ORDER BY SUM(I.item_qtde) DESC

Dynamic SQL Error SQL error code = -206 Column unknown I.ITEM_QTDE At
line 10, column 21.

I tried ORDER BY QTDE, but it didnt work, is there any way to do this?

thanx for any help :)

On 3/29/06, Arno Brinkman <fbsupport@...> wrote:
>
> Hi,
>
>
> > SELECT FIRST 20 SUM(V.vnd_total) as TOTAL, (SELECT SUM(I.item_qtde)
> > FROM items_venda I WHERE I.vnd_cod=V.vnd_cod)
> AS
> > QTDE,
> > C.cli_nome
> > FROM VENDAS V
> > JOIN clientes C ON (C.cli_cod=V.cli_cod) WHERE V.vnd_data BETWEEN
> > '2006-1-01' AND '2006-1-31'
> > GROUP BY C.cli_nome
> > ORDER BY SUM(V.vnd_total) DESC
> >
> > And i got this error:
> >
> > Dynamic SQL Error SQL error code = -104 Invalid expression in the
> > select list (not contained in either an aggregate function or the
> > GROUP BY
> clause)
> >
> > Whats wrong?
>
> V.vnd_cod isn't part of the GROUP BY clause.
> Thus include V.vnd_cod in the GROUP BY clause or SUM the sub-select.
>
> Regards,
> Arno Brinkman
> ABVisie
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> General database development support:
> http://www.databasedevelopmentforum.com
>
> Firebird open source database (based on IB-OE) with many SQL-99
features :
> http://www.firebirdsql.org
> http://www.firebirdsql.info
> http://www.fingerbird.de/
> http://www.comunidade-firebird.org/
>
> Support list for Firebird and Interbase users :
> firebird-support@yahoogroups.com
>
> Nederlandse firebird nieuwsgroep :
> news://newsgroups.firebirdsql.info
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item on
> the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
> ------------------------------
> YAHOO! GROUPS LINKS
>
>
> - Visit your group
"firebird-support<http://groups.yahoo.com/group/firebird-support>"
> on the web.
>
> - To unsubscribe from this group, send an email to:
>
> firebird-support-unsubscribe@yahoogroups.com<firebird-support-unsubscr
> ibe@yahoogroups.com?subject=Unsubscribe>
>
> - Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/>.
>
>
> ------------------------------
>


[Non-text portions of this message have been removed]



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links