On Jun 26, 12:26 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jun 26, 2:32 pm, devdew..._at_hotmail.com wrote:
>
>
>
> > Hello,
> > Need some help with returning a result set with a special sort.
>
> > Example of rows:
> > Type Name
> > credit sam
> > credit will
> > credit david
> > credit kate
> > debit bob
> > debit joe
> > debit kate
> > debit david
> > salary fred
> > salary will
> > salary phill
>
> > Need result set to be:
> > Type Name
> > credit sam
> > credit will
> > credit david
> > debit david
> > credit kate
> > debit kate
> > debit bob
> > debit joe
> > salary fred
> > salary will
> > salary phill
>
> > If the "name" has a matching Debit and Credit I need them grouped
> > together. the rest will be regularly sorted by type.
>
> > Any ideas on the SQL. A regular ORDER BY will not solve this. I can be
> > emailed at devdew..._at_hotmail.com
>
> > Thank you much
>
> I am not sure that I follow the sorting arrangement. I don't like
> having columns named TYPE and NAME:
> CREATE TABLE T2(
> C1 VARCHAR2(20),
> C2 VARCHAR2(20));
>
> INSERT INTO T2 VALUES('credit','sam');
> INSERT INTO T2 VALUES('credit','will');
> INSERT INTO T2 VALUES('credit','david');
> INSERT INTO T2 VALUES('credit','kate');
> INSERT INTO T2 VALUES('debit','bob');
> INSERT INTO T2 VALUES('debit','joe');
> INSERT INTO T2 VALUES('debit','kate');
> INSERT INTO T2 VALUES('debit','david');
> INSERT INTO T2 VALUES('salary','fred');
> INSERT INTO T2 VALUES('salary','will');
> INSERT INTO T2 VALUES('salary','phill');
>
> If the version of Oracle supports analytical functions, it should be
> fairly easy to implement the sort. For example:
> SELECT
> C1,
> C2,
> LAG(C1) OVER (PARTITION BY C2 ORDER BY C1)
> FROM
> T2;
>
> C1 C2 LAG(C1)OVER(PARTITIO
> -------------------- -------------------- --------------------
> debit bob
> credit david
> debit david credit
> salary fred
> debit joe
> credit kate
> debit kate credit
> salary phill
> credit sam
> credit will
> salary will credit
>
> 11 rows selected.
>
> LAG shows the value from the previous row based on the specified
> grouping (PARTITION BY) and sorting (ORDER BY). The above is not
> exactly what you want, but it is close.
>
> If we check the third column for 'credit' and return the value of C1
> if the third column is anything but 'credit', or 'credit' if the third
> column is 'credit':
> SELECT
> C1,
> C2,
> DECODE(LAG(C1) OVER (PARTITION BY C2 ORDER BY
> C1),'credit','credit',C1) C3
> FROM
> T2
> ORDER BY
> 3,
> 2,
> 1;
>
> C1 C2 C3
> -------------------- -------------------- ------
> credit david credit
> debit david credit
> credit kate credit
> debit kate credit
> credit sam credit
> credit will credit
> salary will credit
> debit bob debit
> debit joe debit
> salary fred salary
> salary phill salary
>
> 11 rows selected.
>
> The above is close, but now we have an additional column.
>
> SELECT
> C1,
> C2
> FROM
> (SELECT
> C1,
> C2,
> DECODE(LAG(C1) OVER (PARTITION BY C2 ORDER BY
> C1),'credit','credit',C1) C3
> FROM
> T2)
> ORDER BY
> C3,
> C2,
> C1;
>
> C1 C2
> -------------------- -----
> credit david
> debit david
> credit kate
> debit kate
> credit sam
> credit will
> salary will
> debit bob
> debit joe
> salary fred
> salary phill
>
> As I mentioned, I do not quite understand your sorting arrangement, so
> what appears above will still need to be adjusted (hint check the
> value of the C1 column in the DECODE, only return 'credit' if C1 is
> 'debit').
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
Hello,
Thx 4 looking at. It is close. The sorting requirements is based on
the Type/C1 column, but additionally if there is a matching Name/C2 of
Credit with a Debit, then sort these together. Otherwise, everything
else is by C1/Type column.
Received on Tue Jun 26 2007 - 14:39:21 CDT
