Query not working - 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

Query not working

Author

Topic

blumbdi
Starting Member

4 Posts

Posted - 2011-05-12 : 09:41:22

Hello Team, I want to query a large table (450.000.000 rows)on a Sybase 12.5 Server for 2 conditions and cannot get it running, because I do not know how to get the result for mor than 1 ckey. I want to get only 1 value from this table: the distinct study_ckey where the study_ckey does have more than 1 distinct volume_ckey. I get it running in a simple querey for one study_ckey:
1> select distinct study_ckey, volume_ckey from arc_address where study_ckey=2522429
2> go
study_ckey volume_ckey
----------- -----------
2522429 6
2522429 7

(2 rows affected)

I'm looking for a query where only the study_ckey is returned for the study_ckeys where the count of volume_ckey is greater than 1

Hopefully anybody can help!?!

blumbdi

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-12 : 09:43:12

quote:
Originally posted by blumbdi

Hello Team, I want to query a large table (450.000.000 rows)on a Sybase 12.5 Server for 2 conditions and cannot get it running, because I do not know how to get the result for mor than 1 ckey. I want to get only 1 value from this table: the distinct study_ckey where the study_ckey does have more than 1 distinct volume_ckey. I get it running in a simple querey for one study_ckey:
1> select distinct study_ckey, volume_ckey from arc_address where study_ckey=2522429
2> go
study_ckey volume_ckey
----------- -----------
2522429 6
2522429 7

(2 rows affected)

I'm looking for a query where only the study_ckey is returned for the study_ckeys where the count of volume_ckey is greater than 1

Hopefully anybody can help!?!

blumbdi

blumbdi
Starting Member

4 Posts

Posted - 2011-05-12 : 09:52:50

Hello lionofdezert,

thank you for the fast answer and the provided link, but I doesnt find a similar case on it, or do you mean I have to open the question again in the linked page?

blumbdi

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-12 : 10:01:41

Sorry, i just highlyted Sybase keyword from your post, because these forum are just about SQL Server , any way try this
SELECT study_ckey,
volume_ckey
FROM arc_address
WHERE study_ckey IN ( SELECT study_ckey
FROM arc_address
GROUP BY study_ckey
HAVING COUNT(study_ckey) > 1 )

blumbdi
Starting Member

4 Posts

Posted - 2011-05-12 : 10:40:26

Hello lionofdezert,

thank you for help, I tried it first for only one study_ckey and the results where not successfully, maybe I did not exactly described the problem: We have a multiple (study_ckey) to possiple multiple (volume_ckey) relationship. Example for a dataset where have only 1 volume_ckey for study_ckey 12345:

1> select count(distinct volume_ckey) from arc_address where study_ckey=12345
2> go

-----------
1

(1 row affected)
1> SELECT study_ckey,
volume_ckey
FROM arc_address
WHERE study_ckey IN ( SELECT study_ckey
FROM arc_address
GROUP BY study_ckey
HAVING COUNT(study_ckey) > 1 )

2> 3> 4> 5> 6> 7> 8> 9> and study_ckey=12345
10> go
study_ckey volume_ckey
----------- -----------
12345 540
12345 540

(2 rows affected)

Adding the distinct:

1> SELECT distinct study_ckey,
volume_ckey
FROM arc_address
WHERE study_ckey IN ( SELECT study_ckey
FROM arc_address
GROUP BY study_ckey
HAVING COUNT(study_ckey) > 1 )2> 3> 4> 5> 6> 7> and study_ckey=12345
8> go
study_ckey volume_ckey
----------- -----------
12345 540

(1 row affected)
1> select count(*) from arc_address where study_ckey=12345
2> go

-----------
2

(1 row affected)

Any other idea?

blumbdi

blumbdi
Starting Member

4 Posts

Posted - 2011-05-12 : 13:13:26

I get it:

select study_ckey, count(distinct volume_ckey) from arc_address where study_ckey > 1 group by study_ckey having count(distinct volume_ckey)>1

Thank you for support,

blumbdi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources