Footer rows to skip: key

Showing posts with label key. Show all posts

Monday, March 26, 2012

Fetching Duplicate Rows

Hi,

I have some duplicate rows in a table. I didnt define any primary key or unique key on the table.

I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows.

How can i do it?

Please help me....

Thanx in AdvanceI have some duplicate rows in a table. I didnt define any primary key or unique key on the table.

Can you paste some DDL. Are all the columns for the duplicated rows same ? Is there any date time stamp ? You could find some help Here (http://www.sqlteam.com/item.asp?ItemID=3331)|||Try something like:

Select * from Table
Where {Key Fields} IN
(select {key Fields} from Table
group by {Key Fields}
Having Count(*) >1)

Where Table is your Table/Query and {Key Fields} is the list of fields you want to search for duplicates on

HTH

Marp

[
QUOTE]Originally posted by d_kishan
Hi,

I have some duplicate rows in a table. I didnt define any primary key or unique key on the table.

I can get unique rows using DISTINCT, but i want to fetch only the duplicated rows and also i want to delete the duplicated rows.

How can i do it?

Please help me....

Thanx in Advance [/QUOTE]|||You say you want to delete the duplicated rows, but I bet what you really want is to delete all but one of each duplicated row. Your first step is to add a primary key. Otherwise, there is no way to discern which of the duplicated rows to retain and your only option will be to select DISTINCT into a new table and then replace your old table.|||If you want to know how many duplicate rows you have per your Key criteria,

do this

[Select Key1,Key2,..Keyn, count(*)
from Table
group by {Key1,Key2,...Keyn}
Having Count(*) >1)

Fetch last record in a table

HI ALL,
Suppose i did not use identity for generating sequence in a
table.Also there is no sequence no column or no primary key on a
column. Then how to find last record in a table. Suppose there are 10
rows. How can i fetch 10th rows record.
Define your SQL SELECT statement , and THEN use ORDER BY myCol DESC -
select TOP 1. That's assuming there are 10 records.
If there are more than 10 records , and you are using SQL 2005 , you could
do something like:
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY Col2 DESC)AS RowFROm
myTableWHERE Row = 10
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
|||On Jan 16, 2:50Xpm, "MC" <markoDOTculo@.gmailDOTcom> wrote:
> First you need to define 10th. What does it mean exactly? Last fetched, last
> by some value, last..... Without primary key you basically dont have a
> consistent approach, so some kind of definition is definitely needed here.
> MC
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
>
> - Show quoted text -
HI,
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.
|||MC
SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
"MC" <markoDOTculo@.gmailDOTcom> wrote in message
news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
> First you need to define 10th. What does it mean exactly? Last fetched,
> last by some value, last..... Without primary key you basically dont have
> a consistent approach, so some kind of definition is definitely needed
> here.
>
> MC
>
> "mohit" <goenka.mohit@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
|||On Jan 16, 3:22Xpm, mohit <goenka.mo...@.gmail.com> wrote:
> HI ALL,
> X X X Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
In a set there is no such thing as last record.
|||On Jan 16, 3:22Xpm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
>
>
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong
|||On Jan 16, 3:28Xpm, SB <othell...@.yahoo.com> wrote:
> On Jan 16, 3:22Xpm, mohit <goenka.mo...@.gmail.com> wrote:
>
> In a set there is no such thing as last record.
Can you tell what set means. i am asking from table how to fetch last
record i.e last row from a table
|||On Jan 16, 4:45Xpm, mohit <goenka.mo...@.gmail.com> wrote:
> On Jan 16, 3:28Xpm, SB <othell...@.yahoo.com> wrote:
>
>
> Can you tell what set means. i am asking from table how to fetch last
> record i.e last row from a table
A table consists of a set of records. It is not a sequence so that you
have first or last record. A set is an unordered set of records.
|||> Sorry yaar but its not working. When we do order by col_name desc
> the rows gets shuffle due to which answer is coming wrong
How do you know it is not working if there is no data in the row to identify
the order of insertion?
An important relational database concept is that a table is an unordered set
of rows. Rows may be returned in an arbitrary sequence unless you specify
ORDER BY. If you want data returned in the sequence in which rows were
inserted, you'll need an incrementing column like inserted datetime or
identity for the ordering.
Hope this helps.
Dan Guzman
SQL Server MVP
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:d4d2d6c5-c6e0-4612-9f57-13accf2ff8b1@.i7g2000prf.googlegroups.com...
On Jan 16, 3:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
>
>
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong
|||"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
As others have said, w/o a primary key, there's no such thing as a "last
record" defined.
And in fact, without an ORDER BY you can never guarantee the order you'll
return the data in.
SELECT * from FOO may in fact return a different order of results at
different times.
This could be due to what's in the memory cache at the time, if the engine
decides to parallize the query across different CPUs, etc.
Now, MOST LIKELY for 10 rows, a simple "select * from FOO" will return the
data in the order it was inserted but that's absolutely no guarantee this is
true.
You may want to google the definition of a "SET" or "TABLE" within SQL.
They have no inherent order.
So sorry to say, you probably can't get the answer to the question you seek
(at least not the way it's posed.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
sql

Fetch last record in a table

HI ALL,
Suppose i did not use identity for generating sequence in a
table.Also there is no sequence no column or no primary key on a
column. Then how to find last record in a table. Suppose there are 10
rows. How can i fetch 10th rows record.First you need to define 10th. What does it mean exactly? Last fetched, last
by some value, last..... Without primary key you basically dont have a
consistent approach, so some kind of definition is definitely needed here.
MC
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.|||Define your SQL SELECT statement , and THEN use ORDER BY myCol DESC -
select TOP 1. That's assuming there are 10 records.
If there are more than 10 records , and you are using SQL 2005 , you could
do something like:
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY Col2 DESC)AS RowFROm
myTableWHERE Row = 10
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.|||On Jan 16, 2:50=A0pm, "MC" <markoDOTculo@.gmailDOTcom> wrote:
> First you need to define 10th. What does it mean exactly? Last fetched, la=st
> by some value, last..... Without primary key you basically dont have a
> consistent approach, so some kind of definition is definitely needed here.=
> MC
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
> > HI ALL,
> > =A0 =A0 =A0Suppose i did not use identity for generating sequence in a
> > table.Also there is no sequence no column or no primary key on a
> > column. Then how to find last record in a table. Suppose there are 10
> > rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
HI,
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.|||MC
SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
"MC" <markoDOTculo@.gmailDOTcom> wrote in message
news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
> First you need to define 10th. What does it mean exactly? Last fetched,
> last by some value, last..... Without primary key you basically dont have
> a consistent approach, so some kind of definition is definitely needed
> here.
>
> MC
>
> "mohit" <goenka.mohit@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>> HI ALL,
>> Suppose i did not use identity for generating sequence in a
>> table.Also there is no sequence no column or no primary key on a
>> column. Then how to find last record in a table. Suppose there are 10
>> rows. How can i fetch 10th rows record.
>|||On Jan 16, 3:22=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> HI ALL,
> =A0 =A0 =A0 Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
In a set there is no such thing as last record.|||On Jan 16, 3:22=A0pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
> > First you need to define 10th. What does it mean exactly? Last fetched,
> > last by some value, last..... Without primary key you basically dont ha=ve
> > a consistent approach, so some kind of definition is definitely needed
> > here.
> > MC
> > "mohit" <goenka.mo...@.gmail.com> wrote in message
> >news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> >> HI ALL,
> >> =A0 =A0 =A0Suppose i did not use identity for generating sequence in a
> >> table.Also there is no sequence no column or no primary key on a
> >> column. Then how to find last record in a table. Suppose there are 10
> >> rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong|||On Jan 16, 3:28=A0pm, SB <othell...@.yahoo.com> wrote:
> On Jan 16, 3:22=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> > HI ALL,
> > =A0 =A0 =A0 Suppose i did not use identity for generating sequence in a
> > table.Also there is no sequence no column or no primary key on a
> > column. Then how to find last record in a table. Suppose there are 10
> > rows. How can i fetch 10th rows record.
> In a set there is no such thing as last record.
Can you tell what set means. i am asking from table how to fetch last
record i.e last row from a table|||On Jan 16, 4:45=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> On Jan 16, 3:28=A0pm, SB <othell...@.yahoo.com> wrote:
> > On Jan 16, 3:22=A0pm, mohit <goenka.mo...@.gmail.com> wrote:
> > > HI ALL,
> > > =A0 =A0 =A0 Suppose i did not use identity for generating sequence in =a
> > > table.Also there is no sequence no column or no primary key on a
> > > column. Then how to find last record in a table. Suppose there are 10
> > > rows. How can i fetch 10th rows record.
> > In a set there is no such thing as last record.
> Can you tell what set means. i am asking from table how to fetch last
> record i.e last row from a table
A table consists of a set of records. It is not a sequence so that you
have first or last record. A set is an unordered set of records.|||If the 'last' you mean last added then you need a column which will define
sequence of insertions. Something like 'DateAdded' or something like that.
Otherwise, you need to specify how do YOU know which record is 'last' when
you open sample data from table.
MC
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:a53c73e5-4685-4d12-8f94-5e8a1b94b5b8@.f10g2000hsf.googlegroups.com...
On Jan 16, 2:50 pm, "MC" <markoDOTculo@.gmailDOTcom> wrote:
> First you need to define 10th. What does it mean exactly? Last fetched,
> last
> by some value, last..... Without primary key you basically dont have a
> consistent approach, so some kind of definition is definitely needed here.
> MC
> "mohit" <goenka.mo...@.gmail.com> wrote in message
> news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
>
> > HI ALL,
> > Suppose i did not use identity for generating sequence in a
> > table.Also there is no sequence no column or no primary key on a
> > column. Then how to find last record in a table. Suppose there are 10
> > rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
HI,
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.|||> Sorry yaar but its not working. When we do order by col_name desc
> the rows gets shuffle due to which answer is coming wrong
How do you know it is not working if there is no data in the row to identify
the order of insertion?
An important relational database concept is that a table is an unordered set
of rows. Rows may be returned in an arbitrary sequence unless you specify
ORDER BY. If you want data returned in the sequence in which rows were
inserted, you'll need an incrementing column like inserted datetime or
identity for the ordering.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"mohit" <goenka.mohit@.gmail.com> wrote in message
news:d4d2d6c5-c6e0-4612-9f57-13accf2ff8b1@.i7g2000prf.googlegroups.com...
On Jan 16, 3:22 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> MC
> SELECT TOP 1 col FROM tbl ORDER BY whatever DESC
> "MC" <markoDOTculo@.gmailDOTcom> wrote in message
> news:5C08A997-8EFD-4C5B-96FD-AC49EFC9DFB6@.microsoft.com...
>
> > First you need to define 10th. What does it mean exactly? Last fetched,
> > last by some value, last..... Without primary key you basically dont
> > have
> > a consistent approach, so some kind of definition is definitely needed
> > here.
> > MC
> > "mohit" <goenka.mo...@.gmail.com> wrote in message
> >news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> >> HI ALL,
> >> Suppose i did not use identity for generating sequence in a
> >> table.Also there is no sequence no column or no primary key on a
> >> column. Then how to find last record in a table. Suppose there are 10
> >> rows. How can i fetch 10th rows record.- Hide quoted text -
> - Show quoted text -
Hi,
Sorry yaar but its not working. When we do order by col_name desc
the rows gets shuffle due to which answer is coming wrong|||"mohit" <goenka.mohit@.gmail.com> wrote in message
news:7d4851f9-331b-431d-a352-c8c5d634bf95@.f3g2000hsg.googlegroups.com...
> HI ALL,
> Suppose i did not use identity for generating sequence in a
> table.Also there is no sequence no column or no primary key on a
> column. Then how to find last record in a table. Suppose there are 10
> rows. How can i fetch 10th rows record.
As others have said, w/o a primary key, there's no such thing as a "last
record" defined.
And in fact, without an ORDER BY you can never guarantee the order you'll
return the data in.
SELECT * from FOO may in fact return a different order of results at
different times.
This could be due to what's in the memory cache at the time, if the engine
decides to parallize the query across different CPUs, etc.
Now, MOST LIKELY for 10 rows, a simple "select * from FOO" will return the
data in the order it was inserted but that's absolutely no guarantee this is
true.
You may want to google the definition of a "SET" or "TABLE" within SQL.
They have no inherent order.
So sorry to say, you probably can't get the answer to the question you seek
(at least not the way it's posed.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||>>
10th means last record. Without primary key there is no consitent
approach but suppose we dont have then how we will find.
If you don't have a column that can tell you what "last" means, then you
could return ANY row and how would you know it wasn't the "last" row?
In SQL Server, a table is an unordered set of rows by definition. If you
say SELECT TOP 1 columns FROM table or SELECT TOP 10 columns FROM table and
you don't include an ORDER BY clause, the optimizer is free to return
whichever rows it wants... and it can change its mind when you run the query
again 5 minutes later, giving you a different set. So, if you want to
identify your "last" row, you will need to have a column that you can use to
tell SQL Server "use this column to determine last."
A

Friday, March 23, 2012

Feature Request: ANSI/MS Access Unique Key Implementation

Not sure if this is the right place to post this, but:

In a future release of SQL Server, could there be an option that would implement the ANSI/MS Access version of a Unique Key.

As I understand it, the implementation of Unique Key in SQL Server considers Null as one of the values, so that there can only be one entry in the field with Null.

In MS Access and, as I understand it, in the ANSI Standard, there is an option to ignore Null entries in a Unique Key field. In this implementation, there can be many instances where Null is used, but each non-Null value must be unique.

I am building a database and in one of my tables, the field identifies a predecessor record, which could be either Null or one of the other records in the table. In order to ensure that a record is only used once, I would have liked to use the Unique Key, but can't. So, I have had to build a second table, with triggers and foreign keys, and it is ugly. Would have been so much simpler with an ANSI Unique Key.

I understand that this would have to be an option, since there is existing code that probably depends on the current implementation of the Unique Key in SQL Server, but it would have been nice to have the option to use the ANSI Implementation.

Thanks,

Flavelle

Regards,

FlavelleLow priority - the workaround is to build a schema-bound view containing the column to be indexed where the content of the column includes only those non-Null value. A unique index can then be applied to the view.sql

Wednesday, March 7, 2012

Fast Sequencing

I have a table that I am trying to bulk load 100K records via SQL. The table
has a primary key of ID and SubID. I am creating a temporary table that will
populate ID just fine, but is there a fast way I can run through these
records and assign the SubID. I have no problem doing this in VB (it runs in
2 min) , but when I try doing this in SQL using a cursor it takes hours.
The net effect is as follows.
ID, SubID
1001, 1
1001, 2
1001, 3
1002, 1
1003, 1
1003, 2
1003, 3
TIA
RobRob Diamant wrote:
> I have a table that I am trying to bulk load 100K records via SQL.
> The table has a primary key of ID and SubID. I am creating a
> temporary table that will populate ID just fine, but is there a fast
> way I can run through these records and assign the SubID. I have no
> problem doing this in VB (it runs in 2 min) , but when I try doing
> this in SQL using a cursor it takes hours.
> The net effect is as follows.
> ID, SubID
> 1001, 1
> 1001, 2
> 1001, 3
> 1002, 1
> 1003, 1
> 1003, 2
> 1003, 3
> TIA
> Rob
Cursors are slow because they are not set-based. Do it in your
application and then bulk-load the data. I don't how we would know how
to help you assign a sub id since we don't know anything about your
requirements. Are you saying the SubID values do not exist in the file?
If so, how do you know how to assign them? How do you assign the IDs for
that matter?
--
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks for the info. I already can sequence the SubID's in my application. I
am trying to find a way to cheat (ie faster) by using SQL.
In a related topic is there a way to get the select the ROWID?
Rob
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
> Cursors are slow because they are not set-based. Do it in your application
> and then bulk-load the data. I don't how we would know how to help you
> assign a sub id since we don't know anything about your requirements. Are
> you saying the SubID values do not exist in the file? If so, how do you
> know how to assign them? How do you assign the IDs for that matter?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Rob Diamant wrote:
> David,
> Thanks for the info. I already can sequence the SubID's in my
> application. I am trying to find a way to cheat (ie faster) by using
> SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
What is the ROWID as you mean it? Rows in SQL Server do not have ROW IDs
like they do in Oracle. If you just wanted an incremental ID, you could
use an IDENTITY column, and SQL Server would create the value for you.
But it appears from your sample data that your PK is the ID + SUBID. I
think adding the necesssary IDs in your application is the best option
based on the information you provided.
--
David Gugick
Imceda Software
www.imceda.com|||I don't know if this will be faster than assigning the SubID values in your
app but the example below shows how you can accomplish the task using a
set-based technique.
CREATE TABLE MyTable
(
ID int NOT NULL,
SubID int NOT NULL,
CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
)
CREATE TABLE MyStagingTable
(
SequenceNumber int IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_MyStagingTable PRIMARY KEY,
ID int NOT NULL
)
INSERT INTO MyStagingTable
SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1001
UNION ALL SELECT 1002
UNION ALL SELECT 1003
UNION ALL SELECT 1003
UNION ALL SELECT 1003
CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
SequenceNumber)
INSERT INTO MyTable
SELECT ID,
(SELECT COUNT(*)
FROM MyStagingTable b
WHERE b.ID = a.ID AND
a.SequenceNumber <= b.SequenceNumber)
FROM MyStagingTable a
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rob Diamant" <rob@.usi.com> wrote in message
news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
> David,
> Thanks for the info. I already can sequence the SubID's in my application.
> I am trying to find a way to cheat (ie faster) by using SQL.
> In a related topic is there a way to get the select the ROWID?
> Rob
>
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
>> Cursors are slow because they are not set-based. Do it in your
>> application and then bulk-load the data. I don't how we would know how to
>> help you assign a sub id since we don't know anything about your
>> requirements. Are you saying the SubID values do not exist in the file?
>> If so, how do you know how to assign them? How do you assign the IDs for
>> that matter?
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>|||Dan,
Thanks, this should give me a great start.
Rob
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uJEdf91$EHA.2676@.TK2MSFTNGP12.phx.gbl...
>I don't know if this will be faster than assigning the SubID values in your
>app but the example below shows how you can accomplish the task using a
>set-based technique.
> CREATE TABLE MyTable
> (
> ID int NOT NULL,
> SubID int NOT NULL,
> CONSTRAINT PK_MyTable PRIMARY KEY(ID, SubID)
> )
> CREATE TABLE MyStagingTable
> (
> SequenceNumber int IDENTITY(1, 1) NOT NULL
> CONSTRAINT PK_MyStagingTable PRIMARY KEY,
> ID int NOT NULL
> )
> INSERT INTO MyStagingTable
> SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1001
> UNION ALL SELECT 1002
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> UNION ALL SELECT 1003
> CREATE UNIQUE NONCLUSTERED INDEX Index1 ON MyStagingTable(ID,
> SequenceNumber)
> INSERT INTO MyTable
> SELECT ID,
> (SELECT COUNT(*)
> FROM MyStagingTable b
> WHERE b.ID = a.ID AND
> a.SequenceNumber <= b.SequenceNumber)
> FROM MyStagingTable a
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Rob Diamant" <rob@.usi.com> wrote in message
> news:uw30Mnz$EHA.612@.TK2MSFTNGP09.phx.gbl...
>> David,
>> Thanks for the info. I already can sequence the SubID's in my
>> application. I am trying to find a way to cheat (ie faster) by using SQL.
>> In a related topic is there a way to get the select the ROWID?
>> Rob
>>
>> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
>> news:OievhCz$EHA.464@.tk2msftngp13.phx.gbl...
>> Rob Diamant wrote:
>> I have a table that I am trying to bulk load 100K records via SQL.
>> The table has a primary key of ID and SubID. I am creating a
>> temporary table that will populate ID just fine, but is there a fast
>> way I can run through these records and assign the SubID. I have no
>> problem doing this in VB (it runs in 2 min) , but when I try doing
>> this in SQL using a cursor it takes hours.
>> The net effect is as follows.
>> ID, SubID
>> 1001, 1
>> 1001, 2
>> 1001, 3
>> 1002, 1
>> 1003, 1
>> 1003, 2
>> 1003, 3
>> TIA
>> Rob
>> Cursors are slow because they are not set-based. Do it in your
>> application and then bulk-load the data. I don't how we would know how
>> to help you assign a sub id since we don't know anything about your
>> requirements. Are you saying the SubID values do not exist in the file?
>> If so, how do you know how to assign them? How do you assign the IDs for
>> that matter?
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com
>>
>

Sunday, February 26, 2012

Family Tree Branches

Okie, this one has me a little stumped so bear with me an I will explain as best I can...

I have a family tree database...

The two key tables (for this problem) are Person and Child. The person table is your generic person details table and contains, PersonId, PersonFirstName, PersonLastName, PersonDOB etc. The Child table is a linking table that links a PersonId to a ChildPersonId (so both Id's are valid Id's in the person table).

Now what I want to try and achieve is a breakdown of the different branchs of the family tree. A branch is an independant, unattached section of the tree (if that makes sense). It's a grouping of people we know are related but we can't determine how they are related to another group of people in the tree.

If you look at this http://gw.geneanet.org/index.php3?b=bengos&lang=en;m=N;v=van+halewyn you will get an idea of what I mean.

I'm not sure if this is something that can be don't with a query at all the be honest... I suspect that I will have to wrap some other code around it, but I'm really not sure on what approach I should be using. Any help people could offer would be great.Are you just looking for all the roots? If that's the case, this would show you that:

SELECT c1.PersonId
FROM
Child c1
LEFT OUTER JOIN Child c2 ON c1.PersonId = c2.ChildPersonId
WHERE c2.ChildPersonId IS NULL

This will give you the root of all branches. You can then build your individual branches from there. Make sense?|||linking table can be modified with an identity column for each parent-child
relation , so that any branch of tree can be identified with the identity column.|||Are you sure that is going to work...? It would find the root nodes but there could be multiple root nodes for a tree couldn't there? The family tree itself isn't really a tree structure... it expands in both directions from any given point.

It expands up (for example) due to the children nodes and up because of the parent nodes...|||currently what I am doing is

1. Getting all PersonIds and creating an array.
2. Take the first Id and get all the children and parent and spouse ids
3. Get all the child and parent and spouse id's of those found in step 2.
- repeat until no more ids are found, this means the end of a branch
4. go back to array and select the first id that is not one of those that has been found so far
5. return to step 2 until all ideas have been found or searched on

this gives me all the branches.... not sure how to structure the display be it's a start.|||Can u post the table structure?|||CREATE TABLE [dbo].[Child] (
[PersonId] [int] NULL ,
[ChildPersonId] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Person] (
[PersonId] [int] NOT NULL ,
[Firstnames] [nvarchar] (60) NULL ,
[Surname] [nvarchar] (60) NULL ,
[Title] [nvarchar] (100) NULL ,
[Gender] [nvarchar] (1) NULL ,
[DOBDay] [int] NULL ,
[DOBMonth] [int] NULL ,
[DOBYear] [int] NULL ,
[POBLocale] [nvarchar] (50) NULL ,
[POBCountry] [nvarchar] (50) NULL ,
[DODDay] [int] NULL ,
[DODMonth] [int] NULL ,
[DODYear] [int] NULL ,
[PODLocale] [nvarchar] (50) NULL ,
[PODCountry] [nvarchar] (50) NULL ,
[POBurial] [nvarchar] (50) NULL
) ON [PRIMARY]
GO

