Re: Opinions on approach, please...

On Fri, 30 May 2008 00:42:31 +1200, "Pete Dashwood"
<dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:



<docdwarf@xxxxxxxxx> wrote in message news:g1m513$p0$1@xxxxxxxxxxxxxxxxxxxx
In article <6a7ffoF343euvU1@xxxxxxxxxxxxxxxxxx>,
Pete Dashwood <dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:


"Frederico Fonseca" <real-email-in-msg-spam@xxxxxxxxx> wrote in message
news:7bos345far6d9sglmuc59ga9mfdlu1qe4n@xxxxxxxxxx

[snip]

Take in consideration that dependign on how you
go on the dynamic sql route, you will not be able to do it exactly as
follows.
move "SELECT TABNAME FROM SYSCAT.TABLES
- " ORDER BY 1

"ORDER BY 1" ? I don't understand this.

<http://www.google.com/search?sourceid=navclient&ie=UTF-8&rls=GGLJ,GGLJ:2006-39,GGLJ:en&q=%22order+by+1%22>

(It is not the way I would, when given the choice, code things... but I
seem to recall seeing something like this in code slapped together by some
Peat, Marwick folks around 1987 or so. ORDER BY (numeric) is sorting on
the column(s) SELECTed in numeric sequence. From
http://www.techonthenet.com/sql/order_by.php :

--begin quoted text:

You can also sort by relative position in the result set, where the first
field in the result set is 1. The next field is 2, and so on.

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY 1 DESC;

This would return all records sorted by the supplier_city field in
descending order, since the supplier_city field is in position #1 in the
result set.

--end quoted text)

DD

Thanks, Doc :-)

Pete.
Just to add to Doc's explanation.

There are some cases where it is needed to use positional instead of
named fields.

For example.

select client, count(client) as nr_of_orders
from invoices
group by client
order by nr_of_orders descending.


The above will fail in almost all SQL implementations.

2 solutions here.
1 - positional order

select client, count(client) as nr_of_orders
from invoices
group by client
order by 2 descending.

3- temporary table
select * from (
select client, count(client) as nr_of_orders
from invoices
group by client) z (or as z depending on db engine)
order by nr_of_orders descending.


Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com
.

Relevant Pages

  • Re: Opinions on approach, please...
    ... go on the dynamic sql route, you will not be able to do it exactly as ... select client, countas nr_of_orders ... from invoices ... order by nr_of_orders descending. ...
    (comp.lang.cobol)
  • Re: Business objects, subset of collection
    ... SQL databases sucks for searching large data sets, ... TPC implementations rely heavily on stored procedures. ... Application modules that are executed on the client side but are stored on the server side. ... Lets say you want to find all unpaid invoices. ...
    (comp.object)
  • Re: Duplicate entries
    ... Do you have a Clients table and an Invoices Table? ... Assuming you have a clients table, is the client in there more than once. ... you are seeing multiples in a query or in a form based on the query? ... I have developed duplicate duplicate entries. ...
    (microsoft.public.access.queries)
  • VBA to allow configured querry.
    ... So I have experience in programming in Excel, ... know if it is possible to build either querries or reports on a customer ... information on accounts receivable (ie. client name, client number, invoice, ... and ArTable shows all invoices with client number name and age that the age ...
    (microsoft.public.access.formscoding)
  • Configureable Query or otherwise
    ... So I have experience in programming in Excel, ... know if it is possible to build either querries or reports on a customer ... information on accounts receivable (ie. client name, client number, invoice, ... and ArTable shows all invoices with client number name and age that the age ...
    (microsoft.public.access.queries)