REMOVE DUPLICATE ROWS

Hi everyone.

How can I get the unique row from a table which contains multiple rows
that have exactly the same values.

example:
create table test (
c1 as smallint,
c2 as smallint,
c3 as smallint )

insert into test values (1,2,3)
insert into test values (1,2,3)

i want to remove whichever of the rows but I want to retain a single
row.

TIA

DiegoHi

There are several ways of doing this... You can select distinct rows into a
temporary table (See DISTINCT in books online), clear out your main table
and then re-populate it. If you have an differentiating column then you can
use that to delete rows that are not (say) the minimum value of that
column, or you could stop rows being put in the table in the first place by
having a unique index or primary key over the columns that should be
distinct, or using a not exists clause when inserting the data.

E.g.
..
SELECT DISTINCT *
INTO #SimpleExample
FROM Test

TRUNCATE TABLE TEST

INSERT INTO TEST ( c1, c2, c3 )
SELECT * FROM #SimpleExample

As there is no AS in a CREATE TABLE statement you will have problems with
this DDL, there is also no information regarding PKs etc which would have
been useful. See http://www.aspfaq.com/etiquette.asp?id=5006

John

"Diego Rey" <diegobph@.yahoo.com> wrote in message
news:e09be785.0412042052.52c8c7e5@.posting.google.c om...
> Hi everyone.
> How can I get the unique row from a table which contains multiple rows
> that have exactly the same values.
> example:
> create table test (
> c1 as smallint,
> c2 as smallint,
> c3 as smallint )
> insert into test values (1,2,3)
> insert into test values (1,2,3)
> i want to remove whichever of the rows but I want to retain a single
> row.
> TIA
> Diego|||John Bell (jbellnewsposts@.hotmail.com) writes:
> As there is no AS in a CREATE TABLE statement you will have problems with
> this DDL, there is also no information regarding PKs etc

Obviously, if he has identical rows in his table, there is no primary key.

Which all good tables in relational database is supposed to have, and thus
this explains why this operation is not a trivial one to perform. You are
simply not supposed to wind up in this situation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> Obviously, if he has identical rows in his table, there is no primary
>> key...Which all good tables in relational database is supposed to have,

Is that a "relational heresy" or a "relational orthodoxy" or an "attempt to
appease the ideas of relational theory" ?( ..kidding :-) )

--
Anithsql

Remove CRLF

I have a column in a data flow task which contains carriage return/line breaks. Is it possible to remove them with a derived column - or is there a better way to do this?

thanks in advance

Yeah, it should be possible. Use the REPLACE function. "\r\n" indicates a CRLF

-Jamie

remove a filegroup

I have a database with several filegroups.
I would like to remove 1 filegroup. This filegroup contains 1 file.
I know when I want to remove a file from a filegroup which contains multiple
files, I first have to empty that file (dbcc shrinkfile with emtyfile).
But how do you empty a file in a filegroup in which that file is the only
file.
This is necessary I think because only empty files can be removed from a
filegroup, and only an empty filegroup can be removed from a database.
Can someone help me with this one ?
thx
Franky,
You can move a table to a different filegroup by re-creating the clustered
index using the ON FILEGROUP clause of the CREATE INDEX statement. NC
indexes can be moved this way as well.
HTH
Jerry
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:A28170DF-7CC0-4855-A447-EB2AB8A9C26B@.microsoft.com...
>I have a database with several filegroups.
> I would like to remove 1 filegroup. This filegroup contains 1 file.
> I know when I want to remove a file from a filegroup which contains
> multiple
> files, I first have to empty that file (dbcc shrinkfile with emtyfile).
> But how do you empty a file in a filegroup in which that file is the only
> file.
> This is necessary I think because only empty files can be removed from a
> filegroup, and only an empty filegroup can be removed from a database.
> Can someone help me with this one ?
> thx
|||Jerry, thx for the advice, but I should have specified more in detail in my
question that that is not a good solution. There are hundreds of tables in
this file with each of them having several indexes. So I was looking for an
other kind of solution.
"Jerry Spivey" wrote:

> Franky,
> You can move a table to a different filegroup by re-creating the clustered
> index using the ON FILEGROUP clause of the CREATE INDEX statement. NC
> indexes can be moved this way as well.
> HTH
> Jerry
> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> news:A28170DF-7CC0-4855-A447-EB2AB8A9C26B@.microsoft.com...
>
>
|||Hmmm...I don't know. Might consider detach/attach moving the file(s) to
another physical location if that is the issue here.
HTH
Jerry
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:E355035F-78C2-4C69-B9D1-83401390297E@.microsoft.com...[vbcol=seagreen]
> Jerry, thx for the advice, but I should have specified more in detail in
> my
> question that that is not a good solution. There are hundreds of tables in
> this file with each of them having several indexes. So I was looking for
> an
> other kind of solution.
>
> "Jerry Spivey" wrote:
|||That does not solve my problem, I would like to delete the filegroup with
that single file in it. And be back in the state of having 1 primary
filegroup, and 1 user filegroup which is the default with several files in it.
"Jerry Spivey" wrote:

> Hmmm...I don't know. Might consider detach/attach moving the file(s) to
> another physical location if that is the issue here.
> HTH
> Jerry
> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> news:E355035F-78C2-4C69-B9D1-83401390297E@.microsoft.com...
>
>
sql

remove a filegroup

I have a database with several filegroups.
I would like to remove 1 filegroup. This filegroup contains 1 file.
I know when I want to remove a file from a filegroup which contains multiple
files, I first have to empty that file (dbcc shrinkfile with emtyfile).
But how do you empty a file in a filegroup in which that file is the only
file.
This is necessary I think because only empty files can be removed from a
filegroup, and only an empty filegroup can be removed from a database.
Can someone help me with this one ?
thxFranky,
You can move a table to a different filegroup by re-creating the clustered
index using the ON FILEGROUP clause of the CREATE INDEX statement. NC
indexes can be moved this way as well.
HTH
Jerry
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:A28170DF-7CC0-4855-A447-EB2AB8A9C26B@.microsoft.com...
>I have a database with several filegroups.
> I would like to remove 1 filegroup. This filegroup contains 1 file.
> I know when I want to remove a file from a filegroup which contains
> multiple
> files, I first have to empty that file (dbcc shrinkfile with emtyfile).
> But how do you empty a file in a filegroup in which that file is the only
> file.
> This is necessary I think because only empty files can be removed from a
> filegroup, and only an empty filegroup can be removed from a database.
> Can someone help me with this one ?
> thx|||Jerry, thx for the advice, but I should have specified more in detail in my
question that that is not a good solution. There are hundreds of tables in
this file with each of them having several indexes. So I was looking for an
other kind of solution.
"Jerry Spivey" wrote:
> Franky,
> You can move a table to a different filegroup by re-creating the clustered
> index using the ON FILEGROUP clause of the CREATE INDEX statement. NC
> indexes can be moved this way as well.
> HTH
> Jerry
> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> news:A28170DF-7CC0-4855-A447-EB2AB8A9C26B@.microsoft.com...
> >I have a database with several filegroups.
> > I would like to remove 1 filegroup. This filegroup contains 1 file.
> > I know when I want to remove a file from a filegroup which contains
> > multiple
> > files, I first have to empty that file (dbcc shrinkfile with emtyfile).
> > But how do you empty a file in a filegroup in which that file is the only
> > file.
> > This is necessary I think because only empty files can be removed from a
> > filegroup, and only an empty filegroup can be removed from a database.
> >
> > Can someone help me with this one ?
> >
> > thx
>
>|||Hmmm...I don't know. Might consider detach/attach moving the file(s) to
another physical location if that is the issue here.
HTH
Jerry
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:E355035F-78C2-4C69-B9D1-83401390297E@.microsoft.com...
> Jerry, thx for the advice, but I should have specified more in detail in
> my
> question that that is not a good solution. There are hundreds of tables in
> this file with each of them having several indexes. So I was looking for
> an
> other kind of solution.
>
> "Jerry Spivey" wrote:
>> Franky,
>> You can move a table to a different filegroup by re-creating the
>> clustered
>> index using the ON FILEGROUP clause of the CREATE INDEX statement. NC
>> indexes can be moved this way as well.
>> HTH
>> Jerry
>> "Franky" <Franky@.discussions.microsoft.com> wrote in message
>> news:A28170DF-7CC0-4855-A447-EB2AB8A9C26B@.microsoft.com...
>> >I have a database with several filegroups.
>> > I would like to remove 1 filegroup. This filegroup contains 1 file.
>> > I know when I want to remove a file from a filegroup which contains
>> > multiple
>> > files, I first have to empty that file (dbcc shrinkfile with emtyfile).
>> > But how do you empty a file in a filegroup in which that file is the
>> > only
>> > file.
>> > This is necessary I think because only empty files can be removed from
>> > a
>> > filegroup, and only an empty filegroup can be removed from a database.
>> >
>> > Can someone help me with this one ?
>> >
>> > thx
>>|||That does not solve my problem, I would like to delete the filegroup with
that single file in it. And be back in the state of having 1 primary
filegroup, and 1 user filegroup which is the default with several files in it.
"Jerry Spivey" wrote:
> Hmmm...I don't know. Might consider detach/attach moving the file(s) to
> another physical location if that is the issue here.
> HTH
> Jerry
> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> news:E355035F-78C2-4C69-B9D1-83401390297E@.microsoft.com...
> > Jerry, thx for the advice, but I should have specified more in detail in
> > my
> > question that that is not a good solution. There are hundreds of tables in
> > this file with each of them having several indexes. So I was looking for
> > an
> > other kind of solution.
> >
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Franky,
> >>
> >> You can move a table to a different filegroup by re-creating the
> >> clustered
> >> index using the ON FILEGROUP clause of the CREATE INDEX statement. NC
> >> indexes can be moved this way as well.
> >>
> >> HTH
> >>
> >> Jerry
> >> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> >> news:A28170DF-7CC0-4855-A447-EB2AB8A9C26B@.microsoft.com...
> >> >I have a database with several filegroups.
> >> > I would like to remove 1 filegroup. This filegroup contains 1 file.
> >> > I know when I want to remove a file from a filegroup which contains
> >> > multiple
> >> > files, I first have to empty that file (dbcc shrinkfile with emtyfile).
> >> > But how do you empty a file in a filegroup in which that file is the
> >> > only
> >> > file.
> >> > This is necessary I think because only empty files can be removed from
> >> > a
> >> > filegroup, and only an empty filegroup can be removed from a database.
> >> >
> >> > Can someone help me with this one ?
> >> >
> >> > thx
> >>
> >>
> >>
>
>

