Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Duplicate data being shown

Author  Topic 

tkotey
Yak Posting Veteran

75 Posts
Posted - 2009-03-06 : 07:08:02
Hi

I have this query below that is meant to produce unique data but some of the data a replicate. Please take a look at it and let me know where the mistake is
SELECT DISTINCT TOP ($limit) * From Revenue Where 1=1 AND BillIndex NOT IN ( SELECT TOP $offset BillIndex From Revenue ORDER BY BillIndex ASC) AND BillCreateDate >='$term' AND BillCreateDate <='$term1' AND UserIndex <> 135 AND UserIndex <> 53 AND UserIndex <> 51 ORDER BY BillIndex, UserID

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2009-03-06 : 07:23:37
As you use *, there is change some column values may get duplicated
Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts
Posted - 2009-03-06 : 07:28:50
Expected results should be: 

PPCB  	Jan 15 2009 12:00AM to Jan 15 2009 12:00AM  	1 MEG Uncapped   	2849.88   	21889.05  	12476.93
ArunaKulasinghe 	Jan 15 2009 12:00AM to Jan 15 2009 12:00AM 	LiveBox256K-12  	-220.00  	478.99 	258.99
DHPConsultingEnginee 	Jan 15 2009 12:00AM to Jan 15 2009 12:00AM 	LiveBox256K-12  	363.63  	449.89 	49.89


But it is showing:


PPCB  	Jan 15 2009 12:00AM to Jan 15 2009 12:00AM  	1 MEG Uncapped   	2849.88   	21889.05  	12476.93
PPCB 	Jan 15 2009 12:00AM to Jan 15 2009 12:00AM 	1 MEG Uncapped  	2849.88  	21889.05 	12476.93
ArunaKulasinghe 	Jan 15 2009 12:00AM to Jan 15 2009 12:00AM 	LiveBox256K-12  	-220.00  	478.99 	258.99
DHPConsultingEnginee 	Jan 15 2009 12:00AM to Jan 15 2009 12:00AM 	LiveBox256K-12  	363.63  	449.89 	49.89
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-06 : 07:45:50
Not sure, But may be you have a special character there. Can you put names of all the rows in your select. Also replace the column containing "Jan 15 2009 12:00AM to Jan 15 2009 12:00AM " column with replace(rtrim(ltrim(<columnname>)),char(9),'')
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts
Posted - 2009-03-06 : 08:03:49
quote:
Originally posted by sakets_2000

Not sure, But may be you have a special character there. Can you put names of all the rows in your select. Also replace the column containing "Jan 15 2009 12:00AM to Jan 15 2009 12:00AM " column with replace(rtrim(ltrim(<columnname>)),char(9),'')


It did not work
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-06 : 08:06:47
post the query that you ran.
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts
Posted - 2009-03-06 : 08:10:53
quote:
Originally posted by sakets_2000

post the query that you ran.


Here's the query:

SELECT TOP ($limit) UserID, BillStartDate, BillEndDate, Package, Sums, Amount_Due, Previous_Amount From Revenue Where 1=1 AND BillIndex NOT IN ( SELECT TOP $offset BillIndex From Revenue ORDER BY BillIndex ASC) AND BillCreateDate >='$term' AND BillCreateDate <='$term1' AND UserIndex <> 135 AND UserIndex <> 53 AND UserIndex <> 51 ORDER BY BillIndex, UserID
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-06 : 08:17:28
quote:
Originally posted by tkotey

quote:
Originally posted by sakets_2000

post the query that you ran.


Here's the query:

SELECT TOP ($limit) UserID, BillStartDate, BillEndDate, Package, Sums, Amount_Due, Previous_Amount From Revenue Where 1=1 AND BillIndex NOT IN ( SELECT TOP $offset BillIndex From Revenue ORDER BY BillIndex ASC) AND BillCreateDate >='$term' AND BillCreateDate <='$term1' AND UserIndex <> 135 AND UserIndex <> 53 AND UserIndex <> 51 ORDER BY BillIndex, UserID



Why did you remove "distinct"?
Go to Top of Page

bklr
Master Smack Fu Yak Hacker

1693 Posts
Posted - 2009-03-06 : 08:22:41
u first posted query has distinct keyword
now ur running query without distinct so that duplicates r getting in ur output
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts
Posted - 2009-03-06 : 08:23:44
quote:
Originally posted by sakets_2000

quote:
Originally posted by tkotey

quote:
Originally posted by sakets_2000

post the query that you ran.


Here's the query:

SELECT TOP ($limit) UserID, BillStartDate, BillEndDate, Package, Sums, Amount_Due, Previous_Amount From Revenue Where 1=1 AND BillIndex NOT IN ( SELECT TOP $offset BillIndex From Revenue ORDER BY BillIndex ASC) AND BillCreateDate >='$term' AND BillCreateDate <='$term1' AND UserIndex <> 135 AND UserIndex <> 53 AND UserIndex <> 51 ORDER BY BillIndex, UserID



Why did you remove "distinct"?


Because it did not make a difference. I thought it was going to prevent the data duplication
Go to Top of Page

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts
Posted - 2009-03-06 : 08:28:35
Whats the data type of BillStartDate and BillEndDate
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts
Posted - 2009-03-06 : 08:30:57
quote:
Originally posted by sakets_2000

Whats the data type of BillStartDate and BillEndDate


datetime
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts
Posted - 2009-03-06 : 08:33:43
When I add the DISTINCT I get the following error msg
Warning: mssql_query() [function.mssql-query]: message: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. (severity 15)
Go to Top of Page

tkotey
Yak Posting Veteran

75 Posts
Posted - 2009-03-06 : 09:04:10
I solved the problem. I got help from <a href="http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx">here</a>
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site