Re: SQL - Special Sorting need for Result set

On Jun 26, 3:39 pm, devdew..._at_hotmail.com wrote:
> 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
> > 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. Received on Tue Jun 26 2007 - 15:06:04 CDT