Now for the Child table both ChildPersonId and PersonId link to a valid PersonId in the Person table|||I would like to suggest 3 more columns in Child Table. RealtionId and ParentRealtionId
And RelationHierarchy.

CREATE TABLE [dbo].[Child] (
[RelationId] [int] NOT NULL,
[ParentRealtionId] NULL,
[PersonId] [int] NULL ,
[ChildPersonId] [int] NULL ,
[RelationHierarchy] varchar(4000)
) GO

so the tables look like

Person Table:

[PersonId] [Firstnames] [Surname]
-------------------
1 Gauthier van Halewyn ..

2 Rogier van Halewyn ..

3 Jacques van Halewyn..

4 Roland van Halewyn ..

Child Table:

[RelationId] [ParentRealtionId] [PersonId] [ChildPersonId] [RelationHierarchy]
-----------------------------
10 null null 1 null
20 10 1 2 10~
30 20 2 3 10~20~
40 30 3 4 10~20~30~

assumptions: Every PersonId occurs only once as a Child PersonId in Child Table.

So to find the children and grand children of a person
All u have to do is select the RelationHierarchy and RelationId from the child table
Concat both and search.

To find the family tree of Gauthier van Halewyn (PersonId=1)

DECLARE @. RelationHierarchy VARCHAR(4000)

SELECT @. RelationHierarchy= isnull(RelationHierarchy, ) + ~ + CONVERT(Varchar(20), RelationId) + ~% FROM [Child] WHERE [ChildPersonId]= 1

SELECT * FROM [Child ] WHERE RelationHierarchy LIKE @. RelationHierarchy

I'm not sure this is going to answer all your questions but this will definetly prove helpful.|||Hmmm,... I see what you are saying... I have though about that myself,.. adding a generation field or something somewhere... I don't want to complicate things too much though,.. but I think I might need to in order to achieve what I would like... thanks for the input

Monday, March 26, 2012

Fetching Duplicate Rows

Fetch last record in a table

Fetch last record in a table

Friday, March 23, 2012

Feature Request: ANSI/MS Access Unique Key Implementation

Wednesday, March 7, 2012

Fast Sequencing

Fast Sequencing

Fast Sequencing

Sunday, February 26, 2012

Family Tree Branches

Footer rows to skip

Blog Archive

About Me