Multiple Distincts - 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

General SQL Server Forums

New to SQL Server Programming

Multiple Distincts

Author

Topic

yawnzzzz
Starting Member

13 Posts

Posted - 2010-03-31 : 13:03:50

Hi,

I'm very new to SQL, so hopefully this is an easy request.

Basically, I'm running a query like this:

SELECT DISTINCT ORDER_ID, LOCATION FROM ORDERSTABLE

What I really want is only the ORDER_ID's and LOCATIONS where there's more than one LOCATION tied to the same ORDER_ID.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-31 : 13:07:37

[code]SELECT ORDER_ID, LOCATION
FROM ORDERSTABLE
GROUP BY
ORDER_ID
HAVING Count(LOCATION) > 1[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 13:12:07

quote:

Originally posted by russell

SELECT	ORDER_ID, LOCATION
FROM 	ORDERSTABLE
GROUP BY
	ORDER_ID
HAVING	Count(LOCATION) > 1

wont work. once you group on a field you need to apply aggregates on other fields.

It should be


SELECT	o1.ORDER_ID, o1.LOCATION
FROM 	ORDERSTABLE o1
JOIN (SELECT ORDER_ID
FROM 	ORDERSTABLE
GROUP BY ORDER_ID
HAVING	Count(LOCATION) > 1)o2
ON o2.ORDER_ID = o1.ORDER_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-03-31 : 15:41:04

whoops. good catch. thx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 05:12:22

no probs

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources