Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2008
    Posts
    1

    Unanswered: Oracle "ROWNUM" in Microsoft Access?

    Does something exist in MS-Access to get rownumber from the result query?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Moved to Access topic
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What are you trying to achieve?
    Remember that a row-number can only have meaning within an ordered resultset.
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by wilawan.s
    Does something exist in MS-Access to get rownumber from the result query?
    not really, although they could've added it in a recent version and i would've missed it

    but i doubt it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    OP - it might be worth stating exactly what rownumber does since otherwise you are not only relying on people knowing Access but also Oracle. In an Access forum that probably knocks out about 90% of the posters.

    I don't believe it exists in 2k7.

    I believe rownumber relies on a propietry way that Oracle stores data but I'm a bit hazy on this. Two options - create a table with an autonmuber field, insert your result set into there and then select. Otherwise have a sub select that counts all rows where pk < subselect.pk. SQL Server 2005 has a row_number function using the over clause too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it has nothing to do with the way the data is stored in oracle

    row number is a concept that makes sense only in the context of a result set

    when you run a query, and get a result set back, those rows definitely are numbered (whether an ORDER BY was specified or not, but especially when), and oracle merely implemented this as an actual reserved word slash query function

    oracle recognized a need and satisifed it, and many versions ago, to boot -- one of several things positive i have to say about oracle (another is CONNECT BY)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •