At 12:50 PM 8/12/2005 +0000, you wrote:

>Hello all,
>
>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.
>
>PS: i have a table called DATA and a field in that table named DATA
>to store date information.'imya' refers to the stock name. 'CLS, VOL,
>OPN, HIGH, LOW, DATA,symbol' are the fields in the table.

Unless you want exactly 30 records, SELECT FIRST is not what you
need. (It's not consistent with what I infer from your requirements, anyway.)

You have some weird, illegal syntax here, anyway: what the heck is
''+imya+''' supposed to be? If the stock code is 'imya' then the search
value is 'imya'. If it's going to be picked up from some user
input/selection, use a parameter and assign the selected value to the
parameter once the query has been prepared.

The keyword for an ascending sort is ASC, not ACS; but you don't need it
anyway, because ASC is the default sort order.

If you want the records where your date field falls anywhere during the
last 30 days, including today, AND this field DATA is a DATE type (not a
TIMESTAMP, not some other type, such as a string) then do this:

SELECT CLS, VOL, OPN, HIGH, LOW, DATA,symbol
FROM DATA
WHERE STOCK= :stock
and DATA between (current_date - 30) and current_date
order by DATA

If DATA is a timestamp, you have to bear in mind that, to include records
created after midnight today, you have to modify the search clause:

SELECT CLS, VOL, OPN, HIGH, LOW, DATA,symbol
FROM DATA
WHERE STOCK= :stock
and DATA >= (current_date - 30)
and DATA < (current_date + 1)
order by DATA

If DATA is a string, then forget it, unless the string is of a format that
you can CAST to a valid date type.

Note that if you are doing the (ill-advised) thing of constructing a
completely literal SQL statement as a way of handling variable inputs for
your STOCK value, or you want a literal statement to test, then your
statement for the first example will be:

SELECT CLS, VOL, OPN, HIGH, LOW, DATA,symbol
FROM DATA
WHERE STOCK= 'imya'
and DATA between (current_date - 30) and current_date
order by DATA


./heLen