Simple Query - 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

SQL Server Development (2000)

Simple Query

Author

Topic

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-08-05 : 14:55:57

When you don't use it, you forget it....

If this will return distinct records:

SELECT DISTINCT MainContactFirst FROM DIRECTORY

then how do I add other fields in with the query? I need all their first names to be absolutely distinct, but I need to pull other information along with it.

-Brian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-05 : 14:58:16

If you have two Tom names, how would you know which one to pull the data for. Let's say you have:

Tom, San Diego, CA
Tom, Las Vegas, NV

Which city and state would you pull information for?

Or show us what your data looks like so that we can help? Are they duplicates?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-05 : 14:58:58

EDIT: Damn sniped again

Distinct is for an entire row...so if you have different values, you'll get them...

You can GROUP BY and use scalr function like MIN and MAX for each name...then you'll get 1 row per name..

Where's the table DDL, and what are the results you are trying to get

Sample data is a plus as well...

Brett

8-)

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-08-05 : 15:01:41

Interesting question tduggan. I guess this is why I feel so lost. This table is an import from Excel and the only thing that makes each individual record (32,000+) unique is the combination of all 15 fields. However, I need all 15 fields where there is only 1 Tom and 1 Sally, etc.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-05 : 15:03:25

Then use a GROUP BY. You would group by all 15 columns.

Tara

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-08-05 : 15:09:20

[code]
SELECT DISTINCT MainContactFirst, MainContactLast, BizName, MailingAddress
FROM DIRECTORY
WHERE
MainContactFirst IS NOT NULL
AND
MainContactLast IS NOT NULL
AND
BizName IS NOT NULL
AND
MailingAddress IS NOT NULL
ORDER BY MainContactFirst
[/code]
Produces
[code]
MainContactFirst, MainContactLast, BizName, MailingAddress

Abra Poindexter Poindexter, Abra 2833 S 87th Ave
Abraham Fuenta LA Carretta 3050 L St
Abraham Mathews Mathews, Abraham P MD 6901 N 72nd St #2244
Abraham Mathews Mathews, Abraham P MD 801 Harmony St #202
Absolom Franklin Franklin Collision Repair 2600 N 20th St
Ada Dailey Super Children in Action 2809 Hamilton St
[/code]

Notice the two Abraham Matthews doctor types who differ only by their address. I need only one of the Abrahams.

-Brian

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-05 : 15:12:56

SELECT MainContactFirst, MainContactLast, BizName, MAX(MailingAddress)
FROM Directory
GROUP BY MainContactFirst, MainContactLast, BizName

Tara

kelleyb
Yak Posting Veteran

61 Posts

Posted - 2004-08-05 : 15:18:18

Tara, I still got the same kind of results.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-05 : 15:20:09

Your data must be different than what you have posted then.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-05 : 15:24:00

[code]
SELECT MainContactFirst, MAX(MainContactLast), MAX(BizName), MAX(MailingAddress)--, MAX(ect)
FROM Directory
GROUP BY MainContactFirst
[/code]

That should cook you up some pretty undetermenistic results

or is that nondetermenistic...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-05 : 15:26:16

And how can you be sure, by way of last name, that it's not a different person?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-05 : 15:36:35

quote:
Originally posted by kelleyb

Tara, I still got the same kind of results.

You're not a betting man are you?


USE Northwind
GO

CREATE TABLE myTable99(
	  MainContactFirst varchar(100)
	, MainContactLast varchar(100)
	, BizName varchar(100)
	, MailingAddress varchar(100)) 
GO

INSERT INTO myTable99(MainContactFirst, MainContactLast, BizName, MailingAddress)
SELECT 'Abra'      ,'Poindexter' ,'Poindexter, Abra'          ,'2833 S 87th Ave' 	UNION ALL
SELECT 'Abraham'   ,'Fuenta'     ,'LA Carretta'               ,'3050 L St' 		UNION ALL
SELECT 'Abraham'   ,'Mathews'    ,'Mathews, Abraham P MD'     ,'6901 N 72nd St #2244'   UNION ALL
SELECT 'Abraham'   ,'Mathews'    ,'Mathews, Abraham P MD'     ,'801 Harmony St #202'    UNION ALL
SELECT 'Absolom'   ,'Franklin'   ,'Franklin Collision Repair' ,'2600 N 20th St' 	UNION ALL
SELECT 'Ada'       ,'Dailey'     ,'Super Children in Action'  ,'2809 Hamilton St'
GO

  SELECT MainContactFirst, MainContactLast, BizName, MAX(MailingAddress)
    FROM myTable99
GROUP BY MainContactFirst, MainContactLast, BizName

  SELECT MainContactFirst, MAX(MainContactLast), MAX(BizName), MAX(MailingAddress)
    FROM myTable99
GROUP BY MainContactFirst

  SELECT DISTINCT MainContactFirst, MainContactLast, BizName, MailingAddress
    FROM myTable99
GO

DROP TABLE myTable99
GO

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-05 : 16:18:15

Brian -- this is why you need a primary key on your data. does your table have one? Actually, a simple identity PK would work very well here:


select 
  YOurTable.*
from
  yourtable
Inner join 
 (select FirstName, LastName, Min(ID) as MinID
  from Yourtable 
  group by firstname, lastname) a
on
  a.ID = YourTable.ID

that returns the first "ID" per combo of firstname/lastname from your table.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-05 : 16:33:27

Fixing the Dr's small type-o, and we get...


USE Northwind
GO

CREATE TABLE myTable99(
	  MainContactFirst varchar(100)
	, MainContactLast varchar(100)
	, BizName varchar(100)
	, MailingAddress varchar(100)
	, [ID] int IDENTITY(1,1)) 
GO

CREATE INDEX myTable99_IX1 ON myTable99(MainContactFirst, MainContactLast,[ID])
GO

INSERT INTO myTable99(MainContactFirst, MainContactLast, BizName, MailingAddress)
SELECT 'Abra'      ,'Poindexter' ,'Poindexter, Abra'          ,'2833 S 87th Ave' 	UNION ALL
SELECT 'Abraham'   ,'Fuenta'     ,'LA Carretta'               ,'3050 L St' 		UNION ALL
SELECT 'Abraham'   ,'Mathews'    ,'Mathews, Abraham P MD'     ,'6901 N 72nd St #2244'   UNION ALL
SELECT 'Abraham'   ,'Mathews'    ,'Mathews, Abraham P MD'     ,'801 Harmony St #202'    UNION ALL
SELECT 'Absolom'   ,'Franklin'   ,'Franklin Collision Repair' ,'2600 N 20th St' 	UNION ALL
SELECT 'Ada'       ,'Dailey'     ,'Super Children in Action'  ,'2809 Hamilton St'
GO

SELECT * FROM myTable99 o 
  WHERE EXISTS (SELECT  MainContactFirst, MainContactLast 
		  FROM  myTable99 i
	      GROUP BY MainContactFirst, MainContactLast
		HAVING MAX(i.[ID]) = o.[ID])
select 
  mytable99.*
from
  myTable99
Inner join 
 (select MainContactFirst, MainContactLast, Min(ID) as MinID
  from myTable99
  group by MainContactFirst, MainContactLast) a
on
  a.MinID = myTable99.ID
GO

DROP TABLE myTable99
GO

PS: Jeff check this out..I get different results from the 2 queries

and check out the execution plans...mine is using a nested loop, while youre uses a hash/match...

Is yours more effecient? Not sure...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-05 : 16:41:56

Add another index for [ID] and things change (the plan).

The plans become almost similar...your hash merge goes away, and becomes a nested loop...I end up incurring a sort though...

The data for that 1 guy is still different...wonder why...

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-08-06 : 08:59:10

i get the same results for the same two queries ....

i prefer JOINS as opposed to WHERE EXISTS() and stuff like that.

Which guy returns different data? maybe i am missing something...

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-06 : 09:29:11

Exists gets me


Abra	Poindexter	Poindexter, Abra		2833 S 87th Ave		1
Abraham	Fuenta	        LA Carretta			3050 L St		2
Abraham	Mathews		Mathews, Abraham P MD		801 Harmony St #202	4
Absolom	Franklin	Franklin Collision Repair	2600 N 20th St		5
Ada	Dailey		Super Children in Action	2809 Hamilton St	6

And Join gets me


Abra	Poindexter	Poindexter, Abra		2833 S 87th Ave		1
Abraham	Fuenta		LA Carretta			3050 L St		2
Abraham	Mathews		Mathews, Abraham P MD		6901 N 72nd St #2244	3
Absolom	Franklin	Franklin Collision Repair	2600 N 20th St		5
Ada	Dailey		Super Children in Action	2809 Hamilton St	6

My show plan show that the exists requires a sort where the join doesn't.

Otherwise the plans are the same

Brett

8-)

EDIT: Damn, that didn't parse too well..

PS What does your plan show?

EDIT2: Doesn't make a damn bit of sense...something must be wrong with the code...but you say you get the same results...anyone else try this?

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-06 : 13:36:28

Brett, i ran your code and got the same results you get.

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-06 : 13:42:51

Bret I get similar result for both queries IF: I change MAX to MIN

USE Northwind
GO

CREATE TABLE myTable99(
MainContactFirst varchar(100)
, MainContactLast varchar(100)
, BizName varchar(100)
, MailingAddress varchar(100)
, [ID] int IDENTITY(1,1))
GO

CREATE INDEX myTable99_IX1 ON myTable99(MainContactFirst, MainContactLast,[ID])
GO

INSERT INTO myTable99(MainContactFirst, MainContactLast, BizName, MailingAddress)
SELECT 'Abra' ,'Poindexter' ,'Poindexter, Abra' ,'2833 S 87th Ave' UNION ALL
SELECT 'Abraham' ,'Fuenta' ,'LA Carretta' ,'3050 L St' UNION ALL
SELECT 'Abraham' ,'Mathews' ,'Mathews, Abraham P MD' ,'6901 N 72nd St #2244' UNION ALL
SELECT 'Abraham' ,'Mathews' ,'Mathews, Abraham P MD' ,'801 Harmony St #202' UNION ALL
SELECT 'Absolom' ,'Franklin' ,'Franklin Collision Repair' ,'2600 N 20th St' UNION ALL
SELECT 'Ada' ,'Dailey' ,'Super Children in Action' ,'2809 Hamilton St'
GO

SELECT * FROM myTable99 o
WHERE EXISTS (SELECT MainContactFirst, MainContactLast
FROM myTable99 i
GROUP BY MainContactFirst, MainContactLast
HAVING Min(i.[ID]) = o.[ID]
)
select
mytable99.*
from
myTable99
Inner join
(select MainContactFirst, MainContactLast, Min(ID) as MinID
from myTable99
group by MainContactFirst, MainContactLast) a
on
a.MinID = myTable99.ID
GO

DROP TABLE myTable99
GO

- RoLY roLLs

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-06 : 13:49:21

Jeff...you playing me?

Oye...want a moron......what a scrub

Thanks RoLY roLLs

Nice curve Jeff...I thought you'd stick with thread, but you changed gears and I TOTALY missed it...

Damn

Brett

8-)

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-06 : 14:13:09

NP Brett! Even tho it was tiny, I finally glad to see i can help the big boys for once!

- RoLY roLLs

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources