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 devdewboy@hotmail.com

Thank you much

email address is devdewboy@hotmail.com

On Jun 26, 2:32 pm, devdew...@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...@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.

On Jun 26, 12:26 pm, Charles Hooper <hooperc2...@yahoo.com> wrote:
> On Jun 26, 2:32 pm, devdew...@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...@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.

On Jun 26, 3:39 pm, devdew...@hotmail.com wrote:
> On Jun 26, 12:26 pm, Charles Hooper <hooperc2...@yahoo.com> wrote:
> > On Jun 26, 2:32 pm, devdew...@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...@hotmail.com
>
> > > Thank you much
> > 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.

With the closing hint that I provided, the data is presented like
this:
C1 C2
-------------------- --------------------
credit david
debit david
credit kate
debit kate
credit sam
credit will
debit bob
debit joe
salary fred
salary phill
salary will

I will leave it to you make the final adjustment to the SQL statement
to correct the sort.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

Thank you very. You've been more than helpful.