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

Simple subquery

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-17 : 12:57:33

I am trying to learn the basics of subqueries. Here i want to return all columns from MyTable for aspecific value of Specialty for distinct name IDs.

What should the syntax be?

SELECT *
FROM MyTable
WHERE IN (SELECT DISTINCT NameID FROM MyTable WHERE Specialty = 'X1')

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-17 : 13:00:00

No need for subquery here at all.

SELECT *
FROM MyTable 
WHERE Specialty = 'X1'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-17 : 13:02:47

The problem is NameID is not unique in MyTable. So WHERE Specialty = 'X1' will return many rows with the same NameID. I need one instance of each NameID where Specialty = 'X1'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-18 : 02:30:49

Can you post some sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-18 : 03:04:21

SELECT DISTINCT NameID
FROM MyTable
WHERE Specialty = 'X1'

E 12°55'05.25"
N 56°04'39.16"

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-18 : 09:41:26

The problem I have had when working with DISTINCT is that all columns in the select list are engaged in the distinct. I have had requests, for example, for distinct NameID and Specialty but include all additional columns in the table. If the data were…

NameID…….Specialty……….ColX
67……………..X1………………A
67……………..X1………………B

These two rows would be unique and appear in the result set. So I could not use a DISTINCT. That is why I went with a subquery and * for the outer select field list. Is there a better way to get all columns in the output but “distinct” on only a few columns?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-18 : 09:48:00

What is your expected result for the sample data above?

E 12°55'05.25"
N 56°04'39.16"

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-18 : 09:56:55

The value for ColX could be A or B. The request is to have the column present in the result set. What is important is having unique on NameID, Specialty

NameID…….Specialty……….ColX
67……………..X1………………A

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-18 : 11:08:58

SELECT NameID,Specialty,MAX(ColX) AS ColX
FROM Table
GROUP BY NameID,Specialty

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-18 : 11:15:08

Ahhhh I see.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources