Distinct?

Hi,
Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
?
How to do that? I need join just the distinct Id from a table with other
table...
Is it possible to be done ?
You could do this:
Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
(select distinct EmployeeID from SomeTable) A join EmpTable B on EmpTable.ID
= A.EmployeeID
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Paulo Roberto" <paulo.roberto@.edt.com.br> wrote in message
news:eIucQTDRIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
> SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
> ?
> How to do that? I need join just the distinct Id from a table with other
> table...
> Is it possible to be done ?
>
|||SELECT Distinct SomeTable.EmployeeID ,EmpTable.EmployeeName from
SomeTable
INNER JOIN EmpTable ON SomeTable.EmployeeID = EmpTable.ID
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Paulo Roberto" <paulo.roberto@.edt.com.br> wrote in message
news:eIucQTDRIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
> SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
> ?
> How to do that? I need join just the distinct Id from a table with other
> table...
> Is it possible to be done ?
>

Distinct?

Hi,
Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
'
How to do that? I need join just the distinct Id from a table with other
table...
Is it possible to be done ?You could do this:
Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
(select distinct EmployeeID from SomeTable) A join EmpTable B on EmpTable.ID
= A.EmployeeID
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Paulo Roberto" <paulo.roberto@.edt.com.br> wrote in message
news:eIucQTDRIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
> SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
> '
> How to do that? I need join just the distinct Id from a table with other
> table...
> Is it possible to be done ?
>|||SELECT Distinct SomeTable.EmployeeID ,EmpTable.EmployeeName from
SomeTable
INNER JOIN EmpTable ON SomeTable.EmployeeID = EmpTable.ID
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Paulo Roberto" <paulo.roberto@.edt.com.br> wrote in message
news:eIucQTDRIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
> SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
> '
> How to do that? I need join just the distinct Id from a table with other
> table...
> Is it possible to be done ?
>sql

Distinct?

Hi,
Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
'
How to do that? I need join just the distinct Id from a table with other
table...
Is it possible to be done ?You could do this:
Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
(select distinct EmployeeID from SomeTable) A join EmpTable B on EmpTable.ID
= A.EmployeeID
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Paulo Roberto" <paulo.roberto@.edt.com.br> wrote in message
news:eIucQTDRIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
> SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
> '
> How to do that? I need join just the distinct Id from a table with other
> table...
> Is it possible to be done ?
>|||SELECT Distinct SomeTable.EmployeeID ,EmpTable.EmployeeName from
SomeTable
INNER JOIN EmpTable ON SomeTable.EmployeeID = EmpTable.ID
--
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Paulo Roberto" <paulo.roberto@.edt.com.br> wrote in message
news:eIucQTDRIHA.5400@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Select Distinct(SomeTable.EmployeeID), EmpTable.EmployeeName from
> SomeTable,EmpTable Where EmpTable.ID = * Distinct(SomeTable.EmployeeID) *
> '
> How to do that? I need join just the distinct Id from a table with other
> table...
> Is it possible to be done ?
>

distinct: removing dups

should be a simple resolution but I'm not familiar enough...

I have the following [simplified] query which generates duplicate rows that I need to get rid of.

SELECT MY_ID, DESCRIPTION, NAME
FROM MYTABLE

When I insert the distinct command, DB2 tells me:
SQL0134N Improper use of a string column, host variable, constant, or
function "DESCRIPTION". SQLSTATE=42907

The datatype of DESCRIPTION is LONG VARCHAR and that cannot change nor the need to query that column. It seems this is preventing distinct from working. It will work without DESCRIPTION being pulled, of course, but again - I need that column.

How do I use SQL to remove the duplicates I am getting since distinct seemingly cannot be used in this scenario ? Can a "WHERE" clause somehow help ?

ThanksCan you use:SELECT MY_ID, DESCRIPTION, NAME
FROM MYTABLE
GROUP BY MY_ID, DESCRIPTION, NAME-PatP|||looks like group by doesn't like DESCRIPTION either... same error.

