[AccessD] sequel question

Hi Arthur:

I was trying to make a complex problem simple but it does not seem to render
down that easily. 

The problem is that there ended up being some duplicates in a table. They
could be grouped out but I needed to be able to delete only the second
duplicate. The solution was to select the matched data, put into a temp
table sorted in descending order. Using half the record count of the temp
table, used 'select top xx...' to create a sub-table in conjunction with In
and Delete the records could be easily removed. 

DELETE * FROM RosterReportTemplate 
WHERE (RosterReportTemplate.RecNum 
In (select top " & intRecords & " recnum " 
from tblRecord ORDER BY recnum desc;));"

Thanks for your help.
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Thursday, September 27, 2007 9:38 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] sequel question

I think that your question encoded several nested questions.

1. Your example in no way resembles a true Sequence. In fact, I have no idea
what it resembles. What does this mean:

George  0
Sally           1
Robert  0
Henry           1
John            0
Mary            1

I have no idea what you intend by suppling this example data.

However, in the absence of clarity I have never been one to refuse to plunge
on.

The concept "Sequence" has a precise meaning in DB theory, and perhaps
that's not the meaning you intend.

In Oracle, for example, you create an object called a Sequence and give it a
beginning and ending number. Judging by your sample data, that is not at all
what you mean. I don't mean that as a put-down; I simply don't understand
what you are after.

One possibility is that you want a nested-sequence:

Parent Key = 135
    Child Key = 135, 1
    Child Key = 135, 2
Parent Key = 136
    Child Key = 136, 1
    Child Key = 136, 2

I could be missing your point by a large slice, but AFAICS your sample data
is wobbly, so either I am wobbly or your sample data is insufficiently
precise.

What is your definition of a sequence? I don't really care to beat the bush
about the official relational definition of a sequence, I would prefer to
know more precisely what you're after.

A.


On 9/27/07, Jim Lawrence <accessd at shaw.ca> wrote:
>
> Can a sequence of numbers be created in a sequel list?
>
> Example:
> Given a list like:
>
> George
> Sally
> Robert
> Henry
> John
> Mary
>
> Can the piece of sequel code be modified to produce/create the following
> list?
>
> George  0
> Sally           1
> Robert  0
> Henry           1
> John            0
> Mary            1
>
> TIA
> Jim
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com