remove a filegroup

I have a database with several filegroups.
I would like to remove 1 filegroup. This filegroup contains 1 file.
I know when I want to remove a file from a filegroup which contains multiple
files, I first have to empty that file (dbcc shrinkfile with emtyfile).
But how do you empty a file in a filegroup in which that file is the only
file.
This is necessary I think because only empty files can be removed from a
filegroup, and only an empty filegroup can be removed from a database.
Can someone help me with this one ?
thxFranky,
You can move a table to a different filegroup by re-creating the clustered
index using the ON FILEGROUP clause of the CREATE INDEX statement. NC
indexes can be moved this way as well.
HTH
Jerry
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:A28170DF-7CC0-4855-A447-EB2AB8A9C26B@.microsoft.com...
>I have a database with several filegroups.
> I would like to remove 1 filegroup. This filegroup contains 1 file.
> I know when I want to remove a file from a filegroup which contains
> multiple
> files, I first have to empty that file (dbcc shrinkfile with emtyfile).
> But how do you empty a file in a filegroup in which that file is the only
> file.
> This is necessary I think because only empty files can be removed from a
> filegroup, and only an empty filegroup can be removed from a database.
> Can someone help me with this one ?
> thx|||Jerry, thx for the advice, but I should have specified more in detail in my
question that that is not a good solution. There are hundreds of tables in
this file with each of them having several indexes. So I was looking for an
other kind of solution.
"Jerry Spivey" wrote:

> Franky,
> You can move a table to a different filegroup by re-creating the clustered
> index using the ON FILEGROUP clause of the CREATE INDEX statement. NC
> indexes can be moved this way as well.
> HTH
> Jerry
> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> news:A28170DF-7CC0-4855-A447-EB2AB8A9C26B@.microsoft.com...
>
>|||Hmmm...I don't know. Might consider detach/attach moving the file(s) to
another physical location if that is the issue here.
HTH
Jerry
"Franky" <Franky@.discussions.microsoft.com> wrote in message
news:E355035F-78C2-4C69-B9D1-83401390297E@.microsoft.com...[vbcol=seagreen]
> Jerry, thx for the advice, but I should have specified more in detail in
> my
> question that that is not a good solution. There are hundreds of tables in
> this file with each of them having several indexes. So I was looking for
> an
> other kind of solution.
>
> "Jerry Spivey" wrote:
>|||That does not solve my problem, I would like to delete the filegroup with
that single file in it. And be back in the state of having 1 primary
filegroup, and 1 user filegroup which is the default with several files in i
t.
"Jerry Spivey" wrote:

> Hmmm...I don't know. Might consider detach/attach moving the file(s) to
> another physical location if that is the issue here.
> HTH
> Jerry
> "Franky" <Franky@.discussions.microsoft.com> wrote in message
> news:E355035F-78C2-4C69-B9D1-83401390297E@.microsoft.com...
>
>

Remove "blank" space?

I have a report that contains a table with two rows. Each of these rows
contains a table with a header row, detail row and footer row. When the
report loads, these inner tables expand to 5-20 rows of data. All well and
good. However, sometimes the top inner table has no data to render. But when
the report renders, this space is blank (the height of the row of the outer
table). So there's a big gap of whitespace at the top of the report.
Is there any way to have this space shrink to nothing (or nearly nothing)?
Or at least very small? I can't seem to find any way to make this happen.
Thanks,
Ripleywrite an expression on the visibilty expression
"Ripley" <ripley@.ihatespam.com> wrote in message
news:epyIiUg0GHA.4228@.TK2MSFTNGP06.phx.gbl...
>I have a report that contains a table with two rows. Each of these rows
>contains a table with a header row, detail row and footer row. When the
>report loads, these inner tables expand to 5-20 rows of data. All well and
>good. However, sometimes the top inner table has no data to render. But
>when the report renders, this space is blank (the height of the row of the
>outer table). So there's a big gap of whitespace at the top of the report.
> Is there any way to have this space shrink to nothing (or nearly nothing)?
> Or at least very small? I can't seem to find any way to make this happen.
> Thanks,
> Ripley
>sql