in reply to Sorting

You mention that the results are ordered correctly in your database but that the order is altered by your SELECT statement. It might be helpful to know that one of the features (no, really, it's a feature) of most relational databases is that a result set from a query will be in an indeterminate order unless you ask for it to be ordered. (Some RDBMS's might have a feature to get around this; since I don't know what you're using and spend most of my time in SAS and DB2, so I'm not sure.)

Ideally, you will have a column in the table on which you can order, but if you don't, do not despair -- most RDBMS's will provide the table in its "natural" (physically stored) order if you do "select * from table". You might then need to add another counter column (or use an RDBMS-specific function to get to it) and either run SQL against that or just do your processing in straight Perl.

Hope this helps.

Replies are listed 'Best First'.
Re: Considerations on ordering
by johnirl (Monk) on Aug 13, 2002 at 12:58 UTC
    Thanks for that and I am actually using DB2 also.
    I realise that when you do a simple select the order is maintained however I'm using a full outer join. So this orders the table fo me in the result. Even when I add an unsorted or order by clause it does not help. I really think that perl is my only option however if you can think of something within DB2 which would save me having top program it I would be delighted to hear about it.

    j o h n i r l .

    Sum day soon I'Il lern how 2 spelI (nad tYpe)

[reply]
      Ah! Well, I think if you're trying to do it in straight-up SQL with very little Perl, it'll be quite tricky. In order to get the order-preservation you need, I'm pretty sure you'd have to create a temporary table with the additional "counter" column I mentioned above (not sure how you would do this in DB2 but suspect there is a function to get that); also, with a temporary table you could update all NULL values to 0, sidestepping that problem as well. Since you're in DB2 you could automate the whole shebang with SPUFI. Or if you have QMF around you might be able to do everything within a FORM.

      But we're straying pretty far from Perl now and I really do think it'll be easier to dump the table into Perl's control and do all of your stuff there. It is the Practical Perfect Extraction and Reporting Language after all :). SQL is all well and good when you're working with more than one table but if you're trying to do fancy stuff within a result set it's almost always easier to program it.

[reply]
        Yeah I've kinda come around to the realisation that I'm gonna have to program it. But any ideas how?

        j o h n i r l .

        Sum day soon I'Il lern how 2 spelI (nad tYpe)

[reply]

In Section Seekers of Perl Wisdom