Re: SQL - Special Sorting need for Result set

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. Received on Tue Jun 26 2007 - 14:26:50 CDT