0

I am fetching a table from a table and it is automatically sorting itself into alphabetical order. I understand you cannor predict the order of a column and that a artifact could be sorting it alphabetially. However, is there a way for me to order column in that same way that it sits within fr_dcode.

the column is "Executing Broker" which is being fetched form "fr_dcode"

        select distinct
        rtrim(portfolio_name) as "_Aladdin Portfolio",
        b.level1_cd as "_Executing Broker",
        'Y' as "_Yes"
        from t$temp5 as a, fr_dcode as b
        where a.name=b.value
        order by "_Aladdin Portfolio"
        )

        LOOP
            "Aladdin Portfolio" := var_r."_Aladdin Portfolio";
            "Executing Broker" := var_r."_Executing Broker";
            "Yes" := var_r."_Yes";
            RETURN NEXT;
        END LOOP;
jack gell
  • 175
  • 1
  • 8
  • 3
    I am confused. Sorting is applied to rows, not columns. If you want your columns in the SELECT list in a different order you need to write them in a different order. If your rows are sorted in a way you don't want, then why don't you just remove the `order by`. –  Nov 17 '22 at 13:56
  • 1
    Why do you use a LOOP? – Frank Heikens Nov 17 '22 at 14:08
  • the column Executing broker is being ordered alphabetically so maybe row 1 the column val for "Executing Broker" could = "a" and on row 20 the column val for "Executing Broker" could be "z". I need it returned in the order that it is sat in the database. – jack gell Nov 17 '22 at 14:10
  • ... and please, please, please use [ANSI JOINs](https://stackoverflow.com/q/19905028/205233) – Filburt Nov 17 '22 at 14:54
  • SQL data is unordered, so *...order column in that same way that it sits within fr_dcode...* is going to constantly change. If you want to sort by `"_Executing Broker"` then add it to the `ORDER BY`. – Adrian Klaver Nov 17 '22 at 16:08
  • The issue is that it is coming out ordered. when I run the equivilent in sybase it comes out in a random order. I assume it is the way that it sits within the database that is be queried. I want to out put to come in the same order. – jack gell Nov 18 '22 at 08:58
  • 1) It is 'ordered' probably because that was how the data was initially entered. As you insert, delete and/or update the ordering will change. 2) To repeat if you want a consistent output order, that persists across backend changes, you will need an `ORDER BY` clause in the query. – Adrian Klaver Nov 18 '22 at 16:30

0 Answers0