How to get Distinct X, Y - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

How to get Distinct X, Y

Author

Topic

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-11-22 : 07:59:33

I have two columns x and y in my table having data type int. I want to get the list of distinct x and y with out casting into varchar because it kills the query when there are 1 million rows.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-11-22 : 08:01:07

SELECT DISTINCT X, Y FROM myTable

Don't know why you thought you needed to cast to varchar.

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-11-22 : 08:05:31

Sorry I forgot mentioning that I actually need count of distinct x, y. And with casting proper result is not returned. You can check with this code

declare @table table (x int, y int)
insert into @table
select 1, 2 union all
select 1, 12 union all
select 11, 2 union all
select 11, 2
select distinct x, y from @table
select count(distinct cast(x as varchar)+cast(y as varchar)) from @table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-22 : 08:05:49

>>I want to get the list of distinct x and y with out casting into varchar because it kills the query when there are 1 million rows.

Did you use Casting in your query?
Post your query if any

Madhivanan

Failing to plan is Planning to fail

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-22 : 08:07:36

[code]
SELECT COUNT(*)
FROM (
SELECT DISTINCT x, y
FROM @table
) AS A
[/code]

bridge
Yak Posting Veteran

93 Posts

Posted - 2005-11-22 : 08:11:29

Yes I too have this solution in my mind but there are more than 20 procedures where this query is being used. Unfortunately the requirements have been changed and now the results are required against distinct x and y. Please tell me where I can change in the similary line instead of using a derived table. Because I just want to change the single line. The queries in the procedures are already using joins, sub-queries and derived tables.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2005-11-22 : 10:05:57

select X, Y, count(*)
from table
group by X, Y

Should give you a single row for each and every variation of X, Y with the count of how many of that combination has been found.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-22 : 11:08:38

quote:
Originally posted by bridge

Yes I too have this solution in my mind but there are more than 20 procedures where this query is being used. Unfortunately the requirements have been changed and now the results are required against distinct x and y. Please tell me where I can change in the similary line instead of using a derived table. Because I just want to change the single line. The queries in the procedures are already using joins, sub-queries and derived tables.

If you want to fix it, I suggest doing it right; the proper solution has been given. Especially if you have large amounts of data.

Another solution is to create a view that returns distinct X,Y and reference that.

create view DistinctXY
as select distinct x,y from YourTable

then you just alter your 20 procedures to query the view instead of the table.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-22 : 11:12:36

By the way --- I bet the reason why those 20 procedures are so complicated and long and tough to change is because no one took the time to correctly re-write them as conditions changed. And that is the very reason why they will get even more convoluted and inefficient since no one wants to take the time to rewrite them yet again. Very ironic.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources