If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > how to translate "row_number" in "ms access"?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
King of Understatement
 
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:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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 !
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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....
Reply With Quote
  #5 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,022
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Posts: 5
thanks georgev,
but i have to really delete those dupes rows
Reply With Quote
  #7 (permalink)  
Old
King of Understatement
 
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:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #8 (permalink)  
Old
King of Understatement
 
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:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #10 (permalink)  
Old
www.gvee.co.uk
 
Join Date: Jan 2007
Location: UK
Posts: 11,022
And once you'd done the tidying up - what are you going to do to stop duplicates re-occuring?
__________________
George
Home | Blog
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On