September 22, 2008 at 12:17 pm
My data is to select totvisits > 1
and all scores if > 4
data looks like this
id totvisits score
706 10 2
706 10 8
706 10 9
706 10 5
706 10 3
706 10 2
706 10 1
708 2 2
706 2 1
708 3 9
708 3 8
708 3 8
select id, totvisits, score
into sav1
from coll1
where totvisits > 1
select id, totvisits, score
into sav2
from sav1
where score > 3
select id, totvisits, score
into coll2
from sav1 a
join
sav2 b
on a.id = b.id
September 22, 2008 at 1:05 pm
What's the issue? Do those queries not work? Or do you need them in one query?
Use and AND in the WHERE clause and include both criteria.
September 22, 2008 at 1:12 pm
Tried to use
where (a.totvisitists > 1 or b.score > 3)
but I wanted to pick up all the rows from the first id, not just the scores >3
September 22, 2008 at 1:39 pm
Solution
My data is to select totvisits > 1
and all scores if > 4
data looks like this
id totvisits score
706 10 2
706 10 8
706 10 9
706 10 5
706 10 3
706 10 2
706 10 1
708 2 2
706 2 1
708 3 9
708 3 8
708 3 8
select id, count( prev.visits) as totvisits
into sav1
from coll1
group by id
order by id
select id
into sav2
from sav1
where totvisits > 1
group by id
order by id
select id, score
into sav3
from coll1 prev
where score > 3
order by id
select distinct id
into sav4
from sav3
order by id
select a.id
b.score
into sav5
from sav4
join
coll1 b
on
a.id=b.id
order by id
results: id and scores
706 2
706 8
706 9
706 5
706 3
706 2
706 1
708 9
708 8
708 8
September 22, 2008 at 1:59 pm
Your logic eludes me. I can't figure out what you are asking for, let alone figure out how your logic provides you with the result set you get from the your original request.
Perhaps a little more detail on why certain records in the example were selected and others weren't would help.
All of your sample data has totvisits > 1, so all records should be returned.
😎
September 22, 2008 at 2:26 pm
Thanks for the post, I had two objectives here from the large data collection.
One was multiple visits (gt 2) and the other was to have a score above 4.
I was able to do this (i am a newbie) in a where but I wanted to collect all the scores.
So I made several queries one to count the visits one to collect scores the just using the id I returned to the collection to bring all the scores of my selected id.
I have this as a solution. I would appreciate a better attempt though.
September 22, 2008 at 3:30 pm
garypete (9/22/2008)
Thanks for the post, I had two objectives here from the large data collection.One was multiple visits (gt 2) and the other was to have a score above 4.
I was able to do this (i am a newbie) in a where but I wanted to collect all the scores.
So I made several queries one to count the visits one to collect scores the just using the id I returned to the collection to bring all the scores of my selected id.
I have this as a solution. I would appreciate a better attempt though.
Now it is even more confusing. Your original request specified totvisits > 1 and all where score > 4. Above, you are saying > 2 and then score > 4.
So, let me try and paraphrase what you are looking for in the query.
Select all records where totvisits > 2 and all records where score > 4 (regardless of totvisits). Is this correct?
😎
September 22, 2008 at 3:34 pm
Instead of waiting for confirmation, here is the code I was working with:
create table #TestTab (
id int,
totvisits int,
score int
);
insert into #TestTab (
id,
totvisits,
score
)
select 706, 10, 2 union all
select 706, 10, 8 union all
select 706, 10, 9 union all
select 706, 10, 5 union all
select 706, 10, 3 union all
select 706, 10, 2 union all
select 706, 10, 1 union all
select 708, 2, 2 union all
select 706, 2, 1 union all
select 708, 3, 9 union all
select 708, 3, 8 union all
select 708, 3, 8;
select
*
from
#TestTab
order by
id;
select
*
from
#TestTab
where
totvisits > 2
or score > 4
order by
id;
drop table #TestTab;
😎
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply