Distributed Partitions (Across multiple servers)

I have an existing table that I want to partition across multiple servers. I am having a hard time finding an article that explains how to do it.

I am in the process of designing applying a scale out architecture to our database...but have hit this brick wall.

Any help would be great.

Thanks!

Eric Elliston

SWFLParent.com

http://www.swflparent.com

Hi Eric,

I think you're thinking of distributed partitioned views. Have a look at http://msdn2.microsoft.com/en-us/library/aa479364.aspx which actually presents a few options available to you.

Cheers,

Rob

Distributed partitioned views

I'm considering distributing a large table which we
now have, for performance reasons.
We've been doing a join on this large table (11 million
rows), and I don't know whether it would help much to
partition the table across four machines.
When you perform a join on a distributed partitioned view,
where does the "work" take place. ie. If the table just
gets reconstructed at the site of the view, and then the
join operation takes place, I might actually *suffer* in
performance because of the network overhead.
Could someone advise me on this? I may be wrong about how
it works, and I'd like to understand it before undertaking
the task of distributing it.
Thanks in advance,
AndrewAndrew,
you will gain the performance enhancement as you imagined -- the join
condition will direct the work to the member table via the view. You can
verify this with the execution plan.
The key is, that your distribution will do this for all your important
joins. You may have different join conditions. If the partitioning column
is not always in the joining key, you will have to engage multiple member
tables. If this happens not two often, it might be acceptable. So here you
have to make the judgment call.
hth
Quentin
"Andrew" <a@.b.com> wrote in message
news:153501c38c0d$783e7b10$a101280a@.phx.gbl...
> I'm considering distributing a large table which we
> now have, for performance reasons.
> We've been doing a join on this large table (11 million
> rows), and I don't know whether it would help much to
> partition the table across four machines.
> When you perform a join on a distributed partitioned view,
> where does the "work" take place. ie. If the table just
> gets reconstructed at the site of the view, and then the
> join operation takes place, I might actually *suffer* in
> performance because of the network overhead.
> Could someone advise me on this? I may be wrong about how
> it works, and I'd like to understand it before undertaking
> the task of distributing it.
> Thanks in advance,
> Andrew
>|||Thanks Quentin,
I'm glad to hear that the work itself gets distributed.
It seems from your answer that the partitioning column
should be the column on which the join is performed for
optimal performance... did I read that right?
Also, I understand you want to keep as much "closely
related" data together as possible. Unfortunately, there
are several tables on which my large table will be joined,
and I'd like to keep them on the host where I create the
distributed view.
Is this going to be a problem? Or is it advisable to
partition the smaller tables as well?
Thanks in advance,
Andrew
>--Original Message--
>Andrew,
>you will gain the performance enhancement as you
imagined -- the join
>condition will direct the work to the member table via
the view. You can
>verify this with the execution plan.
>The key is, that your distribution will do this for all
your important
>joins. You may have different join conditions. If the
partitioning column
>is not always in the joining key, you will have to
engage multiple member
>tables. If this happens not two often, it might be
acceptable. So here you
>have to make the judgment call.
>hth
>Quentin
>
>"Andrew" <a@.b.com> wrote in message
>news:153501c38c0d$783e7b10$a101280a@.phx.gbl...
>> I'm considering distributing a large table which we
>> now have, for performance reasons.
>> We've been doing a join on this large table (11 million
>> rows), and I don't know whether it would help much to
>> partition the table across four machines.
>> When you perform a join on a distributed partitioned
view,
>> where does the "work" take place. ie. If the table
just
>> gets reconstructed at the site of the view, and then
the
>> join operation takes place, I might actually *suffer*
in
>> performance because of the network overhead.
>> Could someone advise me on this? I may be wrong about
how
>> it works, and I'd like to understand it before
undertaking
>> the task of distributing it.
>> Thanks in advance,
>> Andrew
>
>.
>|||Andrew,
> It seems from your answer that the partitioning column
> should be the column on which the join is performed for
> optimal performance... did I read that right?
You are right. Think of your table you want to partition by lastname, where
a-m go to server1 and the rest goes to server2. If you join or search by
firstname, what will sql server do? it of course will have to go into both
server and search the data for every entry of firstname. Had you
partitioned by firstname, the query will go to server1 finding all records
with firstnames with value a-m, and finding all the others in server2.
> Also, I understand you want to keep as much "closely
> related" data together as possible. Unfortunately, there
> are several tables on which my large table will be joined,
> and I'd like to keep them on the host where I create the
> distributed view.
>
I don't see any problem. The way you want use distributed partitioned view
to improve performance is to effectively retrieve and write -- reducing
physical read and load index/data into memory with reduced paging. For
small tables, this does not present a problem and you can keep them in a
convenient db.
> >--Original Message--
> >Andrew,
> >
> >you will gain the performance enhancement as you
> imagined -- the join
> >condition will direct the work to the member table via
> the view. You can
> >verify this with the execution plan.
> >
> >The key is, that your distribution will do this for all
> your important
> >joins. You may have different join conditions. If the
> partitioning column
> >is not always in the joining key, you will have to
> engage multiple member
> >tables. If this happens not two often, it might be
> acceptable. So here you
> >have to make the judgment call.
> >
> >hth
> >
> >Quentin
> >
> >
> >"Andrew" <a@.b.com> wrote in message
> >news:153501c38c0d$783e7b10$a101280a@.phx.gbl...
> >> I'm considering distributing a large table which we
> >> now have, for performance reasons.
> >>
> >> We've been doing a join on this large table (11 million
> >> rows), and I don't know whether it would help much to
> >> partition the table across four machines.
> >>
> >> When you perform a join on a distributed partitioned
> view,
> >> where does the "work" take place. ie. If the table
> just
> >> gets reconstructed at the site of the view, and then
> the
> >> join operation takes place, I might actually *suffer*
> in
> >> performance because of the network overhead.
> >>
> >> Could someone advise me on this? I may be wrong about
> how
> >> it works, and I'd like to understand it before
> undertaking
> >> the task of distributing it.
> >>
> >> Thanks in advance,
> >>
> >> Andrew
> >>
> >
> >
> >.
> >|||Quentin,
Thanks, you've been a great help. I'll get to designing
this thing.
All the best,
Andrew
>--Original Message--
>Andrew,
>> It seems from your answer that the partitioning column
>> should be the column on which the join is performed for
>> optimal performance... did I read that right?
>You are right. Think of your table you want to
partition by lastname, where
>a-m go to server1 and the rest goes to server2. If you
join or search by
>firstname, what will sql server do? it of course will
have to go into both
>server and search the data for every entry of
firstname. Had you
>partitioned by firstname, the query will go to server1
finding all records
>with firstnames with value a-m, and finding all the
others in server2.
>> Also, I understand you want to keep as much "closely
>> related" data together as possible. Unfortunately,
there
>> are several tables on which my large table will be
joined,
>> and I'd like to keep them on the host where I create
the
>> distributed view.
>I don't see any problem. The way you want use
distributed partitioned view
>to improve performance is to effectively retrieve and
write -- reducing
>physical read and load index/data into memory with
reduced paging. For
>small tables, this does not present a problem and you
can keep them in a
>convenient db.
>> >--Original Message--
>> >Andrew,
>> >
>> >you will gain the performance enhancement as you
>> imagined -- the join
>> >condition will direct the work to the member table via
>> the view. You can
>> >verify this with the execution plan.
>> >
>> >The key is, that your distribution will do this for
all
>> your important
>> >joins. You may have different join conditions. If
the
>> partitioning column
>> >is not always in the joining key, you will have to
>> engage multiple member
>> >tables. If this happens not two often, it might be
>> acceptable. So here you
>> >have to make the judgment call.
>> >
>> >hth
>> >
>> >Quentin
>> >
>> >
>> >"Andrew" <a@.b.com> wrote in message
>> >news:153501c38c0d$783e7b10$a101280a@.phx.gbl...
>> >> I'm considering distributing a large table which we
>> >> now have, for performance reasons.
>> >>
>> >> We've been doing a join on this large table (11
million
>> >> rows), and I don't know whether it would help much
to
>> >> partition the table across four machines.
>> >>
>> >> When you perform a join on a distributed partitioned
>> view,
>> >> where does the "work" take place. ie. If the table
>> just
>> >> gets reconstructed at the site of the view, and then
>> the
>> >> join operation takes place, I might actually
*suffer*
>> in
>> >> performance because of the network overhead.
>> >>
>> >> Could someone advise me on this? I may be wrong
about
>> how
>> >> it works, and I'd like to understand it before
>> undertaking
>> >> the task of distributing it.
>> >>
>> >> Thanks in advance,
>> >>
>> >> Andrew
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

Distributed ETL server

I work in the data warehouse team of my organization. We are currently rearchitecting our server environment. One of the ideas on the table is to devote a separate server for ETL processing. The databases would reside on several other servers. The ETL server would run the SSIS packages. I'm questioning if this would be a good idea.

The database servers would continue to carry the load of the query processing for the ETL. But with the ETL process on a separate server, the resultsets would need to go over the network for the SSIS package to then work on them. Plus, they would then have to go back over the wire to the destination server.

Are there advantages to this setup? Does this setup have better scalability? Or, would it be better to run the ETL from either the source or destination database server?

Thanks for your consideration.

Yes, maybe and maybe.

It makes sense in that you have a nice server with dedicated CPU and memory for the ETL. This is especially good for memory intensive stuff like lookups, (64bit helps too) and there is no CPU contention with SQL, but as you say you then incur the extra network cost.

The real answer lies in how much work is being done, CPU and memory wise, versus the extra network cost. I think that will depend on package complexity, types of transformations used, amount of data and general network performance, Better test it if you want a real answer.

The only other big question; is your DW SQL box busy getting hit by other "users"? Would it be advantageous to protect that box by offloading the ETL? Maybe the network cost would slow the ETL down, but protect resources for regular DW queries such that they perform better and that is more important than the network cost and general speed decline that is normally associated with extra network hops. Saying that, depending on the transformation work being done, the network hit may not be a factor. Perhaps the source extract network hit is more costly that the more localised ETL to DW hit.

It really depends....!

Distinguish User Objects and System objects

