SubQuery not working correctly.

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

SubQuery not working correctly.

Author

Topic

vital101
Starting Member

4 Posts

Posted - 2008-04-14 : 13:04:33

I've recently just got started using sub queries. The sub query that I'm running is supposed to select distinct values out of a table. It doesn't seem to be working though.


SELECT id
FROM data
WHERE (issueID IN
     (SELECT DISTINCT issueID
      FROM data))

When I execute this query, it returns a list of id's, but they don't each have a unique issueID. In the data table, issueID can appear more than once (hence, I need to use distinct). However, id is the primary key, and is unique.

Any ideas?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-14 : 13:07:18

Not clear what you want to achieve here. Can you post some sample data and expected output?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-14 : 13:07:27

and which id do you wish to return for those that have more than one issueID?
min, max?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-14 : 13:07:29

That will just return everything in the table - think about it.
How about

select issueID, max(id)
from data d
group by issueID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-14 : 13:07:55

wow... 11 seconds apart...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 13:10:55

They wont. You are just selecting all pks with issueids matching that in distinct list. But still you're selecting from main tables and hence all records will be retrieved. I think what you want is

SELECT d.id,d.IssueID
FROM data d
INNER JOIN
(
SELECT MAX(id) AS id,IssueID
FROM data
GROUP BY IssueID )t
ON t.IssueID=d.IssueID
AND t.id=d.id

vital101
Starting Member

4 Posts

Posted - 2008-04-14 : 13:20:19

Thank you so much for the help! The last suggestion by visakh16 worked like a charm!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-14 : 13:23:22

yes.. but is that really what you need? why would the max id be ok and min id not?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources