Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 27th, 2003, 02:00 AM
Authorized User
 
Join Date: Jun 2003
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Default How to simulate DISTINCT clause in SQL SERVER

Hi friends!!

I want to simulate the DISTINCT clause in SQL SERVER 2000 using a single SQL query or nested SQL query. Basically, I have a table containing Campaigns for Prospects(ssProspectCampaign). The "ssProspectCampaign" table contains record like :

Campaign CampaignName ProspectName
ID
----------------------------------------
1 Aviva Frenkel
2 Bahamas Promo AON
2 Bahamas Promo Marsh
3 ASPSalesource AON
4 ICICI Ebix
5 E-Global HSBC
5 E-Global Marsh
6 Auto Ins AON
7 Bajaj Cap Frenkel
8 Worker Comp HDFC
9 Commercial Ins AON
9 Commercial Ins Marsh
10 New York Max Marsh
        LifeIns
Now, I want all the campaigns and their names but I want them
to appear only once and I want to do it without the "Distinct" clause....Can somebody help...I'm trying hard on it.

thanks



cheers,
debsoft
__________________
cheers,
debsoft
 
Old June 27th, 2003, 02:04 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Why don't you want to use distinct? What you are asking is a little like saying I want to run in a marathon but I don't want to use my legs. There are ways of doing this, but distinct would be the best way to do it.

regards
David Cameron
 
Old June 27th, 2003, 02:17 AM
Authorized User
 
Join Date: Jun 2003
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Cameron!

Thanks for ur very fast response...I just want to simulate the Distinct clause in SQL query...I mean just wanted to find some alternative to it...Actually I was aksed this question by someone before but at that time I didn't hve any answer to it...nor do I hve a valid answer now...just trying hard to think a solution to this...Do you have any method by which one can simulate Distinct???

Thanks

cheers,
debsoft
 
Old June 27th, 2003, 07:37 AM
Authorized User
 
Join Date: Jun 2003
Posts: 77
Thanks: 0
Thanked 0 Times in 0 Posts
Default

i think you can use group by:


select PC.[Campain ID], PC.CampainName
from ssProspectCampain PC
group by PC.[Campain ID], PC.CampainName


defiant.
 
Old June 27th, 2003, 09:06 AM
Authorized User
 
Join Date: Jun 2003
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Would this work?

SELECT CampaignName, COUNT(CampaignName)
FROM <TABLE>
GROUP BY CampaignName

Then get rid of the second column
 
Old June 30th, 2003, 12:04 AM
Authorized User
 
Join Date: Jun 2003
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Ioates!

Thanks for ur reply...I think it wud work fine.

cheers
Debsoft


Quote:
quote:Originally posted by ioates
 Would this work?

SELECT CampaignName, COUNT(CampaignName)
FROM <TABLE>
GROUP BY CampaignName

Then get rid of the second column
 
Old June 30th, 2003, 06:29 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 215
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This does still leave us with the original question. Why don't you want to use distinct?

regards
David Cameron





Similar Threads
Thread Thread Starter Forum Replies Last Post
USING SQL IF Statement in WHERE clause rit01 SQL Server 2000 17 March 15th, 2007 08:33 AM
vbscript version of SQL 'SELECT DISTINCT' SoC Classic ASP Basics 1 February 8th, 2006 03:16 AM
SQL Where Clause sudarshan73 Crystal Reports 2 January 11th, 2006 10:01 AM
distinct clause in report mateenmohd Access 2 February 21st, 2005 06:01 AM
sql to return distinct count pbgurl Access VBA 2 October 29th, 2004 06:29 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.