How to differentiate between user objects(table,view...) and system objects. Where is this information stored in SQL Server 7.0?
Thanks in advance,
Arr S
How to differentiate where? Are you running a query? What is it? WHat
version of SQL Server are you running?
Typically you can add one of the following to limit the resultset to only
user-defined objects, depending on how you are querying for system objects:
WHERE OBJECTPROPERTY(id, 'isMSShipped') = 0
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'isMSShipped')=0
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'isMSShipped')=0
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
> objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S
|||You could try looking in the sysobjects table of the respective databases,
and use the Type column to distinguish between system and user created
objects. Look up the help file for details.
Regards
Ray Mond
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S
|||Hi,
The information about System and UserDefined Objects is stored in
sysobjects database... there are multiple ways for know userdefined
objects...
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 1
--this statment gives all user defined tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 0
--this statment gives all system tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsForeignKey')
= 1
--this statment gives all forien key.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsPrimaryKey')
= 1
--this statment gives all primary key.
etc...
Regards-Hari Sharma
"Ray Mond" <yeohray@.hotmail.com> wrote in message news:<#Kbz7wSFEHA.3132@.TK2MSFTNGP12.phx.gbl>...
> You could try looking in the sysobjects table of the respective databases,
> and use the Type column to distinguish between system and user created
> objects. Look up the help file for details.
> --
> Regards
> Ray Mond
> "Arr S" <anonymous@.discussions.microsoft.com> wrote in message
> news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> objects. Where is this information stored in SQL Server 7.0?

Distinguish User Objects and System objects

How to differentiate between user objects(table,view...) and system objects.
Where is this information stored in SQL Server 7.0?
Thanks in advance,
Arr SHow to differentiate where? Are you running a query? What is it? WHat
version of SQL Server are you running?
Typically you can add one of the following to limit the resultset to only
user-defined objects, depending on how you are querying for system objects:
WHERE OBJECTPROPERTY(id, 'isMSShipped') = 0
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'isMSShipped')=0
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'isMSShipped')=0
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
> objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S|||You could try looking in the sysobjects table of the respective databases,
and use the Type column to distinguish between system and user created
objects. Look up the help file for details.
Regards
Ray Mond
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S|||Hi,
The information about System and UserDefined Objects is stored in
sysobjects database... there are multiple ways for know userdefined
objects...
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 1
--this statment gives all user defined tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 0
--this statment gives all system tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsForeignKey')
= 1
--this statment gives all forien key.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsPrimaryKey')
= 1
--this statment gives all primary key.
etc...
Regards-Hari Sharma
"Ray Mond" <yeohray@.hotmail.com> wrote in message news:<#Kbz7wSFEHA.3132@.TK2MSFTNGP12.phx.g
bl>...
> You could try looking in the sysobjects table of the respective databases,
> and use the Type column to distinguish between system and user created
> objects. Look up the help file for details.
> --
> Regards
> Ray Mond
> "Arr S" <anonymous@.discussions.microsoft.com> wrote in message
> news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> objects. Where is this information stored in SQL Server 7.0?

Distinguish changes by replication and changes by user

Hello,
Merge Replication, SQL Server 2005
I have got a table with an update trigger that is supposed to fire when a
user updates a field.
When replicating this database, the trigger fires when a user updates a
field *and* when the update is performed by the replication process.
How can I avoid the latter? Is there a TSQL-function to distinguish the two?
Thanks for your effort, Wolfgang
Another way is to hack into the sessionproperty
ie if ('replication_agent') <> 1 then its a user action.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Wolfgang" <Wolfgang@.discussions.microsoft.com> wrote in message
news:168538E7-89A2-45F3-ABD6-566F2C4B3760@.microsoft.com...
> Hello,
> Merge Replication, SQL Server 2005
> I have got a table with an update trigger that is supposed to fire when a
> user updates a field.
> When replicating this database, the trigger fires when a user updates a
> field *and* when the update is performed by the replication process.
> How can I avoid the latter? Is there a TSQL-function to distinguish the
> two?
> Thanks for your effort, Wolfgang
sql

Distincts databases on the same stored procedure

Hi!

I need to use two distincts databases on the same stored procedure. One, database1, is where I want to place the procedure, and where the table the procedure populates is (table1). The other, database2, hosts the table (table2) from where I select some data to put into table1 in database1.
Database1 name is fixed, while database2 name may change. So, I would like to pass database2 name as a parameter to the procedure. In this simple example, it works fine:

use database1
go
create procedure teste @.db_name varchar(10) as
exec ('select * from ' + @.db_name + '.dbo.table2')

But the problem is that in my procedure, database2 is used in a cursor, something like this:

create procedure myProcedure @.year int, @.pDatabase2 varchar(30) AS

declare ...

WHILE ...
BEGIN
...
declare cursor1 cursor for
select column1, column2 from @.pDatabase2.dbo.table2
where ...

open cursor1
fetch next from cursor1 into ...

close cursor1
deallocate cursor1

-- insert data into table
INSERT INTO table1
VALUES...

....

And I get an error if I try to use the exec! How can I do it? Can anyone help me please?

Thank you!

Try to rebuild your query; either refrain from using cursor, or from using variable for a database.

Also, you can EXEC('select column1, column2 from ' + @.pDatabase2 + '.dbo.table2 INTO ##TempTable'), then run a cursor over TempTable.

|||

You can execute the declare cursor dynamically if you use a global cursor like:

set @.dbname = quotename(@.pDatabase2)

exec ('declare cursor1 cursor global for select column1, column2 from ' + @.dbname + '.dbo.table2')

open cursor1

...

But it seems like what you are doing is unnecessary and complicated. You should instead do the following which is easier to manage.

1. Create a stored procedure in database2 that returns the expected results from table2 like:

create procedure gettable2

as

select col1, col2 from table2

2. And now do insert..exec from database1 like:

set @.sp = quotename(@.pDatabase2) + '.sys.sp_executesql'

insert into table1

exec @.sp 'gettable2'

Alternatively, you can just use approach in step #2 and issue the SELECT directly like:

set @.sp = quotename(@.pDatabase2) + '.sys.sp_executesql'

insert into table1

exec @.sp 'select col1, col2 from table2'

The SP is approach is slightly better from a security standpoint since you don't have to give SELECT permission on the table(s). With either approach, you will have to watch out for ownership chaining issues for example. See Books Online for more details on how ownership chaining works and db ownership chaining option.

|||Thank you a lot, it's much easier now!

distinct years

in my table I have a column Dates
10/03/2004 18:35:00
how can I get the list of Distinct years ?
2001
2002
2003
2004
thank youselect distinct year(Dates) from yourtable|||it works !

thank you

DISTINCT values from a table

Hi,

I am trying to output a list of data from a table, showing only one record of each TypeID.

So, for instance, I have a simple SQL query that says:

SELECT DISTINCT AlbumTypeIDFROM AlbumORDER BY AlbumTypeIDDESC

This works correctly, and gives a list of 1,2,3. But I need more information than that, I want the Description field output with the ID, but how can I do this without assigning that to be Distinc also?

When I try: SELECT DISTINCT AlbumTypeID, Description FROM Album ORDER BY AlbumTypeID DESC

The output is completely wrong.

Many thanks

My guess is that Description doesn't belong to AlbumTypeID, but to something like AlbumID. If you have an AblumType table with an AlbumTypeID and a Description, change you query to run against that table instead:

SELECT DISTINCT AlbumTypeID, Description FROM AlbumType ORDER BY AlbumTypeID DESC

|||

SELECT DISTINCT AlbumTypeID, Description FROM Album ORDER BY AlbumTypeID DESC

Presumably you have lots of album types and lots of descriptions, so this query will only return results where the combination of the two fields is different to all the other results. For example, two AlbumTypeId of 1 records with a description of "Cars" would result in a single record.

What did you want to get back?

|||

Try

SELECT DISTINCT dbo.GetFirstAlbumDescription(AlbumTypeId), AlbumTypeId FROM Album
where
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION dbo.GetFirstAlbumDescription
(
@.AlbumTypeId INT
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @.RETURN VARCHAR(100)
SELECT @.RETURN = DESCRIPTION FROM Album WHERE Id = (SELECT Min(Id) FROM Album WHERE AlbumTypeId = @.AlbumTypeId)
RETURN @.RETURN
END
GO|||When I ran it I got
AlbumTypeId
-------- ----
Album 1 of 1 1
Album 1 of 2 2
Album 1 of 3 3

(3 row(s) affected) [excess apces deleted]|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Hmmm

Basically, I have 2 tables,

One holds the Album information : AlbumID (PK), Album Description, Owner, DateOfCreation, AlbumTypeID (FK)

One holds the Album type information : AlbumTypeID (PK), TypeDescription

I want to show the last record entered into tblAlbum of each AlbumType.

So for my album table,

AlbumID Desc AlbumTypeID

1 First Record of Type 1 1

2 Second Record of Type 1 1

3 First Record of Type 2 2

So from the SP, possibly using a DISTINCT on the AlbumTypeID, I'd hope the output to be something like:

AlbumID Desc AlbumTypeID

2 Second Record of Type 1 1

3 First Record of Type 2 2

Any clues?

|||

Using the additional function
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE FUNCTION [dbo].[GetFirstAlbumId]
(
@.AlbumTypeId INT
)
RETURNS INT
AS
BEGIN
DECLARE @.RETURN INT
SELECT @.RETURN = Id FROM Album WHERE Id = (SELECT Min(Id) FROM Album WHERE AlbumTypeId = @.AlbumTypeId)
RETURN @.RETURN
END

SELECT DISTINCT dbo.GetFirstAlbumId(AlbumTypeId) as a, dbo.GetFirstAlbumDescription(AlbumTypeId) as B, AlbumTypeId FROM Album

gave

a B AlbumTypeId
---- ---------- ----
1 Album 1 of 1 1
3 Album 1 of 2 2
6 Album 1 of 3 3

|||Can you provide some more sample data with same AlbumId and different AlbumTypeId's..with expected output.|||

SELECT *

FROM Albumns a

JOIN (

SELECT AlbumnTypeID,MIN(AlbumnID) AS LowestAlbumnID

FROM Albumns

GROUP BY AlbumnTypeID) t1 ON a.AlbumnID=t1.LowestAlbumnID

DISTINCT Values

I am trying to run a query to one of two delete duplicates records. The
process I normally use is use
1) SELECT DISTINCT from the table into a second table
2) Delete all duplicate values in original table
3) Copy the disctinct values from the second table back into the original
table
Unfortunately, this time, my table has ntext fields in it. SELECT DISTINCT
does not work with ntext fields.
Does anyone have an alternative solution?
Thank you,
JLFlemingHave you thought about declaring a PRIMARY KEY?|||I have thought about it. I cannot declare a primary key if there are alread
y
duplicates in the table. Once I get rid of duplicates, I can put a primary
key in.
"--CELKO--" wrote:

> Have you thought about declaring a PRIMARY KEY?
>|||http://www.aspfaq.com/2431
http://www.aspfaq.com/2509
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:FAF44802-6129-435D-B7AE-EBBC0C042ECD@.microsoft.com...
> I am trying to run a query to one of two delete duplicates records. The
> process I normally use is use
> 1) SELECT DISTINCT from the table into a second table
> 2) Delete all duplicate values in original table
> 3) Copy the disctinct values from the second table back into the original
> table
> Unfortunately, this time, my table has ntext fields in it. SELECT
> DISTINCT
> does not work with ntext fields.
> Does anyone have an alternative solution?
> Thank you,
> JLFleming

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
ABC
1ax
2by
3c1
4nullnull
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200509/1
alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200509/1

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via droptable.com" <forum@.droptable.com> wrote in message
news:53B523BC4BB04@.droptable.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200509/1sql

Distinct Value of each column !

Hi,
I've table with following structre
create table #test
(a int,
b varchar(10),
c varchar(10)
)
insert into #Test values ('1','a','x')
insert into #Test values ('2','b','y')
insert into #Test values ('3','c','y')
insert into #Test values ('3','b','1')
insert into #Test values ('4','a',null)
insert into #Test values ('1',null,null)
now i want distinct value of
each column like
A B C
--
1 a x
2 b y
3 c 1
4 null null
How do i get this type of resultset ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1alter table #test add row_id int identity(1,1)
go
select * from
(
select *,(select count(*) from #test t
where t.row_id<=#test.row_id and t.a=#test.a)as num
from #test
) as d where num=1
"Malkesh S via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:53B523BC4BB04@.SQLMonster.com...
> Hi,
> I've table with following structre
> create table #test
> (a int,
> b varchar(10),
> c varchar(10)
> )
> insert into #Test values ('1','a','x')
> insert into #Test values ('2','b','y')
> insert into #Test values ('3','c','y')
> insert into #Test values ('3','b','1')
> insert into #Test values ('4','a',null)
> insert into #Test values ('1',null,null)
> now i want distinct value of
> each column like
> A B C
> --
> 1 a x
> 2 b y
> 3 c 1
> 4 null null
> How do i get this type of resultset ?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200509/1

Distinct Type Count

I have a weird MDX request and I'm unsure of how to accomplish this.

My relation table has two fields Department(int) and Employee Type(int)

I need to get a distinct count of the number of distinct employee types per Department.

E.g for the data below: (calculated member) DistinctTypeCount=4 (when dep=1) (four distinct types of employees in this department

Dep. Emp. Type

-

1 23

1 2

1 4

1 23

1 4

1 4

1 10

Can anyone suggest an mdx query for this calculated member? If I redesigning the relational view on which the cube is based makes things easier I can definitely go that route.

>My relation table has two fields Department(int) and Employee Type(int)

How the fields are exposed in your UDM? Are they dimension attributes? What design has the dimension?

|||yes they are dimension attributes.

Distinct Rows but All Columns

I searched but did not find the answer to my specific question...
I have a table where I need to return all columns, however, I need only
distinct rows for one of the columns. The problem is that the data
types are uniqueidentifiers.
The DISTINCT keyword works on the entire row so I cannot simply use
SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
FROM dbo.ReportingTransactions AS A
I have looked at grouping with no luck either. How can I get all
columns but distinct rows on one of the columns?
Here's my table:
CREATE TABLE [dbo].[MyTable]
(
[MyPK] [uniqueidentifier] NOT NULL,
[SomeForeignKey] [uniqueidentifier] NOT NULL,
[LastUpdated] [datetime] NOT NULL
)
Sample Data in Table
--
D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F 20
05-11-12
00:33:32.873
2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F 20
05-11-12
00:30:01.123
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:49:11.450
E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:47:27.967
9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:42:59.200
D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C 20
05-11-11
21:38:01.543
A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD 20
05-11-14
16:13:21.577
7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD 20
05-11-14
16:13:21.577
CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF 20
05-11-10
20:15:36.357
937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF 20
05-11-10
20:14:25.857
Desired Results
--
D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F 20
05-11-12
00:33:32.873
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:49:11.450
D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C 20
05-11-11
21:38:01.543
A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD 20
05-11-14
16:13:21.577
CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF 20
05-11-10
20:15:36.357SELECT * FROM MyTable
WHERE LastUpdated in (SELECT DISTINCT LastUpdated FROM MyTable)
See if that helps you.
Yosh
<Doug@.icr-consulting.com> wrote in message
news:1132090281.548288.36160@.g49g2000cwa.googlegroups.com...
>I searched but did not find the answer to my specific question...
> I have a table where I need to return all columns, however, I need only
> distinct rows for one of the columns. The problem is that the data
> types are uniqueidentifiers.
> The DISTINCT keyword works on the entire row so I cannot simply use
> SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
> FROM dbo.ReportingTransactions AS A
> I have looked at grouping with no luck either. How can I get all
> columns but distinct rows on one of the columns?
> Here's my table:
> CREATE TABLE [dbo].[MyTable]
> (
> [MyPK] [uniqueidentifier] NOT NULL,
> [SomeForeignKey] [uniqueidentifier] NOT NULL,
> [LastUpdated] [datetime] NOT NULL
> )
> Sample Data in Table
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> 2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:30:01.123
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:47:27.967
> 9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:42:59.200
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> 7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
> 937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:14:25.857
> Desired Results
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
>|||Sorry, I wasn't clear. I need the column called 'SomeForeignKey' to be
distinct. Using the same basic query you suggested but with the other
column doesn't work.
SELECT * FROM MyTable WHERE SomeForeignKey in (SELECT DISTINCT
SomeForeignKey FROM MyTable)
Returns all rows...not the rows with a DISTINCT SomeForeignKey value.|||Won't this return exactly the same recordset as SELECT * FROM MYTABLE since
LASTUPDATE will *always* be in the dataset returned by (SELECT DISTINCT
LastUpdated FROM MyTable)?
"Yosh" <yoshi@.nospam.com> wrote in message
news:ORtQG5i6FHA.1020@.TK2MSFTNGP15.phx.gbl...
> SELECT * FROM MyTable
> WHERE LastUpdated in (SELECT DISTINCT LastUpdated FROM MyTable)
> See if that helps you.
> Yosh
>
> <Doug@.icr-consulting.com> wrote in message
> news:1132090281.548288.36160@.g49g2000cwa.googlegroups.com...
>|||How would you determine which row to return? From the looks of the
desired results, what you really want is the last updated row for a
particular FK value - which is different than distinct on one column only.
-- correlated subquery
select MyPK, SomeForeignKey, LastUpdated
from mytable t1
where lastupdate = (select max(lastupdated) from mytable where
someforeignkey = t1.someforeignkey)
or
-- derived table
select t1.MyPK, t1.SomeForeignKey, t1.LastUpdated
from mytable t1
join (
select someforeignkey, max(lastUpdated) as lastupdated
from mytable
group by someforeignkey
) t2
on t1.someforeignkey = t2.someforeignkey
and t1.lastupdated = t2.lastupdated
Doug@.icr-consulting.com wrote:
> I searched but did not find the answer to my specific question...
> I have a table where I need to return all columns, however, I need only
> distinct rows for one of the columns. The problem is that the data
> types are uniqueidentifiers.
> The DISTINCT keyword works on the entire row so I cannot simply use
> SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
> FROM dbo.ReportingTransactions AS A
> I have looked at grouping with no luck either. How can I get all
> columns but distinct rows on one of the columns?
> Here's my table:
> CREATE TABLE [dbo].[MyTable]
> (
> [MyPK] [uniqueidentifier] NOT NULL,
> [SomeForeignKey] [uniqueidentifier] NOT NULL,
> [LastUpdated] [datetime] NOT NULL
> )
> Sample Data in Table
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
2005-11-12
> 00:33:32.873
> 2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F
2005-11-12
> 00:30:01.123
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:49:11.450
> E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:47:27.967
> 9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:42:59.200
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
2005-11-14
> 16:13:21.577
> 7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
2005-11-10
> 20:15:36.357
> 937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
2005-11-10
> 20:14:25.857
> Desired Results
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
2005-11-12
> 00:33:32.873
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:49:11.450
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
2005-11-10
> 20:15:36.357
>|||This comes very close:
CREATE TABLE [dbo].[MyTable]
(
[MyPK] [uniqueidentifier] NOT NULL,
[SomeForeignKey] [uniqueidentifier] NOT NULL,
[LastUpdated] [datetime] NOT NULL
)
insert into mytable values('D301D519-BC09-411B-8F31-8EACFD2E4775',
'F6DA8213-E958-4AE4-A2AB-032EE120831F', '2005-11-12 00:33:32.873')
insert into mytable values('2827DA4D-EE8F-46ED-95D2-2372F727F510',
'F6DA8213-E958-4AE4-A2AB-032EE120831F', '2005-11-12 00:30:01.123')
insert into mytable values('AC1B46B6-9C85-4FD7-830D-144E573CEFF2',
'ACA0EA1A-C729-477E-993A-073F12601FDB', '2005-11-08 20:49:11.450')
insert into mytable values('E1C45075-DEEE-47CB-8E8A-CFA37EFFA377',
'ACA0EA1A-C729-477E-993A-073F12601FDB', '2005-11-08 20:47:27.967')
insert into mytable values('9EC6A9E1-BDE1-494E-9010-13D0C786557E',
'ACA0EA1A-C729-477E-993A-073F12601FDB', '2005-11-08 20:42:59.200')
insert into mytable values('D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD',
'7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C', '2005-11-11 21:38:01.543')
insert into mytable values('A46E3001-0B4C-4669-8EA6-1409CDD1FDC5',
'8FC9E770-0B49-4656-A74A-5BD8B3C71CBD', '2005-11-14 16:13:21.577')
insert into mytable values('7AD20272-39FD-43AA-B18C-7F6D265E3962',
'8FC9E770-0B49-4656-A74A-5BD8B3C71CBD', '2005-11-14 16:13:21.577')
insert into mytable values('CF356908-9A77-4B70-8CBD-A4221DED72FC',
'9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF', '2005-11-10 20:15:36.357')
insert into mytable values('937143F8-4509-400D-81D9-B19EB02F97B0',
'9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF', '2005-11-10 20:14:25.857')
SELECT *
FROM MYTABLE T1
WHERE LASTUPDATED = (SELECT MAX(LASTUPDATED) FROM MYTABLE T2 WHERE
T1.SOMEFOREIGNKEY = T2.SOMEFOREIGNKEY)
drop table [MyTable]
The only real problem that I see is that when there are two values with the
same SOMEFOREIGNKEY and LASTUPDATED values it still returns multiple rows.
I'd have to think about that one a bit. I think the crux of the issue here
is that there is actually nothing distinct about the record that you want to
select.
<Doug@.icr-consulting.com> wrote in message
news:1132090281.548288.36160@.g49g2000cwa.googlegroups.com...
>I searched but did not find the answer to my specific question...
> I have a table where I need to return all columns, however, I need only
> distinct rows for one of the columns. The problem is that the data
> types are uniqueidentifiers.
> The DISTINCT keyword works on the entire row so I cannot simply use
> SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
> FROM dbo.ReportingTransactions AS A
> I have looked at grouping with no luck either. How can I get all
> columns but distinct rows on one of the columns?
> Here's my table:
> CREATE TABLE [dbo].[MyTable]
> (
> [MyPK] [uniqueidentifier] NOT NULL,
> [SomeForeignKey] [uniqueidentifier] NOT NULL,
> [LastUpdated] [datetime] NOT NULL
> )
> Sample Data in Table
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> 2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:30:01.123
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:47:27.967
> 9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:42:59.200
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> 7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
> 937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:14:25.857
> Desired Results
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
>|||Actually, the LastUpdated column is purely informational (as far as my
use of it). It's the SomeForeignKey column that I need to be unique.
Utlimately, I will use the SomeForeignKey column to join on another
table. Once I get the query to return the SomeForeignKey column in
distinct rows I can figure out the rest.
BTW: Thanks for you input thus far.|||Yes. You are correct.
What was I thinking.
Thanks,
Yosh
"Steve Hamilton" <shamilton@.community.nospam> wrote in message
news:OoT25Hj6FHA.3544@.TK2MSFTNGP09.phx.gbl...
> Won't this return exactly the same recordset as SELECT * FROM MYTABLE
> since LASTUPDATE will *always* be in the dataset returned by (SELECT
> DISTINCT LastUpdated FROM MyTable)?
>
>
> "Yosh" <yoshi@.nospam.com> wrote in message
> news:ORtQG5i6FHA.1020@.TK2MSFTNGP15.phx.gbl...
>|||If it is not the combination of SOMEFOREIGNKEY and LASTUPDATED then I am
having a hard time grasping what is distinct about the dataset that you want
returned. It sounds like what you want is one single record returned for
each distinct SomeForeignKey value in your table. The problem with that is
that multiple records exist in your table for the value and you have to in
some form or another tell sql server exactly which record to return, it is
not going to guess on your behalf It sounds like what you need to do is to
define some rule to determine which record for the particular SOMEFOREIGNKEY
value will be returned. Once you have done that crafting the query in the
syntax of what I submitted earlier should be feasible. Hope this helps.
<Doug@.icr-consulting.com> wrote in message
news:1132093840.266390.103410@.g43g2000cwa.googlegroups.com...
> Actually, the LastUpdated column is purely informational (as far as my
> use of it). It's the SomeForeignKey column that I need to be unique.
> Utlimately, I will use the SomeForeignKey column to join on another
> table. Once I get the query to return the SomeForeignKey column in
> distinct rows I can figure out the rest.
> BTW: Thanks for you input thus far.
>|||I looked at your postings and my replies and decided to try and clarify
things a bit. In your example that you originally posted you wanted the
following record in the returned result:
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:49:11.450
In your example data the following records contain that particular
SomeForeignKey value:
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:49:11.450
E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:47:27.967
9EC6A9E1-BDE1-494E-9010-13D0C786557E | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:42:59.200
In this case how did you pick the particular record that you wanted to
return? Once you identify the logic to pick the specific record it should
be possible to write a query that returns the expected result. If the
particular record doesn't matter you could simply use MAX(CAST(MYKEY AS
VARCHAR(36))) to identify a single distinct record.
<Doug@.icr-consulting.com> wrote in message
news:1132093840.266390.103410@.g43g2000cwa.googlegroups.com...
> Actually, the LastUpdated column is purely informational (as far as my
> use of it). It's the SomeForeignKey column that I need to be unique.
> Utlimately, I will use the SomeForeignKey column to join on another
> table. Once I get the query to return the SomeForeignKey column in
> distinct rows I can figure out the rest.
> BTW: Thanks for you input thus far.
>

distinct row count in a table.

Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.|||Try this...

SELECT COUNT(*)
FROM
(SELECT DISTINCT * FROM ch1)ch1|||Or
SELECT COUNT(DISTINCT *) AS Distinct_Rows FROM ch1|||:shocked:
select sum(case when count(*)>1 then 1 else 1 end)
from ch1 group by a,b,c,d|||threads merged

ramshree, please do not post the same question into multiple forums|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1I think that you're "close, but no banana" on this... The code that you posted will actually count the non-distinct rows (how many rows have at least one duplicated row elsewhere).

-PatP

Distinct Records

Hi All,
i want to Write the query which will give me the distinct top 1 records.
see i have my table and data in that table is in this way.
ReqNo Name Other
1 chirag
1 xyz
1 ABC
2 ZZZ
2 YYY
2 QQQ
3 NNN
i want the output as following
1 Chirag
2 ZZZ
3 NNN
the first records of each and every Reqno. i am trying this from a long time
but not able to get the same if you guys has any solution then please help m
e
out.Try this
Alter table TableName add sno int identity
go
Select * from TableName where sno in (Select min(sno) from MyTest group
by ReqNo)
go
Alter table TableName drop column sno
Madhivanan|||Hi
It should be
Alter table TableName add sno int identity
go
Select * from TableName where sno in (Select min(sno) from TableName
group
by ReqNo)
go
Alter table TableName drop column sno
Madhivanan|||Hello Chirag
You can do it as Madhivanan said if you have not created any views based on
this table. If you have any views based on this table then alterting the
table may affect the views. Views become invalid. In that case you can do
like this.
Create table #Temp (Slno int identity, ReqNo int, Name Varchar(50))
Insert into #Temp Select * From TableName
Select * from #Temp where Slno in (Select min(Slno) from #Temp
group by ReqNo)
#Temp is a temporary table. So it will be deleted automatically.
Thank you
Baiju
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1109834405.131179.254390@.l41g2000cwc.googlegroups.com...
> Hi
> It should be
> Alter table TableName add sno int identity
> go
> Select * from TableName where sno in (Select min(sno) from TableName
> group
> by ReqNo)
> go
> Alter table TableName drop column sno
>
> Madhivanan
>|||I would suggest you to create a running number and have it permantently.
creating a temp table, inserting data and doing a select on that, deleting
temp table..will be a workable solution..
but if your table has large number of records, this query takes its own
time..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Baiju" <baiju@.indus-systems.com> wrote in message
news:O#r6yd8HFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hello Chirag
> You can do it as Madhivanan said if you have not created any views based
on
> this table. If you have any views based on this table then alterting the
> table may affect the views. Views become invalid. In that case you can do
> like this.
> Create table #Temp (Slno int identity, ReqNo int, Name Varchar(50))
> Insert into #Temp Select * From TableName
> Select * from #Temp where Slno in (Select min(Slno) from #Temp
> group by ReqNo)
> #Temp is a temporary table. So it will be deleted automatically.
> Thank you
> Baiju
>
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:1109834405.131179.254390@.l41g2000cwc.googlegroups.com...
>|||try
select ReqNo, Max(Name) from ... group by ReqNo
"Chirag" wrote:

> Hi All,
> i want to Write the query which will give me the distinct top 1 records.
> see i have my table and data in that table is in this way.
> ReqNo Name Other
> 1 chirag
> 1 xyz
> 1 ABC
> 2 ZZZ
> 2 YYY
> 2 QQQ
> 3 NNN
> i want the output as following
> 1 Chirag
> 2 ZZZ
> 3 NNN
>
> the first records of each and every Reqno. i am trying this from a long ti
me
> but not able to get the same if you guys has any solution then please help
me
> out.|||If those are the only columns then there's no such thing as the "first"
for each ReqNo. You haven't identified "first" in your table and tables
have no fixed concept of order. The best you can do is probably with an
aggregate function:
SELECT reqno, MIN(name)
FROM YourTable
GROUP BY reqno
David Portas
SQL Server MVP
--

distinct query for table containing records more than 69347

What would be the best way to avoid distinct clause in query to get the result.My Query looks like

select distinct Col1 ,col2,col3,col4 from ABC where (1=1) group by Col1 ,col2,col3,col4 order by col1

ABC Contains records like

Col1 col2 col3 col4

a 1 1 1

a 2 1 1

B 2 2 2

B 1 1 1

;

;

;

how is the performance of disticnt query on Number of Rows ? HOw can i Improve my query performance if want to use distinct in my query? will adding indexes help to boost performance?

Thanks

You didn't tell us what is your desired outcome...

The WHERE clause can be removed.

The GROUP BY forces what you display.

Do you wish to collapse so that all 'a'/'B', etc is in one row?

If so, then something like:

Code Snippet

SELECT
Col1,
col2 = sum( Col2 ),
Col3 = sum( Col3 ),
Col4 = sum( Col4 )
FROM ABC
GROUP BY Col1

|||There is no reason you need both Distinct AND group by that I know of. How is the performance of the query as you have it? And why the where (1=1)... Is this part of a larger generated query?

Distinct Query

Hi everybody,

I would like to select all "id" in a table ONLY where "raff" is distinct.

I think it shoult be such a thing:

SELECT id FROM mytable WHERE (SELECT DISTINCT raff) or

SELECT id FROM (SELECT DISTINCT raff FROM mytable)

So what is the correct syntax

Thnak U

BaRRonThis sounds familiar

select id = max(id)
from tbl
group by raff
having count(*) = 1

Distinct problem - how can I sort this?

Hi All

I have a table sent to me from another source over which i have no control of the content (they don't sem to be able to get a distinct instance of persons for some reason? - anyway that is out of my control)
Now this table contains multiple instances of persons, each person has a unique identifier plus a set of dates and a field that indicates the state of that person (can be 'Curr' or 'Ex').

Fields are as follows:-

PersonID - char(10) - This is unique identifier for each person
Date1 - DateTime
P_State - char(20)

Sample of data from table1:-
-
PersonID - Date1 - P_State
A11324 1998-04-21 Curr
A11324 1999-05-01 Ex
A11324 1998-07-12 Ex
A11324 1998-05-23 Curr
B44321 1999-07-01 Curr
B11111 1999-07-01 Ex
B11111 1998-03-01 Curr
B22222 1999-03-31 Ex
B22222 1998-04-11 Curr
B33333 2004-03-10 Curr
B44443 2002-09-01 Curr
-

What I need to get is the latest instance of each person and their present p_state, I've tried many ways but can't seem to get a distinct instance of the latest record for each person, can anyone point me in the right direction please.

thanks.

select personid,max(date1),p_state

from table1

group by personid,p_state

|||thanks
but that doesn't give me a single instance of each person?

It's much the same as

select personid,date1,p_state
from table1
order by personid,date1

is it not ?

edit
Sorted now thanks, I removed the p_state in your query and then used the output as a basis for another query.

Thanks.