#1
  1. Prom night: 1973
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Dec 2001
    Posts
    1,156
    Rep Power
    24

    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
  2. #2
  3. Prom night: 1973
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Dec 2001
    Posts
    1,156
    Rep Power
    24
    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.
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Why not just:

    SELECT COUNT(*) FROM table_name WHERE Code < 'BLAH';
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    Just a question, does PostgreSQL have something like Oracle's rowid?
  8. #5
  9. Prom night: 1973
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Dec 2001
    Posts
    1,156
    Rep Power
    24
    >Why not just:

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

    Well, I almost got to it myself. Thanks again, Rycamor.
  10. #6
  11. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Originally posted by pabloj
    Just a question, does PostgreSQL have something like Oracle's rowid?
    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).
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  12. #7
  13. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    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?
  14. #8
  15. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Originally posted by pabloj
    Thanks for your answer rycamor, your writing is generally right but I had to use rowids to clean up a very badly designed database ...
    Hehe... we've all been there. Yes, sometimes rowids are necessary in such situations.

    Another one, why do you disable OIDs? Speed?
    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo