November 15th, 2003, 04:43 PM
Row numbers in a query?
I want to return the index of a specific row from a query. For instance, if I do
SELECT * FROM table_name ORDER BY Code
I want to know which row number of the result has (say) Code = 'BLAH' Is there a way to do this in Postgres?
Thanks for viewing
November 16th, 2003, 12:31 PM
The best method I've found so far is to use a sub-query.
SELECT COUNT(*) FROM (SELECT * FROM table_name WHERE Code < 'BLAH') AS result;
If anyone comes up with something better, please let me know.
November 16th, 2003, 04:20 PM
Why not just:
SELECT COUNT(*) FROM table_name WHERE Code < 'BLAH';
November 17th, 2003, 03:12 AM
Just a question, does PostgreSQL have something like Oracle's rowid?
November 17th, 2003, 07:52 AM
>Why not just:
>SELECT COUNT(*) FROM table_name WHERE Code < 'BLAH';
Well, I almost got to it myself. Thanks again, Rycamor.
November 17th, 2003, 10:27 AM
PostgreSQL has OIDs, which are similar to Oracle's rowid. Personally, I always disable OIDs in my tables. Tracking data by rowid is a violation of the relational data model (specifically, the Information Principle), and makes your data manipulation dependent on the wrong things (physical ordering, etc...). Really, if you need row identifiers for anything, I would take that as a sign you need to re-think your database design.
Hey kurious, don't be too hard on yourself ; sometimes its the small obvious things that get you. But you did use a sub-query in the "right" context (as a virtual table in the FROM clause, rather than in the WHERE clause).
November 17th, 2003, 11:19 AM
Thanks for your answer rycamor, your writing is generally right but I had to use rowids to clean up a very badly designed database ...
Another one, why do you disable OIDs? Speed?
November 17th, 2003, 12:53 PM
Hehe... we've all been there. Yes, sometimes rowids are necessary in such situations.
Well, there might be a small speed advantage, but the main reason I do it is that when I design a database, I don't want anyone using tricks like that to bypass my logical model (hopefully they are well-designed enough not to need the surgery you mention above ). Also, in PostgreSQL, OIDs are not an absolute sequence. They are based on an INT4, and if a table grows beyond a certain size, some re-use of previously deleted OIDs can occur, as well as potentially complete wrap-around. Thus, they cannot always be assumed to be unique. (unique for most practical purposes, to be sure)
PostgreSQL uses OIDs internally to handle many details of object manipulation. To me this means it is a part of the DBMS implementation, and should not normally be exposed to the user/designer.