Row numbers in a query?

Hello,

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

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.

Why not just:

SELECT COUNT(*) FROM table_name WHERE Code < 'BLAH';

Just a question, does PostgreSQL have something like Oracle's rowid?

>Why not just:

>SELECT COUNT(*) FROM table_name WHERE Code < 'BLAH';

Well, I almost got to it myself. Thanks again, Rycamor.

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).

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?

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.