SQL0134N Improper use of a string column, host variable, constant, or
function "DESCRIPTION". SQLSTATE=42907

DISTINCT() Question

Hello,

I currently have a SQL Statement:
SELECT ID, SignDescription FROM SignTypes WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' ORDER BY SignDescription

However, it shows several duplicates (which is expected, i do have several duplicates in the database), but I don't want duplicates, so I tried the following:

SELECT DISTINCT(SignDescription), ID FROM SignTypes WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' ORDER BY SignDescription

This shows the same exact results, which if I understand right, is expected also - my ID fields are all unique so of course this is going to happen.

If I do this (omit the ID field from the results):
SELECT DISTINCT(SignDescription) FROM SignTypes WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' ORDER BY SignDescription

I get all my unique signs with a unique SignDescription, but then I don't have the ID field...which I need.
But the problem is I need the ID field returned in the same results/recordset/whateveryoucallit.
Is this even possible?

thanks,
Jamieyes, it's possible, but you need to change the specs

;)

in english, what do you actually want to do?|||Ok,

I was worried that was a little confusing.

What I want to do is return all records in my db that have unique data in the signdescription field, and I want to use only the id field and the signdescription field. (and then of course there is the rest of the statement - WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' ORDER BY SignDescription...but that part is already working for me)|||bear with me and let's go back and approach this thing from basic principles rather than from trying to browbeat the sql into shape, because until you understand the problem, writing sql is futile

suppose you have three rows, all with the same signdescription

which row do you want to keep? describe it in words|||k.
one row.|||select MAX(ID) as ID, SignDescription FROM Signs Group by SignDescription Order BY ID

or

Select MIN(ID) as ID, SignDescription FROM Signs Group by SignDescription Order BY ID

You can use this method if it doesn't matter which ID out of the duplicates you pull back. If it does matter which ID out of the duplicates you pull back then you need to reconsider the design of the table; which IMO is the best way to go.|||k.
one row.

You funny...which one

1 X
2 X
3 X

?

Read the hint sticky at the top of the thread|||KrustyDeKlown, it does not matter which id I pull, so I will try your example.

Brett, it doesn't matter which one, so 1 or 2 or 3 would work just the same for me. Thank you for being precise, I know you don't want to give a wrong answer. Which part of the hint sticky do I need to apply, (besides stating my question better) if any? (I did read it though...before my original post even)|||sample data, expected results, DDL

That part|||Thank you for being precise, I know you don't want to give a wrong answer.

Hey, I'm all for giving wong answers :D|||k.
one row.does it have to be a consistent row, or can it be a conglomeration

for example, suppose you have the following "duplicate" rows

4 fred 102 aaa y
5 fred 104 bbb x
6 fred 105 ccc z

there are three "fred" rows and you want to end up with just one

"yeah," i can hear you saying, "that's right, just one!"

would you accept this --

5 fred 102 ccc x

if so, then the sql is easy, but if not, then why not? because there never was a row like that!!!|||First off, I want to tell Brett that I didn't necessarily mean what I said about giving wrong answers. Actually I thought you and r937 were the same (so really I guess I should thank r937 for starting off being precise...but you too Brett ;) )- I didn't notice there were 2 people replying to me. Additionally, I didn't mean that one of you would give me a 'wrong' answer, I just meant, that you guys were really trying to understand my situation as to not give me something that didn't apply to me...or work for my situation.

Next, I'm not sure if I understood at first what Krusty meant when he said I can use his method if it doesn't matter which id I pull from the duplicates...is he saying that things are going to be mixed up, similar to what you are asking r937? If so, I guess it's acceptable in my current situation, as I'm not actually using the IDs right now (I just have to supply an 'id' to a function because it is a required parameter...but that's a separate subject). However, in the future, I might need to actually use that ID, which would mean that it would actually have to be 'correct'. Which brings me to my next question - what do you mean by

then why not? because there never was a row like that!!!

? I'm not sure I understand.

K, as far as the sample data, expected results, etc...if you guys want, I can give you more specific information. I just didn't realize that this was going to be this complicated when I first started this post. But I guess part of that is my fault for not being perfectly clear. ;)|||okay, here's one quick way to generate something useful

select SignDescription, max(X), max(Y), ... max(Z)
from thetable
group by SignDescription

where X, Y, Z are the other columns in the table

will that work?|||I think so. That's pretty much what I have now from what Krusty suggested. Here's my full SQL statement:

SELECT MAX(ID) AS ID, SignDescription FROM SignTypes WHERE IsInactive <> 1 AND SignDescription LIKE '" & Request.QueryString("Letters") & "%' GROUP BY SignDescription ORDER BY SignDescription

So this will work as long as it returns the correct IDs I guess. It seems to be working great, although I haven't checked if it is giving the right IDs yet.

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 w/ character data

Hello,
I need to eliminate duplicates from records containing a text data type.
Here is the query I try :
select NewsGroup.NewsGroupID,
(distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
-- Line 10
NewsGroup.OnlineFlag
from...
where...
--
And here is the error I get :
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'distinct'.
--
The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what I
am trying to do is possible. But then why this error? If this is not
possible, how else could I eliminate the duplicates?
TIADISTINCT applies to the whole result not just one column. Maybe this
will do what you intended (notice the extra bracket and comma):
SELECT newsgroup.newsgroupid,
MAX(CAST(A.translatedtext AS VARCHAR(8000))) AS newsgroupname,
newsgroup.onlineflag
FROM a
WHERE ...
GROUP BY newsgroup.newsgroupid, newsgroup.onlineflag ;
David Portas
SQL Server MVP
--|||The keyword DISTINCT needs to be before any field names. Also, CAST should b
e
outside of the parentheses. Try the following
SELECT DISTINCT NewsGroup.NewsGroupID, CAST (a.TranslatedText as
varchar(8000)) as NewsGroupName ....
"alto" wrote:

> Hello,
> I need to eliminate duplicates from records containing a text data type.
> Here is the query I try :
> --
> select NewsGroup.NewsGroupID,
> (distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
> -- Line 10
> NewsGroup.OnlineFlag
> from...
> where...
> --
> And here is the error I get :
> --
> Server: Msg 156, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'distinct'.
> --
> The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what
I
> am trying to do is possible. But then why this error? If this is not
> possible, how else could I eliminate the duplicates?
> TIA
>
>sql

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 from a join

Is there any way i can get distinct values in one column from a join of 2
tables with the same columns? FOr example:
table1, column fname
frank
bob
bob
dave
frank
A distinct yields
frank
bob
dave
table2, column fname
bob
alan
dave
alan
I want to join these tables and get one column, fname, to have:
frank
bob
dave
alan
Thanks for any help.
Bernie YaegerNevermind- figured it out:
select distinct invnum from bnlsum union select distinct invnum from bnlsumr
Bernie
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:eOLdvc%236FHA.3276@.TK2MSFTNGP15.phx.gbl...
> Is there any way i can get distinct values in one column from a join of 2
> tables with the same columns? FOr example:
> table1, column fname
> frank
> bob
> bob
> dave
> frank
> A distinct yields
> frank
> bob
> dave
> table2, column fname
> bob
> alan
> dave
> alan
> I want to join these tables and get one column, fname, to have:
> frank
> bob
> dave
> alan
> Thanks for any help.
> Bernie Yaeger
>
>
>|||Hey Bernie,
Just as an FYI: a UNION query performs a DISTINCT inherently. Although
your performance plan may not change much, using DISTINCT and UNION in
the same query is redundant.
If your tables are large, you may see some benefit by running SELECT
Distinct colname... UNION ALL... That way, the DISTINCT selection is
performed in parallel before the rsults are joined.
Stu|||Hi,
You also try it as
SELECT distinct fname
FROM
(
Select fname from Table1
UNION
Select fname from Table2
)UNION_TABLE
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Bernie Yaeger" wrote:

> Is there any way i can get distinct values in one column from a join of 2
> tables with the same columns? FOr example:
> table1, column fname
> frank
> bob
> bob
> dave
> frank
> A distinct yields
> frank
> bob
> dave
> table2, column fname
> bob
> alan
> dave
> alan
> I want to join these tables and get one column, fname, to have:
> frank
> bob
> dave
> alan
> Thanks for any help.
> Bernie Yaeger
>
>
>

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 Value

if I have 5 columns that I want to check for distinct values in all of them
(not just one columns distinct values - I want to eliminate duplicates from
the entire returned result set) will
Select DISTINCT col1,col2,col3,col4,col5 from mytable order by myfieldname
do the trick?
Do you have people in your band or group that only play by ear?
When transposing songs do those that play by ear struggle and
does it leave vocals and band members without a recording in the
correct key to practice with?
Visit http://www.jerichoband.net/jericho/keychange.htm
where you can have the song's key changed to what you need
without negatively affecting the tempo or instruments in the recording...Select DISTINCT col1,col2,col3,col4,col5 from mytable order by
myfieldname
only elimantes the combination of these columns in the resultsset, is
it that what you want to achieve ?
HTH, jens Suessmeyer.|||Just to state this right:
it only elimantes the duplicate combinations.
-Jens.|||That is exactly what DISTINCT does.
ML
http://milambda.blogspot.com/

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 Total

Hi,

I hope someone can help with my problem described below.

I have a dataset with the following information....

Code1 Amount Code2

-- --

56209087 249.35 N01L1

56209087 249.35 N01L2

56209087 249.35 N02L1

56209903 437.52 N04L1

56209903 437.52 N01L1

56209903 437.52 N01L2

56209903 437.52 N03L1

I need my SSRS report to display a total field which is the sum of the distinct "Amount" values.... (249.35 + 437.52) = 686.87. I need to get the distinct amount per Code1 and then sum these distinct amounts.

Thanks

Hi Lorraine M

Create On more Dataset (Totals) with the following Query

Eg:

SELECT SUM(Amount) AS Total FROM

(SELECT DISTINCT code1, Amount FROM [Give the First data set Query here]) AS S

In the Report Give the Following Expression for the Sum field
=Sum(Fields!Total.Value, "Totals")

This will cause executing first data set two times

|||

Hi Chandra,

Thanks for your response.

I forgot to mention that the dataset is constructed not from a query but a recordset returned by a procedure. Therefore I cannot manipulate a query to add a new column. I should have mentioned in my first post.

Lorraine.

|||

Hello Lorraine,

This will guide you exactly to solve this problem. I was experiencing the same scenario and this link helped me.

Deepak -- > http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=831083&SiteID=1

bWelcker --> http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Hope this helps..

-- Deepak

|||Thanks Deepak. I created a function similar to the example above. I have one issue to iron out but this approach should work. Thanks for your help.|||Could you please mark this as answer. Anyone with similar question can view this.

Distinct Total

Hi,

I hope someone can help with my problem described below.

I have a dataset with the following information....

Code1 Amount Code2

-- --

56209087 249.35 N01L1

56209087 249.35 N01L2

56209087 249.35 N02L1

56209903 437.52 N04L1

56209903 437.52 N01L1

56209903 437.52 N01L2

56209903 437.52 N03L1

I need my SSRS report to display a total field which is the sum of the distinct "Amount" values.... (249.35 + 437.52) = 686.87. I need to get the distinct amount per Code1 and then sum these distinct amounts.

Thanks

Hi Lorraine M

Create On more Dataset (Totals) with the following Query

Eg:

SELECT SUM(Amount) AS Total FROM

(SELECT DISTINCT code1, Amount FROM [Give the First data set Query here]) AS S

In the Report Give the Following Expression for the Sum field
=Sum(Fields!Total.Value, "Totals")

This will cause executing first data set two times

|||

Hi Chandra,

Thanks for your response.

I forgot to mention that the dataset is constructed not from a query but a recordset returned by a procedure. Therefore I cannot manipulate a query to add a new column. I should have mentioned in my first post.

Lorraine.

|||

Hello Lorraine,

This will guide you exactly to solve this problem. I was experiencing the same scenario and this link helped me.

Deepak -- > http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=831083&SiteID=1

bWelcker --> http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Hope this helps..

-- Deepak

|||Thanks Deepak. I created a function similar to the example above. I have one issue to iron out but this approach should work. Thanks for your help.|||Could you please mark this as answer. Anyone with similar question can view this.sql

DISTINCT to ShortDateString, not DISTINCT to the DateTime; How?

Hello,

I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a table

CREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )

This app also calculates the total number of proposals coming from a specific branch in a given date by
SELECTing COUNT(BranchID) FROM Proposals WHEREBranchID=@.prmBranchID ANDProposal_Date=@.prmDate
and prints them in a table (my target table).

This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"
and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the
"SELECT DISTINCT BranchID FROM Proposals".
This target table converts the DateTime values ToShortDateString so
that we are able to see comfortably which branch office has sent how many proposals in a given day.

So far so good, and everything works fine except one thing:

Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and
11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)

What I need to do is to get a result like (in a neat fashion :)

"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"

So, how to do it in a suitable way?

Thanks in advance.

Try this -

Select Distinct Convert(char(10), Proposal_Date, 101)

It will eliminate the time part from the selection

Regards

Ash

|||

This one will solve your problem:

SELECT

DISTINCTCONVERT(NCHAR(8), Proposal_Date, 112)AS Proposal_Date

FROM

Proposals

You can look up from Books Online for the date, convert functions to help you understand more about the date and time. If you need more help, please post again.

|||

Hello,

Yeah that was what I neeed to know, and it solved the problem.

(apparently, I'm still new to T-SQL :)

Thanks.

|||

I know your problem is resolved but try the link below for more about the SQL Server DateTime convert function codes. And if you want Short Datetime you use SmallDateTime data type it gives you DateTime less seconds because it has less resolution. Hope this helps.

http://www.sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Distinct Sum for my column

Hi,

Bonjour,

I want distinct sum for one of my column.But iam not able to do that.

I tried DISTINCTSUM function given inMSDN, but it always return ZERO.

My function call in FOOTER section is called first, before my DETAILS section function call.

please help me for this.

thanks and regards

Hemant

You need to add an expression to your detail cells in the column, that expression should call a custom (code) function that records all unique values into an array:

=Code.AddUniqueNumber(myField)

then in your total just call another function that sums the array you built:

=Code.SumUniqueNumbers()

this is roughly what your code should look like:

Code Snippet

dim myArray() as Integer

public function AddUniqueNumber(Byval newNumber as integer) as integer

AddUniqueNumber = newNumber

dim i as integer

for i = lbound(myArray) to ubound(myArray)

'if this array element equals the number then it isn't unique

if myArray(i) = newNumber then exit sub

next

'increase the size of the array

redim preserve myArray(ubound(myArray) + 1)

'add the new unique number to it

myArray(ubound(myArray)) = newNumber

end function

public function SumUniqueNumbers() as Integer

dim sum as integer

dim i as integer

for i = lbound(myArray) to ubound(myArray)

sum = sum + myArray(i)

next i

SumUniqueNumbers = sum

end function

Note that this code is purely of the top of my head, my VBA is rusty, and it is UNTESTED and will have syntax errors. But it gives you an indication of how to do it. You will also need to initialise your array, probably by passing the rownum in as a parameter as well, and if the rownum = 1 then reinitialise the array.

|||

hi,

thanks for the reply.

but function call in my footer is called first, where i display the sum,so the sum always come zero.

so the code doesnt works

-thanks and rgeards

Hemant

|||

HemantC wrote:

but function call in my footer is called first, where i display the sum,so the sum always come zero.

so the code doesnt works

Then you are doing something wrong.... the code concept does work, i have used it in the past.

The columns in a report are evaluated left to right, top to bottom, so if your array was zero then maybe you have one of these things wrong:

- you have not inserted a call to add a value to the array in the detail rows (or you put the call in the wrong place)

- you are making the call correctly but not adding the new value to the array like you should

- you are reinitialising the array on every call, instead of on just the first row of the table

- you are not looping through the array correctly to sum it

- there is an error in the code and you are showing a zero instead of #ERROR

What i have found helpful in the past is to write the code in the macro editor of Excel, along with a test function that calls it, then once it is performing correctly i insert the code into the report.

|||

Hi,

Thanks again.

I deleted my table and again created new one.But my footer function is called first and then my details section.

For debugging i just put a messagebox, which shows that first footer function is called.

thanks and regards

Hemant.

|||Do you mean the page footer or do you mean the subtotal on a table?

If it is the former, then try referencing the code from a hidden text box in the page body, and then refer to the hidden text box from the footer using the "reportitems" collection|||

Its in footer.

I also tried in hidden textbox.

But we cant access textbox of details section in Footer section.It gives error.

thanks

Hemant

|||

this is my code

Public orderIDs As System.Collections.Hashtable
Public total As Double

Public function CalculateSum(ByVal orderID As Object, ByVal freight As Object) As Double

If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (orderID Is Nothing) Then
CalculateSum = total
Else
If (Not orderIDs.Contains(orderID)) Then
total = total + freight
orderIDs.Add(orderID, freight)
End If
CalculateSum = total
End If
End Function

Public function SumUniqueNumbers() as Integer
System.Windows.Forms.MessageBox.Show("toto")
dim sum as integer
Dim myDE As System.Collections. DictionaryEntry

For Each myDE In orderIDs

sum =sum +myDE.Value
Next myDE
SumUniqueNumbers = sum
end function

|||workaround for that error
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1903450&SiteID=17|||

hi,

i have tried this but we cannot acess the textbox present in details section in footer.

this is the error

Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

hemant

Distinct Sum - How To?

Hello everybody,
My data looks like this:
Program StudentID CreditHours
MBA 1 4
MBA 2 3
MSC 1 4
MSC 3 8
(student id=1 is in both programs)
Then, based on the above data, I need to do a summary report by Program
and grand total. Grand total should NOT count the same student twice.
Program CreditHours
MBA 7
MSC 12
--
Total 15
Again, for the grand total I do NOT want to count the same student
twice.
It looks like what I need is DistinctSum() function, distinguishing by
parameter other than the one being summarized (StunentId in this case).
How do I accomplish this with MS Reporting Services?
I remember I was able to do that with Crystal Reports.
Of course this contrived example is only meant to present the problem.
Please advice,
Tomasz
(I apologize if this post shows twice)Hi Tom,
Welcome to the MSDN newsgroup.
Regarding on the Distinct Sum problem, it is a bit difficult to directly
calculate the distinct results from your given dataset. For the first part
in your report
MBA 7
MSC 12
this can be done through a group on the "program" column. For the grand
total value, do you think if convenient that we put another Dataset on the
report which already queried the distinct data(according to the StudentID
column) from datasource? Thus, we can make our Grand total textbox bind to
that dataset. This is done at datasource/database side.
So far there is not direct support on calculate distinct sum in SSRS,
however, there are some other members use the custom code to calculate such
value, you can have a look at the following article which may give you some
idea on this:
http://72.14.203.104/search?q=cache:sSV0CC-nWpwJ:forums.microsoft.com/MSDN/S
howPost.aspx%3FPostID%3D240606%26SiteID%3D1+SSRS+2005+grouping+distinct+data
+&hl=en&ct=clnk&cd=1
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Well, I tried something similar already.
The problem I had was that SSRS processes the outer groups first, so the
however stored "running sum", even checked against some dictionary of
already added values first, is simply not available when the outer group
total, like grand total, is being rendered.
For the grand total or report total I can add additional textbox, outside
the table, but this actually does not solve my problem since my reports are
grouped on 4 levels and I need distinct sum on levels 3 and 4.
Another question: is it guaranteed that some textbox placed under a table
renders in server memory always after the table renders? I would rather not
relay on such assumptions.
Another method I tried was adding some hidden, calculated field with
function like: =Fields!CreditHours.Value /
CountDistinct(Fields!Program.Value, "Session") and than adding those
adjusted values on appropriate levels but then, again, due to the order SSRS
processes groups (outer first) this did not work.
Is there any way I can extend SSRS functionality and add DistinctSum(value,
id) function? I would rather spent some time doing that than resource to SQL
Server based solutions, which, in may case might not even be an option since
I need distinct sums on mid grouping levels.
Please advice.
Tomasz
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:G$xgn1tUGHA.5016@.TK2MSFTNGXA01.phx.gbl...
> Hi Tom,
> Welcome to the MSDN newsgroup.
> Regarding on the Distinct Sum problem, it is a bit difficult to directly
> calculate the distinct results from your given dataset. For the first part
> in your report
> MBA 7
> MSC 12
> this can be done through a group on the "program" column. For the grand
> total value, do you think if convenient that we put another Dataset on the
> report which already queried the distinct data(according to the StudentID
> column) from datasource? Thus, we can make our Grand total textbox bind to
> that dataset. This is done at datasource/database side.
> So far there is not direct support on calculate distinct sum in SSRS,
> however, there are some other members use the custom code to calculate
> such
> value, you can have a look at the following article which may give you
> some
> idea on this:
> http://72.14.203.104/search?q=cache:sSV0CC-nWpwJ:forums.microsoft.com/MSDN/S
> howPost.aspx%3FPostID%3D240606%26SiteID%3D1+SSRS+2005+grouping+distinct+data
> +&hl=en&ct=clnk&cd=1
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Thanks for your response Tomasz,
I'm afraid this would be a bit limited here. As far as I know, SSRS is
planning to provider user ability to create own report item(like Image,
List ,Line...), however, so far this is not available in the current
version, no public document on this. If you do want to do some
customization or extension on this, I think you may consider contacting the
MS consulting service since they usually focus on such decent requests.
Anyway, I'm sorry for the inconvenience this brings you.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Is there any way I can access underlying DataSet? I believe SSRS internally
uses DataSet object, does not it?
That would help in solving my problem because I could access it
programmatically and do all the custom summarization myself.
Tomasz
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:spQjOH%23UGHA.1364@.TK2MSFTNGXA01.phx.gbl...
> Thanks for your response Tomasz,
> I'm afraid this would be a bit limited here. As far as I know, SSRS is
> planning to provider user ability to create own report item(like Image,
> List ,Line...), however, so far this is not available in the current
> version, no public document on this. If you do want to do some
> customization or extension on this, I think you may consider contacting
> the
> MS consulting service since they usually focus on such decent requests.
> Anyway, I'm sorry for the inconvenience this brings you.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Thanks for your followup Tomasz,
I'm afraid so far the reporting service interface dosn't support this since
underlying dataset is not exposed. Actually this limitation also somewhat
related to the particular table used in your case, the certain table which
contains the two columns does not quite conform to the NF rules which make
the common RS items unable to do the work at report level. And yes, I
totally agree with you that this can be done if there has interface which
expose the underlying dataset object, however, so far we still need to wait
for the later update or version.
Thanks for your understanding.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)