Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2008
    Posts
    5

    how to translate "row_number" in "ms access"?

    hello,
    i have to translate for "ms access" a sql query that works on "oracle"

    i'm not so skilled in access sql syntax...

    this is the query:

    select *
    from mytable
    where rowid in (select rid
    from (select rowid as rid, row_number() over(partition by myfield order by myfield ) as rn
    from mytable)
    where rn > 1)

    my problem is the translation of row_number clause

    thanks a lot
    mick

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    There is no direct Access equivalent for this particular function. Also, the vast majority of the users here will not know PL\SQL so you would do well to explain what that SQL actually does, ideally in natural English.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    In Oracle, Informix (my own database home) and other enterprise-scale databases, a primary key can be an autonumber, same as in MSAccess. However, this is simply a sequential number guaranteed to be unique. It is a gapped series, because records can be deleted. A concept such as rowid is, as I understand it, actually a mapping device, indicating the record which is currently the record which is that number of records removed from the head of the table. It is, so far as I know, used only to identify the current record of interest and to ensure that any operations (update, etc.) are carried out only on that record. I do not believe that the concept exists in Access, though I daresay, one could open a recordset and physically count one's way down to the record of interest. Not good for performance, though !

  4. #4
    Join Date
    Sep 2008
    Posts
    5
    sorry for my english, i explain my problem:

    i have some duplicates rows in my table and i have to keep only one of them

    example:

    field1|field 2|field3
    johnboy|mick | jack
    sara | fara | mara
    johnboy|mick | jack
    johnboy|mick | jack
    sara | fara | mara
    johnboy|mick | jack

    i need:

    field1|field 2|field3
    johnboy|mick | jack
    sara | fara | mara

    without duplicates rows....

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,138
    Are you trying to delete dupes, or just show a single row for each dupe?
    If it's the latter then try:
    Code:
    SELECT field1
         , field2
         , field3
    FROM   my_table
    GROUP
        BY field1
         , field2
         , field3
    George
    Home | Blog

  6. #6
    Join Date
    Sep 2008
    Posts
    5
    thanks georgev,
    but i have to really delete those dupes rows

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Quote Originally Posted by Jim Wright
    However, this is simply a sequential number guaranteed to be unique.
    Actually, it isn't. You can dupe autonumbers, identities etc.

    OP - easiest way to do this is is:
    1) convert George's code into a Make Table query
    2) Delete the contents of "mytable"
    3) Insert the contents of the table you created in step 1 in to "mytable"

    Obviously back up your data first. There are more sophisticated ways of doing this, and it depends on relationships, preserving autonumbers etc..

    Thoughts?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Also, looking at your PL\SQL it looks to me that you retain an arbitrary row - you don't care which rows you delete so long as you have only one row per "myfield" value left once it has run. Correct?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    If you are intending to do a cleanup of duplicates, the following technique might work.

    Add an autonumber field (RecordId, say). Then create a query (or even a temporary table) populated by selecting Min(RecordId) As RealId, field1, field2, field3 grouping by field1, field2, field3. Then do an inner join between the query and the original table and delete all records where the field1s, field2s and field3s match but RealId is not equal to RecordId.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,138
    And once you'd done the tidying up - what are you going to do to stop duplicates re-occuring?
    George
    Home | Blog

Posting Permissions

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