How to determine min. datetime in mdx

Say I have 2 columns, id and date as follow:

id date

1 11-Jan-2007

1 18-Jun-2007

2 21-Mar-2007

2 19-Sep-2007

How to write a mdx function to filter out on each id with the min date as follow:

id date

1 11-Jan-2007

2 21-Mar-2007

I would use ID as an dimension and this table as fact table - the date for a measure with the min aggregate function and then "select Dimension.ID on rows, measures.mindate on columns from cube"

HANNES

How to determine columns that are part of an index.

Hello,
How can I use SQL-DMO to get list of all the columns that are part of
an index?
I am able to determine if the column is primay key.
ThanksSELECT sysindexes.name, syscolumns.name, *
FROM dbo.sysindexes
JOIN dbo.sysindexkeys ON (sysindexes.id = sysindexkeys.id AND
sysindexes.indid = sysindexkeys.indid)
JOIN dbo.syscolumns ON (sysindexes.id = syscolumns.id AND
sysindexkeys.colid = syscolumns.colid)
-- all indecies of table 'cus_address'
WHERE OBJECT_NAME(sysindexes.id) = 'cus_address'
-- indicies on column 'PKadr_id'
WHERE syscolumns.name = 'PKadr_id'
-- indicies with name 'ix_adr'
WHERE sysindexes.name = 'ix_adr'
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> schrieb im Newsbeitrag
news:<76C61B54-40C9-48E4-A89E-F6FF9A76DF0C@.microsoft.com>...
> Hello,
> How can I use SQL-DMO to get list of all the columns that are part of
> an index?
> I am able to determine if the column is primay key.
> Thanks

How to delimit in Derived Column Component

If there are two columns in a Derived Column Component, Is there a way we can put a delimiter between them say a '||' symbol or so and build an expression. All I was able to do was concatenate them.

[Col1]+[Col2]

thanks in advance.

You can specify a literal value, and concatenate, as shown below. Any help?

[Col1] + "||" + [Col2]

|||

Thx for the quick reply :) That was exactly I was looking for...

How to delete the reduplicate row in a table?

There are two columns in the table1,
ID|AccountName | ContactName |
1 | ebay.com | Alex
2 | ebay.com |
Look the second row, it is reduplicate info which is absolutely same with
the first row. So I'd like to delete the second row. How to use SQL to do
that?
Cheers,
Jim
DELETE FROM
SOMETABLE ST
WHERE yourid Column >
(SELECT youridcolumn From sometable ST2 where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
--Where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
exact data. perhaps put that in a transaction to see if it works
BEGIN TRANSACTION
DELETE ...
Select ... --See the result
--Then Commit or Rollback if not as excpected
HTH, Jens Suessmeyer.
"CEO" wrote:

> There are two columns in the table1,
> ID|AccountName | ContactName |
> 1 | ebay.com | Alex
> 2 | ebay.com |
>
> Look the second row, it is reduplicate info which is absolutely same with
> the first row. So I'd like to delete the second row. How to use SQL to do
> that?
> Cheers,
> Jim
|||That should be:

> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT MIN(youridcolumn) From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT youridcolumn From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
> --Where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
> exact data. perhaps put that in a transaction to see if it works
> BEGIN TRANSACTION
> DELETE ...
> Select ... --See the result
> --Then Commit or Rollback if not as excpected
> HTH, Jens Suessmeyer.
> "CEO" wrote:
|||Thanks Jens,
I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
which is not what I wanted, becasue I want to remove the row which has NO
ContactName included. It maybe not the MIN(youridcolumn).
So what can I do?
|||CEO, This might do what you want:
delete from auction
where id in (select a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?
|||You told that the rows are about THE SAME, you didn′t mentioned something of
a missing contact name, I thought this was just a copy & paster error from
you.
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?
|||CEO, In case you have more than 2 duplicate rows , i slightly modified the
SQL statement:
create table auction(
id int,
account varchar(25),
contact varchar(25)
)
go
insert into auction values(1, 'ebay.com', 'Alex')
insert into auction values(2, 'ebay.com', '')
insert into auction values(3, 'aol.com', 'Alexsey')
insert into auction values(4, 'aol.com', '')
insert into auction values(5, 'aol.com', '')
delete from auction
where id in (select distinct a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?

How to delete the reduplicate row in a table?

There are two columns in the table1,
ID|AccountName | ContactName |
1 | ebay.com | Alex
2 | ebay.com |
Look the second row, it is reduplicate info which is absolutely same with
the first row. So I'd like to delete the second row. How to use SQL to do
that?
Cheers,
JimDELETE FROM
SOMETABLE ST
WHERE yourid Column >
(SELECT youridcolumn From sometable ST2 where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
--Where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
exact data. perhaps put that in a transaction to see if it works
BEGIN TRANSACTION
DELETE ...
Select ... --See the result
--Then Commit or Rollback if not as excpected
HTH, Jens Suessmeyer.
"CEO" wrote:
> There are two columns in the table1,
> ID|AccountName | ContactName |
> 1 | ebay.com | Alex
> 2 | ebay.com |
>
> Look the second row, it is reduplicate info which is absolutely same with
> the first row. So I'd like to delete the second row. How to use SQL to do
> that?
> Cheers,
> Jim|||That should be:
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT MIN(youridcolumn) From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
"Jens Sü�meyer" wrote:
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT youridcolumn From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
> --Where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
> exact data. perhaps put that in a transaction to see if it works
> BEGIN TRANSACTION
> DELETE ...
> Select ... --See the result
> --Then Commit or Rollback if not as excpected
> HTH, Jens Suessmeyer.
> "CEO" wrote:
> > There are two columns in the table1,
> >
> > ID|AccountName | ContactName |
> > 1 | ebay.com | Alex
> > 2 | ebay.com |
> >
> >
> > Look the second row, it is reduplicate info which is absolutely same with
> > the first row. So I'd like to delete the second row. How to use SQL to do
> > that?
> >
> > Cheers,
> > Jim|||Thanks Jens,
I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
which is not what I wanted, becasue I want to remove the row which has NO
ContactName included. It maybe not the MIN(youridcolumn).
So what can I do?|||CEO, This might do what you want:
delete from auction
where id in (select a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:
> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||You told that the rows are about THE SAME, you didn´t mentioned something of
a missing contact name, I thought this was just a copy & paster error from
you.
"CEO" wrote:
> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||CEO, In case you have more than 2 duplicate rows , i slightly modified the
SQL statement:
create table auction(
id int,
account varchar(25),
contact varchar(25)
)
go
insert into auction values(1, 'ebay.com', 'Alex')
insert into auction values(2, 'ebay.com', '')
insert into auction values(3, 'aol.com', 'Alexsey')
insert into auction values(4, 'aol.com', '')
insert into auction values(5, 'aol.com', '')
delete from auction
where id in (select distinct a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:
> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?

How to delete the reduplicate row in a table?

There are two columns in the table1,
ID|AccountName | ContactName |
1 | ebay.com | Alex
2 | ebay.com |
Look the second row, it is reduplicate info which is absolutely same with
the first row. So I'd like to delete the second row. How to use SQL to do
that?
Cheers,
JimDELETE FROM
SOMETABLE ST
WHERE yourid Column >
(SELECT youridcolumn From sometable ST2 where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
--Where
ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches the
exact data. perhaps put that in a transaction to see if it works
BEGIN TRANSACTION
DELETE ...
Select ... --See the result
--Then Commit or Rollback if not as excpected
HTH, Jens Suessmeyer.
"CEO" wrote:

> There are two columns in the table1,
> ID|AccountName | ContactName |
> 1 | ebay.com | Alex
> 2 | ebay.com |
>
> Look the second row, it is reduplicate info which is absolutely same with
> the first row. So I'd like to delete the second row. How to use SQL to do
> that?
> Cheers,
> Jim|||That should be:

> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT MIN(youridcolumn) From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> DELETE FROM
> SOMETABLE ST
> WHERE yourid Column >
> (SELECT youridcolumn From sometable ST2 where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2)
> --Where
> ST1.col1 = ST2.col1 AND ST1.col2 = ST2.col2 is the criteria that matches t
he
> exact data. perhaps put that in a transaction to see if it works
> BEGIN TRANSACTION
> DELETE ...
> Select ... --See the result
> --Then Commit or Rollback if not as excpected
> HTH, Jens Suessmeyer.
> "CEO" wrote:
>|||Thanks Jens,
I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
which is not what I wanted, becasue I want to remove the row which has NO
ContactName included. It maybe not the MIN(youridcolumn).
So what can I do?|||CEO, This might do what you want:
delete from auction
where id in (select a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||You told that the rows are about THE SAME, you didn′t mentioned something o
f
a missing contact name, I thought this was just a copy & paster error from
you.
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?|||CEO, In case you have more than 2 duplicate rows , i slightly modified the
SQL statement:
create table auction(
id int,
account varchar(25),
contact varchar(25)
)
go
insert into auction values(1, 'ebay.com', 'Alex')
insert into auction values(2, 'ebay.com', '')
insert into auction values(3, 'aol.com', 'Alexsey')
insert into auction values(4, 'aol.com', '')
insert into auction values(5, 'aol.com', '')
delete from auction
where id in (select distinct a1.id
from auction a1, auction a2
where a1.account = a2.account
and a1.contact = ' ' and a2.contact != ' ')
"CEO" wrote:

> Thanks Jens,
> I noticed this command: (SELECT MIN(youridcolumn) From sometable ST2 where
> which is not what I wanted, becasue I want to remove the row which has NO
> ContactName included. It maybe not the MIN(youridcolumn).
> So what can I do?

How to delete duplicate rows

Hi,
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
Herbert
Hi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:

> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"Herbert" wrote:

> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:

> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> than
> having
>
>

How to delete duplicate rows

Hi,
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
HerbertHi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:

> Hi,
> Currently we are having one large table in that we are having more tha
n
> 1 lacs record but most of the records are duplicates(All columns are havin
g
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"Herbert" wrote:

> Hi,
> Currently we are having one large table in that we are having more tha
n
> 1 lacs record but most of the records are duplicates(All columns are havin
g
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:

> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> than
> having
>
>

How to delete duplicate rows

Hi,
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
HerbertHi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> > Hi,
> > Currently we are having one large table in that we are having more
> than
> > 1 lacs record but most of the records are duplicates(All columns are
> having
> > same values).
> > How can i delete the particular duplicate row?
> >
> > Please give me a solution as soon as possible
> >
> > Thanks,
> > Herbert
>
>

How to delete a table in Visual Studio 2005

I need to delete a database table from my database. I can not figure out how to do it though. I tried deleting individual columns but go an error message "Drop Failed for column 'TPListHistoryId ' (Microsoft.SqlServer.Smo)

If your DB is attached to your project in VS2005 then you can open it up in server explorer and rightclick onthe table and choose delete.

If you are using sqlserver express management studio then try DROP table <tablename>

|||

Do you need to do this from within an ASP page?

The syntax is:

DROP TABLE <tablename>

You can do this directly in the query analyzer if you open up a query window. Or you could do it from within an ASP.net page. In this case, the syntax is:

SqlConnection conn =new SqlConnection(string here>);
SqlCommand cmd =new SqlCommand("DROP TABLE <tablename>", conn);
cmd.ExecuteNonQuery();

Note: If this table is referenced by foreign constraints, then you cannot simply drop this table without disabling the constraints first. To do this, you must be absolutely positive that you know what you're doing, or you'll possibly end up breaking the integrity of your database.

Good luck!

|||

I am working strictly out of SQL Server 2005. Not visual studio. Sorry I made a mistake. So how can I delete the table? Do you know?

|||

Sorry. I meant to say that I am working directly out of SQL Server 2005. So I messed up in my post. I tried dropping the table in SQL Server 2005, but I get the following error message:

Msg 3726, Level 16, State 1, Line 1

Could not drop object 'Location' because it is referenced by a FOREIGN KEY constraint.

|||

You would need to drop the child tables first. What that message is telling you is that you can't drop the table because there is another table that has a foreign key constraint tide to it. This is put in place to help maintain data integrety.

|||

Check out this previous post for a discussion on how you do a cascading delete from the bottom up:

http://forums.asp.net/p/1144446/1854498.aspx

How to define programatically the width for columns?

Hi everyone,

Either Sql2k or Sql25k are targeted if you answer to this thread. When we have source/destination files we usually wish to define its properties, the width for each field and so on. My question is related with this, how do such by-hand tasks via scripting inside the own ETL? Tedious tasks are if there are more than 20 columns.

Is it possible? I think so regarding 2005 but about 2000 I haven't idea at all how to begin. Issue comes when one programmer must alter lots of columns due to for example, a new file format from mainframe is released.

Thanks in advance for your time or advices,

Any ideas?