Find duplicate references 1

[Cpreston]

Hi

I am trying to find duplicate references in a table but keep getting NULL values returned. I am using the code below.
The customerref keeps coming in as a null. In the field there may be various characters numbers and letters.

Couls someone advise how I can adapt the code so it shows the customeref entrys. Thanks

[link sql]
select ordernumber,customerref, count(1) as CNT
from orderheader
group by customerref,OrderNumber
HAVING (count(1) > 1) Link[/url]

 

[gmmastros]

If you are returning rows that contain a null value, then there must be nulls in the database.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

What's the result? If you only have rows with NULL in customerref, you have no double customerref value. If you try to establish a unique index, you still can't have one when NULLs appear multiple times.
That hints on a normalizsation problem.

Bye, Olaf.

 

[Cpreston]

What I am trying to get to is to list all that have duplicate customerref numbers.

If I run the query without the Having clause, then it returns everything including Null values, because it is looking at every entry of course that appears once.

If I run the clause with the Having, then it only displays customerref with NULL results in.

 

[Olaf Doschke]

Then you have no double (or triple etc) Ordernumber, customerref combination.

If you look for double customerref independent of Ordernumber, then simply remove that from the field list and grouping.

I just bet once you do so you get almost all customerref values.

Bye, Olaf.

 

[Cpreston]

Yes you are correct, so therefore how do I get to see the ordernumber also please.

 

[Olaf Doschke]

Afterward, not within the same query. Or differently formulated, using the customerref values from the simpler group by as your starting point named "Link", you finally inner join orderheaders to "Link":

With Link as(
select customerref
from orderheader
group by customerref
HAVING count(*) > 1) 

Select orderheader.* from Link inner join orderheader on orderheader.customerref = link.customerref ORDER BY orderheader.customerref, orderheader.ordernumber

Bye, Olaf.

 

[Cpreston]

That is perfect thank you for your help and advice

 

[Olaf Doschke]

Just a few more hints:

[tt]Select * FROM Links[/tt] is your list of recurring customers (apart from NULL), you don't have to see with your own eyes in which orders they appear, T-SQL can count. This has to come after the [tt]With Link as...[/tt] part.
[tt]Select * FROM orderheaders WHERE customerref IS NULL[/tt] will list all orders missing a referenced customer. You will have such orders, as you said NULL appears in the results. This query runs standalone.

Bye, Olaf.