Re: find position of row in set of rows
Date: Sun, 31 Jan 2010 19:06:15 +0000 (UTC)
Message-ID: <hk4kb7$c9l$2_at_solani.org>
On Sat, 30 Jan 2010 08:24:28 -0800, Mark D Powell wrote:
> "If you need a database to return you an ordered list, you are having an
> application design issue."
>
> I think that this statement is a little overly broad. After all the
> order by clause exists for a reason. It is often desired to return an
> ordered set of data to make the data more useful. After all it is much
> easier to find the desired account, name, or other valid value off an
> ordered drop down list than using a list in random order!
>
> Mgogala, has a point in that how you order a list can be both of
> questionable value and the ordering itself can be questionable based on
> how the dasta is stored and retrieved. Nevertheless, I will
> respectively disagree that ordering a set is wrong or in any way invalid
> in itself. It is how you order it and what you try to do with it that
> may be invalid.
Mark, I do agree with the statement that ordered lists can be really useful. Ordering and ordering relations are, after all, an integral part of both the set theory and the RDBMS software. There is, however, an issue of understanding here: what you retrieve from the RDBMS is a subset of a relation, possibly an ordered one. All other characteristics should be superimposed on that subset by the application, not the database itself. Having said that, I do find Oracle exceptionally limited in its syntax. Take a look at this:
Command: SELECT
Description: retrieve rows from a table or view
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
- | expression [ [ AS ] output_name ] [, ...] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY expression [, ...] ] [ HAVING condition [, ...] ] [ WINDOW window_name AS ( window_definition ) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]
where from_item can be one of:
[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( select ) [ AS ] alias [ ( column_alias [, ...] ) ] --More--
This is from PostgreSQL 8.4.2, obtained by executing "\h select" in psql. Observe the "LIMIT" and OFFSET clauses, ideal for implementing pagination. That is precisely the reason why many people are attempting to return "an ordered list". Oracle Corp. has made a duct tape fix of the situation with the ROWNUM pseudo-column and row_number() function, but that is not the same as having this capability built into the SELECT statement itself. This is very well known to Oracle Corp., the first comparison of this type was made with MySQL a long time ago. That was precisely the reason why MySQL was deemed a "web database". It was easier to manage and understand than the complex Oracle RDBMS and programming pagination was a breeze. The same thing is with cursors.
-- http://mgogala.freehostia.comReceived on Sun Jan 31 2010 - 13:06:15 CST