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 PROBLEM

i have two fields. One is id (otomatic number) and another is email..
there are some duplicate records in email records.
I want to use distinct query for email field to eliminate duplicate
records..I also want to get id fields of resultset of distinct email.
I thought that there are more than one id value for duplicate records. Can i
get max or min id valure of duplicate records or what algorithm sql server
does use ?Maybe something like...
SELECT email, MAX([ID]) AS [ID]
FROM table
GROUP BY email
ORDER BY email
HTH,
Ben
"Savas Ates" <in da club> wrote in message
news:OtQCQLyNGHA.916@.TK2MSFTNGP10.phx.gbl...
>i have two fields. One is id (otomatic number) and another is email..
> there are some duplicate records in email records.
> I want to use distinct query for email field to eliminate duplicate
> records..I also want to get id fields of resultset of distinct email.
> I thought that there are more than one id value for duplicate records. Can
> i
> get max or min id valure of duplicate records or what algorithm sql server
> does use ?
>|||Untested...
Select A.ID, A.EMAIL from MYTABLE A
where exists (select 1 from MYTABLE B
where B.EMAIL = A.EMAIL
and B.ID < A.ID)
This should select all rows with duplicate EMAIL except for the EMAIL with
the lowest numbered ID.
You could also use:
select A.EMAIL, count(*) from MYTABLE A
group by A.EMAIL
having COUNT(*) > 1
Which will give you a list of all EMAILs occuring more than once, along with
how many times they occur. After you ge the data cleaned up you can add a
unique constraint which will prevetn duplicate emails from getting inserted
in the future.
"Savas Ates" <in da club> wrote in message
news:OtQCQLyNGHA.916@.TK2MSFTNGP10.phx.gbl...
> i have two fields. One is id (otomatic number) and another is email..
> there are some duplicate records in email records.
> I want to use distinct query for email field to eliminate duplicate
> records..I also want to get id fields of resultset of distinct email.
> I thought that there are more than one id value for duplicate records. Can
i
> get max or min id valure of duplicate records or what algorithm sql server
> does use ?
>sql

DISTINCT OR GROUP BY

I have some records in my Sql Db.
There are some duplicate record in my email and adsoyad fields
Email Adsoyad
a@.a.com Savas
a@.a.com Pele
b@.b.com Savas
c@.c.om Ilim
d@.d.com Hasan
d@.d.com Hasan
I want to eliminate email address which can be duplicate and its Adsoyad
field.
There can be different adsoyad records for duplicate email records. I want
to select one of them which doesnt have any importance for me .
I also have soma email records which doesnt have @. character. I want to
eliminate those records too. Is there any command in SQL like INSTR ?Do you want to delete the row or update the fields?
For deleting the rows with email without '@.'
Delete from table where email not like '_%@._%'
I put the additional requirement that there must be at least one char in
front of and 1 char behind the @. sign...
The second thing you wanted to do is delete the dupe emails by picking the
Adsoyad which does not have meaning for you... You'll have to repst and
describe how you determine which Adsoyad doesn't have meaning and someone
will help you with the SQL...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Savas Ates" wrote:

> I have some records in my Sql Db.
> There are some duplicate record in my email and adsoyad fields
> Email Adsoyad
> a@.a.com Savas
> a@.a.com Pele
> b@.b.com Savas
> c@.c.om Ilim
> d@.d.com Hasan
> d@.d.com Hasan
> I want to eliminate email address which can be duplicate and its Adsoyad
> field.
> There can be different adsoyad records for duplicate email records. I want
> to select one of them which doesnt have any importance for me .
> I also have soma email records which doesnt have @. character. I want to
> eliminate those records too. Is there any command in SQL like INSTR ?
>
>|||> There can be different adsoyad records for duplicate email records. I want
> to select one of them which doesnt have any importance for me .
Use DISTINCT, then you can tell from your query that that is your purpose.
GROUP BY is typically used for aggregation.

> I also have soma email records which doesnt have @. character. I want to
> eliminate those records too. Is there any command in SQL like INSTR ?
Yes, look at CHARINDEX, PATINDEX. You might also consider a function or
even a check constraint that actually validates the format of an e-mail
address. Then you can't get any crap in there in the first place. Search
groups.google.com, there are plenty of examples out there ready to use.|||IT doesnt matter which adsoyad Record im gonna choose. I want to just pick
one of adsoyad records ?
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com>, haber iletisinde
unlar yazd:E9100F46-9958-4408-B527-2209F25B40B5@.microsoft.com...
> Do you want to delete the row or update the fields?
> For deleting the rows with email without '@.'
> Delete from table where email not like '_%@._%'
> I put the additional requirement that there must be at least one char in
> front of and 1 char behind the @. sign...
> The second thing you wanted to do is delete the dupe emails by picking the
> Adsoyad which does not have meaning for you... You'll have to repst and
> describe how you determine which Adsoyad doesn't have meaning and someone
> will help you with the SQL...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Savas Ates" wrote:
>|||> IT doesnt matter which adsoyad Record im gonna choose. I want to just pick
> one of adsoyad records ?
Do you need adsoyad in the result? Can you provide more clear requirements
so we don't have to ask 80 follow-up questions? Please see
http://www.aspfaq.com/5006|||Yep I need adsoyad records too. But it doesnt matter which one i can come
up. I want to elimitate email addresses which are dublicate and adsoyad
records whics is tied to one of the duplicate email addresses. I should say
that i dont need to chooese any adsoyad records exactly . Just wanna pick
up one of them .
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%2366CMQ1TGHA.1236@.TK2MSFTNGP11.phx.gbl...
> Do you need adsoyad in the result? Can you provide more clear
> requirements so we don't have to ask 80 follow-up questions? Please see
> http://www.aspfaq.com/5006
>|||(reposting using the Microsoft's site, since the Google Groups post was not
found here)
Hi, Savas
Try something like this (untested):
SELECT Email, MIN(Adsoyad)
FROM YourTable
WHERE Email LIKE '_%@._%._%' AND Email NOT LIKE '%@.%@.%'
GROUP BY Email
Note that the LIKE expression above (although more complex than what
you have suggested), still doesn't ensure a valid e-mail address (as
per RFC 822).
Razvan

DISTINCT not working

I'm using the last version of JDBC driver, and the following query is
returning duplicate values:
SELECT DISTINCT COMPANY_ID C1 FROM COMPANY_WORKERS
By if i make de following query, it works:
SELECT A.C1 FROM (SELECT DISTINCT COMPANY_ID C1 FROM COMPANY_WORKERS ) A
Both queries work ok in the SQL Analyzer, but in the code only the second
one works as espected.
Thanks
Gon?alo
Gonalo,
I ran a quick test with the MS driver using SELECT DISTINCT and it worked.
Can you post the code that you are using? Perhaps there is something else
that is happening.
Thanks.
SuePurkis
DataDirect Technologies
"Gonalo" <Gonalo@.discussions.microsoft.com> wrote in message
news:B3FCD484-53E7-42E3-AD22-26236C9F3927@.microsoft.com...
> I'm using the last version of JDBC driver, and the following query is
> returning duplicate values:
> SELECT DISTINCT COMPANY_ID C1 FROM COMPANY_WORKERS
> By if i make de following query, it works:
> SELECT A.C1 FROM (SELECT DISTINCT COMPANY_ID C1 FROM COMPANY_WORKERS ) A
> Both queries work ok in the SQL Analyzer, but in the code only the second
> one works as espected.
> Thanks
> Gonalo
sql

DISTINCT function does not remove the duplicate member

Hi All,

The following query returns 4 members ([Gross Sales], [Trade Sales], [Intercompany Sales], [Trade Sales]) while it should return only 3. DISTINCT function does not remove the duplicated member [Trade Sales].

SELECT

{[Measures].[Amount]}

ON AXIS(0),

DISTINCT (DESCENDANTS({ [Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after))

ON AXIS(1)

FROM [ADVENTURE WORKS]

Any inputs will be appreciated.

David.

DISTINCT must follow SELECT:

Code Snippet

SELECT

DISTINCT (DESCENDANTS({ [Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after)) ON AXIS(1),

{[Measures].[Amount]}

ON AXIS(0),

FROM [ADVENTURE WORKS]

Adamus

|||

Seems to be a bug, since if you statically list the members, Distinct() seems to work:

Code Snippet

SELECT

{[Measures].[Amount]} ON AXIS(0),

Distinct({[Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales],

[Account].[Accounts].[Intercompany Sales],

[Account].[Accounts].[Trade Sales]}) ON AXIS(1)

FROM [ADVENTURE WORKS]

Using Generate() instead of Distinct() also seems to work:

Code Snippet

SELECT

{[Measures].[Amount]} ON AXIS(0),

Generate(DESCENDANTS({[Account].[Accounts].[Gross Sales],

[Account].[Accounts].[Trade Sales]}, 0, self_and_after),

{[Account].[Accounts].CurrentMember}) ON AXIS(1)

FROM [ADVENTURE WORKS]

|||

Thx, Deepak.

The question remains: is it just a bug or there is some hidden meaning to this behavior?

Curiously, if instead of DISTINCT you apply other functions that should remove duplicates,

the duplicates still remain, for example, UNION with an empty set:

UNION(DESCENDANTS({[Account].[Accounts].[Gross Sales], [Account].[Accounts].[Trade Sales]}, 0, self_and_after), {})

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 & eliminate duplicate data

Hi there.. Although i know my questions will sound quite stupid, but i've tried n tried n i can't get the desired output.. so here goes...

I'm using SQL Server 2000. I have one table call SMSReceived. in that table, there's a lot of columns. But i need to display only 2 column which is column Sender and column Message. Since this is our project, we had a lot of data with the same number in the column Sender. I want to display the data but I only want the Sender number only once. (no redundant data) So i try my query like this :

select distinct Sender from SmsReceived <-- this works since they are no redundant data

then i tried 2 columns which is :

select distinct Sender, Message from SmsReceived

then, but the Sender number still redundant.. It's like distinct is not functioning. I think it is because of the different message.

So, can anyone help me with this?.. I need to display 2 columns but only one Sender number. you can eliminate either one data as long as the Sender number didn't appear twice.

Erm... i tried group by, but an error occured like this..

Column 'Message' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Sender, Message
1234, hi how are you?
1234, r u ok?
7777, my name is
7777, hello

yeah, it is something like this.. But i need to eliminate either one...

So the result will become like this..

1234, hi, how are you?
7777,hello

Is it possible?.. Please help me... :( :(select Sender, min(Message) as lowest_message
from SmsReceived
group by Sender:)|||thanks..it already works...

select dnyFrom as 'SMS Sender', min(dnyReason) as 'Reason', min(dnySMSText) as 'SMS Text', min(dnycustomerid) as 'Customer Id'
from shldnysmsreceived
where dnycustomerid='PRM' and
dnycustomerid is not null and
dnyDateReceived between '2006-12-01 00:00:00.000' and '2007-07-01 00:00:00.000'
group by all dnyFrom
order by min(dnySMSText) asc

and one more thing... for example if i had data that looks like this

1234, successful
1234, invalid message
4444, winner of prize
4444, successful
4444, invalid format
6666, hello
6666, bulk sms

i need to display only the data with 'successful'. so the output will look like this

1234, successful
4444, successful
6666, hello <-- can choose either one (hello or bulk sms)|||the solution i gave you was for two columns, one for the GROUP BY and the other for the MIN() function

you do realize that by having multiple MIN() expressions, the resulting values are not necessarily from the same row, don't you?|||No, i don't know that... why we can't have multiple expression? Can u explain by giving examples? .. I really dont understand :(|||oh, you can have multiple MIN() expressions, that's not the problem

example:

tom 12 ggg 105
tom 11 hhh 108
tom 15 xxx 104
bob 16 bbb 112
bob 13 ccc 109
bob 17 aaa 137

assume GROUP BY on the first column, with MIN on the others, the resuts are:

tom 11 ggg 104
bob 13 aaa 109

see? good results from the query, but these do not represent actual rows from the table|||owh.. so how am i going to choose 'successful' only from other data? i don't know how to use if statement..|||you would probably use a CASE expression

but i really do not understand what you are trying to do

your example data uses two columns, but your query has four|||owh.. i was just trying to make it easy for the readers to understand.. the actual data have 4 or more columns that needed to be displayed. but for the moment, i only need to know how to display 2 columns with conditions that already stated above... and i don't know how to use CASE.. :(|||if i give you the solution for only two columns, it will be of very little use to you...

... so i won't, sorry

for an example of the CASE expression, see http://www.sqlzoo.net/fun_case|||Thanks everyone..I already solve it..using case...|||hi.. i have some query that i want to solve. i had a data that looks like this. (it's actually a text messages where people sms in to win prizes) and it have to start with 'PRM'

SMSReceived <-- column name
Prm 65719471
Prm 68516237
PRM 72847410
Prm 75031193
PRM (SPACE) PINSEND TO32080
prm (space)36398226
PRM < > 22733564
Prm < 82848916 > pin
PRM <36943554>

how can i choose only the number? so the desired output will look like this..
65719471
68516237
72847410
75031193
32080
36398226
22733564
82848916
36943554

please help..|||While most SQL engines can do this kind of text manipulation, the SQL standard doesn't provide any standard way that I know to acomplish this. In other words, you can use the vendor specific extensions to get this done, but I don't know of anything in the SQL language definition that will give you a generic solution that will work on any SQL implementation.

I'd post this question in the appropriate vendor/engine specific forum to get an answer (if I didn't already know how to solve it using the engine of choice).

-PatP|||I would recommend REGEXP in MySQL but i know you're not using MySQL. I'm sure there is a MSSQL equivalent though. More importantly I think you should be stripping this information down before it goes into the database...

ever thought that someone can SQL inject from a text message?|||Isn't this exactly why your answer should be classed as incorrect if you don't follow a set pattern.
E.g. "Text PRM then a space then your answer to 55555"
If your results were always consistent it'd be easy as pie to extract just the number partsql

Distinct

Hi, I've recently moved a site to MSSQL server from Access and I've hit a few problems as it implements SQL differently. I've had a lot of duplicate data on the site which I have partly remedied by using SELECT DISTINCT - though I don't know how to use this in conjunction with SELECT * to cure other parts of my site that return duplicate data. Does anyone know how to do this? Thanks!First let mi say that MSSQL Server supports most(if not all) of the sql u write in Ms Access; it however builds on this to provide proprietary utility aggregate functions using T-SQL...
So depending on ur calling application(front end)...use 'select distinct' in ur SQL cmd to get distinct record temporarily or u just go 2 d database to do dat - select distinct into a temporary table, delete d table n insert d distinct records into a new table dat bears d name of the old(deleted) table.|||ok thanks, i'll look into that.

Like above because of syntax differences, this no longer works and the page does not display:

"Select * from Events where Format(EventDate,'yyyymmdd') >= " & FormatDate(Now(),"YYYYMMDD") & " order by EventDate asc"

I'm pretty sure it has something to do with Format(), but I'm a little unsure. Any ideas?

Thanks!|||u cannot use an expression directly on the column name, so try:
"Select * from Events where EventDate >= " & FormatDate(Now(),"YYYYMMDD") & " order by EventDate asc"|||hmm still not working|||use convert function of sql serversql

displaying non-duplicate keys of all duplicate entries

Ok, so I'm checking for duplicate data in a table. Let's say it has 3 data fields and a key field (i.e. "ID", "FIRST", "MIDDLE", "LAST"). No keys are duplicated. If I find entries that have the same data in each of the non-key fields, I want to know the keys for all those entries. I have been able to find duplicate rows using this...

SELECT
TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
FROM
TABLE
GROUP BY
TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
HAVING
COUNT(*) > 1

Unfortunately I've found no way to incorporate the return of the TABLE."ID" for every duplicated entry. Is there some way I can join the result with the db.table to find this, or some other way to make this happen?

Thanks,
DeanSure, use:SELECT
A."ID", A."FIRST", A."MIDDLE", A."LAST"
FROM TABLE AS A
WHERE 1 < (SELECT Count(*)
FROM TABLE AS B
WHERE B."FIRST" = A."FIRST"
AND B."LAST" = A."LAST"
AND B."MIDDLE" = A."MIDDLE")-PatP|||Thanks, that worked well, although it takes a good while for the server to process the query.|||Indicies would help this query a lot, particularly an index on last, first, middle.

-PatP

displaying just the differences between 2 tables datasets

Hi, I have 2 identically defined tables that should have duplicate rows
(majority). I want a way of displaying just the data that doesn't exit in
either table, one table at a time for reporting purposes.
I have coded this already using a 3rd table that holds all data that matches
2 tables and then deleting from both tables the data that matches the third
table and then doing a select from the result in each table.
I want to know how to do this more efficiently as this way seems clumsy and
slow. Can anyone help?For non-nullable columns:
SELECT A.*
FROM A
LEFT JOIN B
ON A.col1 = B.col1
AND A.col2 = B.col2
AND ... etc
WHERE B.col1 IS NULL
If you need to cope with NULLs by treating them as equal values in the
comparison:
SELECT col1, col2, ...
FROM
(SELECT 1 AS x, col1, col2, ...
FROM A
UNION ALL
SELECT 2 AS x, col1, col2, ...
FROM B) AS T
GROUP BY col1, col2, ...
HAVING MAX(x)=1
David Portas
SQL Server MVP
--|||select * from table1 where ID not in (select ID from table2)
union
& vice versa
Does this help?
Daniel
"sysbox27" <sysbox27@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E691E4AE-E0B6-4120-A072-B42001AE47EB@.microsoft.com...
> Hi, I have 2 identically defined tables that should have duplicate rows
> (majority). I want a way of displaying just the data that doesn't exit in
> either table, one table at a time for reporting purposes.
> I have coded this already using a 3rd table that holds all data that
> matches
> 2 tables and then deleting from both tables the data that matches the
> third
> table and then doing a select from the result in each table.
> I want to know how to do this more efficiently as this way seems clumsy
> and
> slow. Can anyone help?
>|||Allow me to illustrate:
Let's compare these two tables:
create table dbo.Names1
(
NameID int identity (1, 1)
,[Name] nvarchar(64) primary key
)
go
create table dbo.Names2
(
NameID int identity (1, 1)
,[Name] nvarchar(64) primary key
)
go
insert dbo.Names1
(
[Name]
)
select N'Jack' as [Name]
union
select N'Phil'
union
select N'Rod'
union
select N'Bing'
go
insert dbo.Names2
(
[Name]
)
select N'Jack' as [Name]
union
select N'Tommy'
union
select N'Midge'
union
select N'Bing'
go
Like this:
select Combination.[Description] as [Description]
,Combination.[Name] as [Name]
from (
select 'Exists in Names1' as [Description]
,dbo.Names1.[Name] as [Name]
from dbo.Names1
full join dbo.Names2
on dbo.Names2.[Name] = dbo.Names1.[Name]
where (dbo.Names1.NameID is null or dbo.Names2.NameID is null)
union
select 'Exists in Names2'
,dbo.Names2.[Name]
from dbo.Names1
full join dbo.Names2
on dbo.Names2.[Name] = dbo.Names1.[Name]
where (dbo.Names1.NameID is null or dbo.Names2.NameID is null)
) Combination
where (Combination.[Name] is not null)
go
Is this what you're looking for?
ML|||thank you to everyone for taking the time to assist me.
much appreciated.

displaying found duplicate - both records

I would like to look at both records returned as duplicates. I am using the
following script to find the duplicates but can it only returns one record
not both. I would like to see the entire record so that I can make a
determination on more than just the criteria that I selected as the basis of
the record being a duplicate.
SELECT Patient.Last_Name,
Patient.First_Name,
Patient.Birthdate
COUNT(*) AS Dupes
FROM Patient
WHERE Patient_Key IN (SELECT DISTINCT Patient_Key FROM Patient_Elg WHERE
Payor_Key = 36)
GROUP BY Last_Name, First_Name, Birthdate
HAVING (COUNT(*) > 1)
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200510/1In order to do that, you may need to put your current query as "Result
Table", something like:
SELECT P1.Last_Name,
P1.First_Name,
P1.Birthdate,
Dupes
FROM Patient P1,
(SELECT P2.Last_Name P2LastName,
P2.First_Name P2FirstName,
P2.Birthdate P2Birthday
COUNT(*) AS Dupes
FROM Patient P2
WHERE P2.Patient_Key IN (SELECT DISTINCT Patient_Key FROM
Patient_Elg WHERE
P2.Payor_Key = 36)
GROUP BY P2.Last_Name, P2.First_Name, P2.Birthdate
HAVING (COUNT(*) > 1)) DupCount
where P1.Last_Name = P2LastName
and P1.First_Name = P2FirstName
and P1.Birthdate = P2Birthday
This code has not been tested yet but just an idear.
Perayu
"Jay via webservertalk.com" <u7124@.uwe> wrote in message
news:5685c45d8a0c6@.uwe...
>I would like to look at both records returned as duplicates. I am using the
> following script to find the duplicates but can it only returns one record
> not both. I would like to see the entire record so that I can make a
> determination on more than just the criteria that I selected as the basis
> of
> the record being a duplicate.
> SELECT Patient.Last_Name,
> Patient.First_Name,
> Patient.Birthdate
> COUNT(*) AS Dupes
> FROM Patient
> WHERE Patient_Key IN (SELECT DISTINCT Patient_Key FROM Patient_Elg WHERE
> Payor_Key = 36)
> GROUP BY Last_Name, First_Name, Birthdate
> HAVING (COUNT(*) > 1)
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200510/1|||This did work for me after I made some tweaks. Thanks allot.
Perayu wrote:
>In order to do that, you may need to put your current query as "Result
>Table", something like:
>SELECT P1.Last_Name,
> P1.First_Name,
> P1.Birthdate,
> Dupes
>FROM Patient P1,
> (SELECT P2.Last_Name P2LastName,
> P2.First_Name P2FirstName,
> P2.Birthdate P2Birthday
> COUNT(*) AS Dupes
> FROM Patient P2
> WHERE P2.Patient_Key IN (SELECT DISTINCT Patient_Key FRO
M
>Patient_Elg WHERE
> P2.Payor_Key = 36)
> GROUP BY P2.Last_Name, P2.First_Name, P2.Birthdate
> HAVING (COUNT(*) > 1)) DupCount
>where P1.Last_Name = P2LastName
> and P1.First_Name = P2FirstName
> and P1.Birthdate = P2Birthday
>This code has not been tested yet but just an idear.
>Perayu
>
>[quoted text clipped - 13 lines]
Message posted via http://www.webservertalk.com

Displaying duplicate row in red color

Hi,
In my report, I have used a table. In the table, I want to display all the
duplicate rows in red color, i.e if Field1 is listed twice in the table
details, then I want to display the duplicate row in red color.
ANy ideas on how to achieve this?
Thanks
--
pmudSet the Color property of the textbox of Field1 to use an expression,
like this:
=IIF( Fields!Field1.Value = Previous( Fields!Field1.Value), "Red",
"Black")
Matt A|||Thanks MattA. This is extremely helpful. :)
--
pmud
"MattA" wrote:
> Set the Color property of the textbox of Field1 to use an expression,
> like this:
> =IIF( Fields!Field1.Value = Previous( Fields!Field1.Value), "Red",
> "Black")
> Matt A
>|||I was wondering if you can refer to the textbox.value from within a property
like:
=iif(textbox98.value > 0, "green", "red")
--
William Stacey [MVP]
"MattA" <mattarabas@.gmail.com> wrote in message
news:1138312093.490398.115610@.g14g2000cwa.googlegroups.com...
| Set the Color property of the textbox of Field1 to use an expression,
| like this:
|
| =IIF( Fields!Field1.Value = Previous( Fields!Field1.Value), "Red",
| "Black")
|
| Matt A
||||=IIF(ReportItems!textbox98.Value > 0, "GREEN", "RED")
Note that scope rules apply to ReportItems collection, so you'll have
scope limitations on being able to use it (not on aggregates, for
example...)
Matt A|||Thanks Matt. Are these processing "rules" layed out in BOL or somewhere.
Cheers!
--
William Stacey [MVP]
"MattA" <mattarabas@.gmail.com> wrote in message
news:1138370026.412815.53510@.g44g2000cwa.googlegroups.com...
| =IIF(ReportItems!textbox98.Value > 0, "GREEN", "RED")
|
| Note that scope rules apply to ReportItems collection, so you'll have
| scope limitations on being able to use it (not on aggregates, for
| example...)
|
| Matt A
|

display on duplicate records

I have the following query:
SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
FROM dbo.cbt_BillingAddress
WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
ORDER BY ProductCode, BillingCustID, BillingPeriod
which returns the following columns:
BillingPeriod CustomerID ProductCode BillingCustID
-- -- -- --
200502 205022338 APX 79110
200503 205022338 APX 79110
200504 205022338 BRW 79110
200505 205022338 BRW 79110
200506 205027355 APX 79110
200506 205022338 APX 79110
200507 205027355 BRW 79110
200507 205022338 BRW 79110
As you can see from the result, there are duplicates under the billingperiod
column for the same billingcustID. Essentially, there should only ever be on
e
CustomerID associated with the same ProductCode and BillingCustID and
BillingPeriod.
How can I rewrite my query to only display only the duplicates?
Thanks for your help in advance.Try this...
SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
FROM dbo.cbt_BillingAddress A
WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
AND EXISTS (
SELECT 1 FROM dbo.cbt_BillingAddress B
WHERE A.BillingCustID = B.BillingCustID
AND A.ProductCode = B.ProductCode
AND A.BillingPeriod = B.BillingPeriod
AND A.CustomerID <> B.CustomerID)
ORDER BY ProductCode, BillingCustID, BillingPeriod
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:D1DDD109-45A5-479C-B784-EE86856B14B0@.microsoft.com...
> I have the following query:
> SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
> FROM dbo.cbt_BillingAddress
> WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
> ORDER BY ProductCode, BillingCustID, BillingPeriod
> which returns the following columns:
> BillingPeriod CustomerID ProductCode BillingCustID
> -- -- -- --
> 200502 205022338 APX 79110
> 200503 205022338 APX 79110
> 200504 205022338 BRW 79110
> 200505 205022338 BRW 79110
> 200506 205027355 APX 79110
> 200506 205022338 APX 79110
> 200507 205027355 BRW 79110
> 200507 205022338 BRW 79110
> As you can see from the result, there are duplicates under the
billingperiod
> column for the same billingcustID. Essentially, there should only ever be
one
> CustomerID associated with the same ProductCode and BillingCustID and
> BillingPeriod.
> How can I rewrite my query to only display only the duplicates?
> Thanks for your help in advance.|||Awesome. Thanks for your help. One last question: what is the significance o
f
the statement:
SELECT 1...
What does this do and where can I get more info on its usage. Thanks again.
"Jim Underwood" wrote:

> Try this...
> SELECT BillingPeriod, CustomerID, ProductCode, BillingCustID
> FROM dbo.cbt_BillingAddress A
> WHERE (BillingCustID IS NOT NULL) AND (BillingCustID = '79110')
> AND EXISTS (
> SELECT 1 FROM dbo.cbt_BillingAddress B
> WHERE A.BillingCustID = B.BillingCustID
> AND A.ProductCode = B.ProductCode
> AND A.BillingPeriod = B.BillingPeriod
> AND A.CustomerID <> B.CustomerID)
> ORDER BY ProductCode, BillingCustID, BillingPeriod
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:D1DDD109-45A5-479C-B784-EE86856B14B0@.microsoft.com...
> billingperiod
> one
>
>|||Honestly, I am not sure if this serves a purpose in SQL Server, but I use it
out of habit from my Oracle 7/8 experience.
where exists (Select 1 from table1 where column1 = 'MyData')
is functionally the same as
where exists (Select column1 from table1 where column1 = 'MyData')
It simply verifies that a row exists in either case. I use the literal 1
for performance reasons.
In Oracle 7/8 (and likely 9 and 10) selecting a literal uses less memory
than selecting a value from a table. You could select a character ('x' for
example) but I was always told using a number was more efficient than a
character. Essentially, you don't need to retrieve a data value from disk
or memory, save what you use in your where clause. The SQL engine can
evaluate the where clause without returning any data in the process.
SQL server may very well ignore the select columns in an exist clause, I
really don't know. Maybe someone with more experience can validate or
correct me on this point.
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:0155F28A-3A76-4159-8157-EFC6E2A45C0E@.microsoft.com...
> Awesome. Thanks for your help. One last question: what is the significance
of
> the statement:
> SELECT 1...
> What does this do and where can I get more info on its usage. Thanks
again.
>
> "Jim Underwood" wrote:
>
be|||"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uCH2N0pJGHA.984@.tk2msftngp13.phx.gbl...
> Honestly, I am not sure if this serves a purpose in SQL Server, but I use
> it
> out of habit from my Oracle 7/8 experience.
> where exists (Select 1 from table1 where column1 = 'MyData')
> is functionally the same as
> where exists (Select column1 from table1 where column1 = 'MyData')
> It simply verifies that a row exists in either case. I use the literal 1
> for performance reasons.
> In Oracle 7/8 (and likely 9 and 10) selecting a literal uses less memory
> than selecting a value from a table. You could select a character ('x'
> for
> example) but I was always told using a number was more efficient than a
> character. Essentially, you don't need to retrieve a data value from disk
> or memory, save what you use in your where clause. The SQL engine can
> evaluate the where clause without returning any data in the process.
> SQL server may very well ignore the select columns in an exist clause, I
> really don't know. Maybe someone with more experience can validate or
> correct me on this point.
I'm sure that I don't have more experience but from what I've seen in this
newsgroup,
select *
is the norm in Exists clauses.