distributed transaction: values

Showing posts with label values. Show all posts

Tuesday, March 27, 2012

DistinctCount

I am running into a problem using DistinctCount - I have values in the report
that can either be a valid value, an empty string or null - I believe the
nulls are being discarded in the counts, but is there a way to make sure that
the empty strings are not being counted?This may be out of your jurisdiction, but it sounds like the data needs to be
cleansed? If NULL is valid in the column then you probably shouldn't have
empty string. In any case, if you're using stored procedures, which would be
the recommendation, you can perform some data cleanup there so you're left
with valid values. I'd check with the DBA on why there are both empty
strings and NULLS and use one or the other for absence of "valid data"
"Myles" wrote:
> I am running into a problem using DistinctCount - I have values in the report
> that can either be a valid value, an empty string or null - I believe the
> nulls are being discarded in the counts, but is there a way to make sure that
> the empty strings are not being counted?|||Yes J.P., thank you - you hit the nail on the head - on all accounts.
Unfortunately, I am already 'blue' in the face - but suppose the right thing
to do is hit my head again...I am not sure it is going to change anything,
however, and so am still looking for a way to filter this stuff out of the
counts. Thanks for the reply!
"JP.Sklenka" wrote:
> This may be out of your jurisdiction, but it sounds like the data needs to be
> cleansed? If NULL is valid in the column then you probably shouldn't have
> empty string. In any case, if you're using stored procedures, which would be
> the recommendation, you can perform some data cleanup there so you're left
> with valid values. I'd check with the DBA on why there are both empty
> strings and NULLS and use one or the other for absence of "valid data"
> "Myles" wrote:
> > I am running into a problem using DistinctCount - I have values in the report
> > that can either be a valid value, an empty string or null - I believe the
> > nulls are being discarded in the counts, but is there a way to make sure that
> > the empty strings are not being counted?|||Myles,
Try using the COALESCE(fieldname,0) function in your SQL to change Null
into what is more appropriate, or you could use the CASE WHEN trim(fieldname)
= â'â' THEN null ELSE fieldname END statement to change the empty strings to
nulls.
You could also consider using the filter section of the dataset.
HTH
-walter
"Myles" wrote:
> Yes J.P., thank you - you hit the nail on the head - on all accounts.
> Unfortunately, I am already 'blue' in the face - but suppose the right thing
> to do is hit my head again...I am not sure it is going to change anything,
> however, and so am still looking for a way to filter this stuff out of the
> counts. Thanks for the reply!
>
> "JP.Sklenka" wrote:
> > This may be out of your jurisdiction, but it sounds like the data needs to be
> > cleansed? If NULL is valid in the column then you probably shouldn't have
> > empty string. In any case, if you're using stored procedures, which would be
> > the recommendation, you can perform some data cleanup there so you're left
> > with valid values. I'd check with the DBA on why there are both empty
> > strings and NULLS and use one or the other for absence of "valid data"
> >
> > "Myles" wrote:
> >
> > > I am running into a problem using DistinctCount - I have values in the report
> > > that can either be a valid value, an empty string or null - I believe the
> > > nulls are being discarded in the counts, but is there a way to make sure that
> > > the empty strings are not being counted?

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/

Sunday, March 25, 2012

Distinct Report Parameter Values

How to display only distinct values/labels in a report parameter drop down?
The values are generated from the main query of the report. Thx. JLYou should have a dataset that is specifically for your report parameter. As
a matter of fact, you have it a little reversed. The report parameters
should be used to limit the query. If you are getting the data and then
using the report parameters to filter the report, you should re-evaluate. In
most cases you should limit the data coming over using query parameters
mapped to report parameters. If you filter the data and the data is of any
significant size you will have performance problems.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JL" <JL@.discussions.microsoft.com> wrote in message
news:CCDA1621-CE1A-4790-8BF7-447456ABB794@.microsoft.com...
> How to display only distinct values/labels in a report parameter drop
> down?
> The values are generated from the main query of the report. Thx. JL|||It's very helpful. That really enlightens me. Now I think I have a lot of
changes to make. Thx. JL
"Bruce L-C [MVP]" wrote:
> You should have a dataset that is specifically for your report parameter. As
> a matter of fact, you have it a little reversed. The report parameters
> should be used to limit the query. If you are getting the data and then
> using the report parameters to filter the report, you should re-evaluate. In
> most cases you should limit the data coming over using query parameters
> mapped to report parameters. If you filter the data and the data is of any
> significant size you will have performance problems.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "JL" <JL@.discussions.microsoft.com> wrote in message
> news:CCDA1621-CE1A-4790-8BF7-447456ABB794@.microsoft.com...
> > How to display only distinct values/labels in a report parameter drop
> > down?
> > The values are generated from the main query of the report. Thx. JL
>
>

DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

I need to only return rows with unique Active_Orders.Account_Number. The way it is now, even if I have the same Account_Number, it will return both, because according the statment above, it still is a unique record despite the fact the account number is the same. The Order_ID is diffrent, Service_Date_time is different, etc.

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You want it to return fewer rows than it does--specifically--only one row for each Account_Number. The question then is "Which rows do you want?" Unfortunately, there is no "any one but I don't care which one" aggregate in SQL, so you have to give a precise condition that can be evaluated in SQL to describe for a given account number, which row you want to see out of the many you're currently getting.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number
) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

Steve, I am using SQL 2005. I like the idea of the rule with the time. I'm actually going to have to put that in place anyway. If an order has the same account number within 2 minutes of the FILE_TIME, then only show one account number.

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

DISTINCT question

Hello all!

I understand that if you place a DISTINCT in a SELECT statement, it will return only unique values of that row, depending how many coumns you have. So look at this.

Code Snippet

SELECT DISTINCT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Active_Orders.Service_Date_Time, Active_Orders.Stat,

Order_Status.Status, Active_Orders.Order_ID, Active_Orders.Age, Active_Orders.DOB, Active_Orders.Rm_Desc, Active_Orders.Check_Out,

Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.FilePath, Active_Orders.Misc, Active_Orders.File_Date_Time

FROM Active_Orders INNER JOIN

Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN

Locations ON Active_Orders.Location_ID = Locations.Location_ID

WHERE (Order_Status.Status = 'InProcess') OR

(Order_Status.Status = 'Pending') OR

(Order_Status.Status = 'OnHold') OR

(Order_Status.Status = 'C1') OR

(Order_Status.Status = 'C2') OR

(Order_Status.Status = 'C3') OR

(Order_Status.Status = 'C4') OR

(Order_Status.Status = 'C5') OR

(Order_Status.Status = 'C6') OR

(Order_Status.Status = 'C7')

ORDER BY Active_Orders.Stat DESC, Order_Status.Status DESC

So, How can I return all the fields above, but eliminate account numbers are the same?

Thanks!!

Rudy

Your query currently returns distinct rows.

You might end up with something like (if your rule "the one with the latest File_Date_Time" value)

with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

)

select * from YourQuery as Q1

where rk = 1

If you aren't using SQL Server 2005, it's harder to write, but can still be done. In that case, you express this:

select <columns>

from <wherever>

where <whatever> as T1

and NOT EXISTS (

select *

from <same wherever> as T2

where <same whatever>

and T2.account_number = T1.account_number

and T2.File_Date_Time > T1.File_Date_Time

)

Steve Kass

Drew University

http://www.stevekass.com

|||

Just Try a Group statement instead

like this

SELECT a.* FROM mytable AS a

WHERE a.Account_Number IN(

SELECT B.Account_Number FROM (

SELECT Account_Number , count(Account_Number ) AS ACC

FROM mytable GROUP BY Account_Number
) AS B

WHERE B.ACC=1

)

So You'll get only the rows of The Account_numbers represented once in the table

I hope this will help

Best regards

Raimund

|||

Thank you guys for your suggestion!

So do I put "with YourQuery as (

rank() over (partition by Account_Number order by File_Date_Time desc) as rk,

this on the very top of my procedure?

Then put in my query in,

then put in "select * from YourQuery as Q1

where rk = 1"

Sorry, I'm just sure of the order of this.

Thanks!

Rudy

sql

DISTINCT Query

Hi,
I want a query to return the values in three columns, but I only
want distinct values in one of the three columns. Is this possible? I
want to do something like this:
SELECT A, DISTINCT(B), C
FROM TABLE Z
but SQL Server doesn't like this syntax.
Any ideas?
JDHi
No
SELECT DISTINCT A, B, C
FROM TABLE Z
How do you expect a valid set to come back with only one column being unique
and every other column an possible permutations?
You do you want your data to look like?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:fwY1f.47009$lq6.25552@.fed1read01...
> Hi,
> I want a query to return the values in three columns, but I only
> want distinct values in one of the three columns. Is this possible?
> I
> want to do something like this:
> SELECT A, DISTINCT(B), C
> FROM TABLE Z
> but SQL Server doesn't like this syntax.
> Any ideas?
> JD
>|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. The "unit of work" in a SELECT
statement is a row; if this were a file system, then the fields would
be scanned from left to right.
So, in terms of RDBMS, your question and attempted syntax make no
sense. Also TABLE is a reserved word, so code fails.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. Is this what you meant?
CREATE TABLE Foobar
(a INTEGER NOT NULL,
b INTEGER NOT NULL UNIQUE, -- no dups allowed!
c INTEGER NOT NULL,
.);|||Joe Delphi wrote:
> Hi,
> I want a query to return the values in three columns, but I
> only want distinct values in one of the three columns. Is this
> possible? I want to do something like this:
> SELECT A, DISTINCT(B), C
> FROM TABLE Z
> but SQL Server doesn't like this syntax.
> Any ideas?
> JD
I think you will need to show us some sample data and desired results
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Distinct on Text Column

How can I select distinct values from a table which has column datatype as
Text.
data sholud n't be truncated.
--
Regards,
Kassim.http://support.microsoft.com/kb/162032/en-us
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
> How can I select distinct values from a table which has column datatype as
> Text.
> data sholud n't be truncated.
> --
> Regards,
> Kassim.|||I do get these error, is there any other way to over come this.
Kassim.
"Jens Sü?meyer" wrote:

> http://support.microsoft.com/kb/162032/en-us
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
>
>|||I do get these error, is there any other way to over come this.
Kassim.
"Jens Sü?meyer" wrote:

> http://support.microsoft.com/kb/162032/en-us
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
>
>|||I do get these error, is there any other way to over come this.
Kassim.
"Jens Sü?meyer" wrote:

> http://support.microsoft.com/kb/162032/en-us
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
>
>|||Can you post some DDL and your query please.
"M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
news:517D91E0-FA6D-41EB-AD5C-3CAF4ED0F393@.microsoft.com...
>I do get these error, is there any other way to over come this.
> Kassim.
> "Jens Smeyer" wrote:
>|||Does the text column need to be part of the DISTINCT
operator, or can you be sure the text columns match if all
the other columns match? If you only need DISTINCT on
the other columns, one solution is to create a primary key or
unique column for the table. If myID is a unique
integer column, you could do something like this:
select * from myTable
where myID in (
select min(myID)
from myTable
group by col1, col2, col3
-- Do *not* include the text column in this list
)
If there are two different text column values for
the same (col1, col2, col3), you will get only one
of those rows.
If you need to determine if the text columns are unequal,
you could compare the first 8000 characters, or more if
you want:
select * from myTable
where not exists (
select * from myTable as Tcopy
where Tcopy.col1 = T.col1
and Tcopy.col2 = T.col2
..
and substring(Tcopy.textcol,1,8000) = substring(T.textcol,1,8000)
and substring(Tcopy.textcol,8001,8000) = substring(T.textcol,8001,8000)
and Tcopy.myID < T.myID
)
Steve Kass
Drew University
M Kassim wrote:

>How can I select distinct values from a table which has column datatype as
>Text.
>data sholud n't be truncated.
>|||Hi,
I have a table called comments, which has 2 columns commentID primarykey
and comment [Text datatype], now I would like
select distinct comment from comments.
Kassim.
---
"Steve Kass" wrote:

> Does the text column need to be part of the DISTINCT
> operator, or can you be sure the text columns match if all
> the other columns match? If you only need DISTINCT on
> the other columns, one solution is to create a primary key or
> unique column for the table. If myID is a unique
> integer column, you could do something like this:
> select * from myTable
> where myID in (
> select min(myID)
> from myTable
> group by col1, col2, col3
> -- Do *not* include the text column in this list
> )
> If there are two different text column values for
> the same (col1, col2, col3), you will get only one
> of those rows.
> If you need to determine if the text columns are unequal,
> you could compare the first 8000 characters, or more if
> you want:
> select * from myTable
> where not exists (
> select * from myTable as Tcopy
> where Tcopy.col1 = T.col1
> and Tcopy.col2 = T.col2
> ...
> and substring(Tcopy.textcol,1,8000) = substring(T.textcol,1,8000)
> and substring(Tcopy.textcol,8001,8000) = substring(T.textcol,8001,8000)
> and Tcopy.myID < T.myID
> )
> Steve Kass
> Drew University
> M Kassim wrote:
>
>sql

DISTINCT not returning data in sorted order after specific no. of

Hi
I have the problem with the DISTINCT keyword. I used the following statement
to get the distinct values from the table:
SELECT DISTINCT trade_name FROM Customer
This works absolutely perfect getting the distinct values and getting in
sorted order. But my requirement needs the statement to be as :
SELECT DISTINCT RTRIM(trade_name) FROM Customer
With the above statement, it works fine only if the table contains few 100
of records. My table contains around 6000+ records and the above statement
returns DISTINCTINCT values but does not get them in sorted order.
The same statement works fine if the there are around say 500+ records. I
see that the results displayed are distinct and sorted as well.
The field in question (trade_name) of 30 chars (char(30))
Why is this happening.Without ORDER BY the sort order is undefined for any SQL query. Add
ORDER BY:
SELECT DISTINCT trade_name
FROM Customer
ORDER BY trade_name
SELECT DISTINCT RTRIM(trade_name)
FROM Customer
ORDER BY RTRIM(trade_name)
David Portas
SQL Server MVP
--

Thursday, March 22, 2012

Distinct Function

Hi All,

I have used Distinct function in my mdx query to remove duplicate
values.

I want to know what performance effect it will have on execution of
query.

With large volume of data query is taking more time to execute with
Distinct function. If we remove it it is taking less time.

Any inputs is appreciated.

Raghu

Depends on the query, but usually it has no effect on the performance. Distinct function doesn't look at cell values - it dedups tuples from the set. And if it is placed on the axis of SELECT query, then before getting cell values, the AS engine performs Distinct internally anyway. So it would be interesting to see your exact scenario to understand why you see performance difference.

DISTINCT COUNT WITH NULL VALUES (GRAND TOTAL)

Hello,

I have a DB of professors and information related with them. I created the cube, it consist of:

Measures:

Measure group Professors:

Amount of projects (COUNT proj_id)

Amount of pulications (COUNT pub_id)

Amount of e_books (COUNT book_id)

Measure group Projects:

Distinct amount of projects (DISTINCT COUNT proj_id)

Measure group Publications:

Distinct amount of publications (DISTINCT COUNT pub_id)

Measure group E_books:

Distinct amount of e_books (DISTINCT COUNT book_id)

Calculated measures:

Amnt_Projects

iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])

Amnt_Publications

(similar to the above one)

Amnt_E_books

(similar to the above one)

Dimensions:

dimPROFESSORS

- prof_id

-surname

-name

-gender

dimPROJECTS

- proj_id

-type name

-name

dimPUBLICATIONS

- pub_id

-type name

-name

dimE_BOOKS

- book_id

-name

Data_Projects

-data_id

-years

Data_Publications

-data_id

-years

Data_E_books

-data_id

-years

For example, when I browse the cube:

prof_id Amount of projects Distinct amount of projects Amnt_Projects

1032 30 1 1

1070 90 2 2

1111 0 1 0

1137 0 1 0

1234 1404 9 9

1721 504 7 7

2661 85 5 5

... ... ... ...

6999 20 1 1

9956 50 5 5

Uknown 0

Grand Total 2421 11 11

Grand Total “11“ is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be ?59“ for all professors). How could I get the right value to be shown in Grand Total?

Any help would be appreciated as I'm very new to the MDX.

|||

Maybe you need to sum the project counts for each professor in the selection, like:

Amnt_Projects

Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects])

|||Thank You Deepak. This calculation works perfectly, just i added my calculated measure expression instead of [Measures].[ Distinct amount of projects]. I am very happy that at last it works!!!
|||Hello again. I was working with KPI's and noticed, that this calculation needs to be improved. When I try to filter by any particular professor, the Grand Total remains the same - for the projects always "59". How could I change the calculateted member?

Thank You in advance.
|||

Were you testing the KPI with the KPI Browser in BIDS? If so, please check directly with an MDX query, since the browser may be using a subselect vs. where. The query would be like:

select

KPIValue("ProfCalc") on 0

from ProfCube

where [DimProfessors].[prof_id].&

|||I tested Your SQL query, and You are right. This query works fine. But how can I make the KPI browser in BIDS show the right values (I use the Amnt_Projects calculation for the KPIValue expression)? The same with cube browser:

prof_id Amnt_Projects
1032 1
1070 2
1111 0
Grand Total 59

Thank You again. I appreciate Your help very much!
|||

In that case, a different approach may be needed, based on a recent Forum post:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group.

- Add a statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects:

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

|||I don't know if I'm doing something wrong, but in this case I get #VALUE! for every professor.
First, I created a row count measure Amnt_Projects in the Professors measure group. Than I created calculated measure as You wrote and named it Amnt_projects2.

What is more, I am creating reports using this cube. I tried to make the one similar to the "Teritory Sales Drilldown" example. But the strange thing is with Amnt_Projects (the calculation, that You provided earlier). I make this kind of drilldown: Professor (Name/Surname) and Amnt_Publications->Type of Publication and Amnt_Publications->Name of Publication and Amnt_Publications

I get the results:
Professor Type of Publication Name of Publication Amnt_Publication
Professor1 #Error
Type1 0
Name1 0
Name2 0
Type2 #Error
Name3 1
Name4 0

So when the value of Amnt_Publication is 0, everything is ok, but when it has to sum one's, it shows #Error.

Thank You!
|||By the way, I get this cind of warning in the reporting services when I preview the report:
"The Value expression for the textbox ‘Amnt_Publications’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type."
|||I have just notices that using distinct count fits me very well in this reporting services situation. It behaves very differently than in Analysis Services browser. In Reporting Services it counts distinct values and shows null value for those professors that have no publications. And in Analysis Services it shows "1" for null value. Indeed strange.

Still would appreciate Your help with those Amnt in Analysis Services, which I use for KPI value and browse in KPI browser.

Thank You!
|||"I created calculated measure as You wrote and named it Amnt_projects2" - in the approach which I suggested, there is no calculated measure. The cube script assignment applies to the new cube measure: "Amnt_Projects".|||Thank You for answering. But could You be more specific? (about "The cube script assignment applies to the new cube measure: "Amnt_Projects". ")This is my first try with SQL Server and I have only a couple of days to finish this.

What is more, I need those KPI using not the whole Amount of projects, but something like this:

KPI for projects = Amount of Type1 projects*0,6 + Amount of Type2projects *0,3 + Amount of Type3*0,1

as I mentioned, I have such kind of dimension Projects:

dimPROJECTS

- proj_id

-type name (there are three Types of projects)

-name (the name of project itself)

Thank You very much!
|||OK, I have just assigned the KPI value by myself and it works.Of course it works only in MS SQL Server Manegement Studio..I tested it by SQL query and it should work in Reporting Services. But because I'm still using that previous Amnt_projects -->Sum(existing [DimProfessors].[prof_id].[prof_id], [Measures].[ Distinct amount of projects]), it doesn't work properly in KPI browser. The KPI Value expresion is:

SUM([Dim Projects].[Type Name].&[Type1],[Measures].[Amnt_Projects])* 0.6 + SUM([Dim Projects].[Type Name].&[Type2],[Measures].[Amnt_Projects])* 0.3 + SUM([Dim Projects].[Type Name].&[Type3],[Measures].[Amnt_Projects]) * 0.1

So I would appreciate Your explanation about "The cube script assignment applies to the new cube measure: "Amnt_Projects". "

Thank You in advance!
|||

Not sure whether you reviewed the earlier post, which I provided a link to - but the approach I suggested is similar:

- Create a new "row count" measure called Amnt_Projects for the Professors measure group (this replaces the calculated measure: Amnt_Projects)

- Add this statement to the cube MDX script, assigning values at the DimProfessors leaf level to Amnt_Projects (it doesn't create any new measures):

([DimProfessors].[prof_id].[prof_id], [Measures].[Amnt_Projects]) = [Measures].[ Distinct amount of projects];

Distinct Count With Null Values (grand Total)

Hello,

I am using SQL Server 2005. I have a DB of professors and information related with them. I created the cube, it consist of:
Measures:
Measure group Professors:
Amount of projects (COUNT proj_id)
Amount of publications (COUNT pub_id)
Amount of e_books (COUNT book_id)
-----
Measure group Projects:
Distinct amount of projects (DISTINCT COUNT proj_id)
-----
Measure group Publications:
Distinct amount of publications (DISTINCT COUNT pub_id)
-----
Measure group E_books:
Distinct amount of e_books (DISTINCT COUNT book_id)
Calculated measures:
Amnt_Projects
iif ([Measures].[ Amount of projects ] = 0 OR [Measures].[ Amount of projects] = NULL,0,[Measures].[ Distinct amount of projects])
Amnt_Publications
(similar to the above one)
Amnt_E_books
(similar to the above one)
--------
Dimensions:
dimPROFESSORS
- prof_id
-surname
-name
-gender
dimPROJECTS
- proj_id
-type name
-name
dimPUBLICATIONS
- pub_id
-type name
-name
dimE_BOOKS
- book_id
-name
Date_Projects
-date_id
-years
Date_Publications
-date_id
-years
Date_E_books
-date_id
-years

For example, when I browse the cube:
prof_id____Amount of projects___Distinct amount of projects___Amnt_Projects
1032------ 30 --------1------1
1070------ 90 --------2------2
1111------ 0 --------1------0
1137------ 0 --------1------0
1234------1404--------9------9
1721------ 504--------7------7
2661------ 85 --------5------5
...------- ...--------...------...
6999------ 20--------1------1
9956------ 50--------5------5
Unknown------(empty)-------(empty)----0
Grand Total---- 2421--------11------11

Grand Total 11 is the amount of distinct projects +1 (because of the unknown member). So the last column shows the right amount of projects for the professor but I want Grand Total to sum those values and show, how many projects do the professors have (it should be 59 if for all professors). How could I get the right value to be shown in Grand Total?Any suggestions?|||if you want to include nulls in a count in t-sql, you can do something like this:

select count(distinct coalesce(mycolumn, 'THIS COLUMN IS NULL')) from mytable