hi all,
could anyone help me?
I need to query output by compare dates from 2 table and i'm using a UNION query..and wanted to sort the output by date..
My query like this:
SELECT
TO_CHAR(DATE) DATE1,
INVOICE
FROM ACCOUNT1
WHERE DATE < (to_date('122003','MMYYYY'))
UNION
SELECT
TO_CHAR(DATE) DATE1,
INVOICE
FROM ACCOUNT2
WHERE DATE >= (to_date('122003','MMYYYY')) AND
ORDER BY 1 ASC;
but the output like below:
DATE1 INVOICE
--------------- ----------------
28-APR-08 412.82
28-APR-09 363.72
28-APR-10 578.82
28-AUG-07 111
28-AUG-08 241.05
28-AUG-09 363.72
28-DEC-06 111
28-DEC-09 363.72
28-FEB-07 111
28-FEB-10 294.42
28-JAN-07 296
28-JAN-08 372.83
If there any query that can sort the output by date in ascending order like below;
DATE1 INVOICE
--------------- ----------------
28-FEB-07 111
28-AUG-07 111
28-APR-08 412.82
28-APR-09 363.72
28-APR-10 578.82
Pls help,
thanks a lot
Kate
Try this one
#!/bin/sh
sed 's/\(..\)-\(...\)-\(..\).*/\3\2\1 &/' | \
sed 's/JAN/01/; s/FEB/02/; s/MAR/03/; s/APR/04/; s/MAY/05/; s/JUN/06/; s/JUL/07/; s/AUG/08/; s/SEP/09/; s/OCT/10/; s/NOV/11/; s/DEC/12/;' | \
sort | \
cut -f 2- -d ' '
sh script.sh < input_file.txt
Since you haven't mentioned your database, I'll assume that it's Oracle.
The problem with the query is that it sorts by the 1st column, which is a string (you used TO_CHAR function). So, the output is ordered by strings and not by dates.
The remedy is to either leave the dates unformatted, or format+sort in an outer SELECT query.
The testcase here shows both techniques -
SQL>
SQL> -- Check the data in my tables
SQL> --
SQL> select * from account1;
DT INVOICE
--------- ----------
28-SEP-02 111.22
28-NOV-02 222.33
2 rows selected.
SQL>
SQL> select * from account2;
DT INVOICE
--------- ----------
28-FEB-08 333.44
28-AUG-09 444.55
2 rows selected.
SQL>
SQL> --
SQL> -- The problem is that sorting is done on character strings, and
SQL> -- not on dates. ORDER BY 1 ASC means "order by 1st column in the
SQL> -- SELECT column-list", and the 1st column is a string!
SQL> --
SQL> select to_char(dt) date1, invoice
2 from account1
3 where dt < to_date('122003','mmyyyy')
4 union
5 select to_char(dt) date1, invoice
6 from account2
7 where dt >= to_date('122003','mmyyyy')
8 order by 1 asc;
DATE1 INVOICE
------------------ ----------
28-AUG-09 444.55
28-FEB-08 333.44
28-NOV-02 222.33
28-SEP-02 111.22
4 rows selected.
SQL>
SQL> --
SQL> -- SOLUTION 1: Select *dates* and sort on *dates*
SQL> --
SQL> select dt date1, invoice
2 from account1
3 where dt < to_date('122003','mmyyyy')
4 union
5 select dt, invoice
6 from account2
7 where dt >= to_date('122003','mmyyyy')
8 order by 1 asc;
DATE1 INVOICE
--------- ----------
28-SEP-02 111.22
28-NOV-02 222.33
28-FEB-08 333.44
28-AUG-09 444.55
4 rows selected.
SQL>
SQL> --
SQL> -- The only problem with SOLUTION 1 is that the dates
SQL> -- will be displayed in the default format of the client.
SQL> -- My client is sqlplus, and its default date format is
SQL> -- "DD-MON-RR", so that's how the dates are formatted for me.
SQL>
SQL> -- Now let's say you want to sort by dates, and want to *display*
SQL> -- those dates in some non-default character format, say, "MM/DD/YYYY".
SQL> -- For that, you'll have to be explicit.
SQL>
SQL> -- SOLUTION 2: Format *dates* as per your preference in an outer
SQL> -- SELECT query and mention the *date column* explicitly
SQL> -- in the ORDER BY clause
SQL> --
SQL> select to_char(dt, 'mm/dd/yyyy') date1, invoice
2 from ( select dt, invoice
3 from account1
4 where dt < to_date('122003','mmyyyy')
5 union
6 select dt, invoice
7 from account2
8 where dt >= to_date('122003','mmyyyy')
9 )
10 order by dt asc;
DATE1 INVOICE
---------- ----------
09/28/2002 111.22
11/28/2002 222.33
02/28/2008 333.44
08/28/2009 444.55
4 rows selected.
SQL>
SQL>
SQL>
HTH,
tyler_durden