--- In firebird-support@yahoogroups.com, "Ivan Prenosil" wrote:

> > What is the syntax to select the latest 30 days data and sort it
> > from oldest date to most recent date? (please refer message id
> > 26907, question about the same, but i need to change it a bit). I
> > have tried :
> >
> > 'SELECT First 30 CLS, VOL, OPN, HIGH, LOW, DATA,symbol FROM DATA
> > WHERE STOCK='''+imya+''' order by DATA ACS'
> >
> > With the statement above i got the first 30 days data sorted from
> > the oldest date to the most recent date.
> >
> > 'SELECT First 30 CLS, VOL, OPN, HIGH, LOW, DATA,symbol FROM DATA
> > WHERE STOCK='''+imya+''' order by DATA DESC'
> >
> > with the statement above i got the last 30 days data BUT sorted
> > from the latest date to the oldest date.
> >
> > Now i want the latest 30 days data SORTED from the oldest date to
> > the most recent date.
>
> Method 1:
> 'SELECT Skip((SELECT COUNT(*) - 30 FROM DATA)) CLS, VOL, OPN, HIGH,
> LOW, DATA,symbol FROM DATA
> WHERE STOCK='''+imya+''' order by DATA ACS'

ACS??? ;O)

> Method 2 (you need Firebird 2):
>
> 'SELECT * FROM (
> SELECT First 30 CLS, VOL, OPN, HIGH, LOW, DATA,symbol FROM DATA
> WHERE STOCK=xxx ORDER BY DATA DESC )
> ORDER BY DATA ASC

Method 3 (if DATA is unique):
'SELECT CLS, VOL, OPN, HIGH, LOW, DATA,symbol FROM DATA D1
WHERE STOCK='''+imya+''' AND
(SELECT COUNT(*) FROM DATA D2
WHERE D2.STOCK = D1.STOCK AND D2.DATA > D1.DATA) < 30
order by DATA ASC'

Method 4 I have described here a few times before, and that is to use
a NOT EXISTS with a few joins. Though I do not have the patience to
write an example with about 30 joins. It may be able to compete with
the other three suggestions in terms of speed, but not in terms of
maintainability.

Set