abgail
asked on
How to get results in order of second query
I have these 2 queries that currently order the results by tic, and I need the results ordered by the results of the 2nd query.
insert into table3 (field1, field2,field3, field4, column2, column3)
select '3352', 0,'top tic',0,tic,a.idnumber from
(select top 10 tic, a.idnumber from table1 a inner join table2 b on a.idnumber = b.idnumber
where custfield = '3352' and ratecode >= '7' group by a.tic, a.idnumber
order by count(distinct tic) desc ) a
update table3 set column4 = (select count(distinct idnumber) from table1 where tic = column3
and custfield = '3352' and field3 = 'top tic') where field1 = '3352' and field3 = 'top tic'
insert into table3 (field1, field2,field3, field4, column2, column3)
select '3352', 0,'top tic',0,tic,a.idnumber from
(select top 10 tic, a.idnumber from table1 a inner join table2 b on a.idnumber = b.idnumber
where custfield = '3352' and ratecode >= '7' group by a.tic, a.idnumber
order by count(distinct tic) desc ) a
update table3 set column4 = (select count(distinct idnumber) from table1 where tic = column3
and custfield = '3352' and field3 = 'top tic') where field1 = '3352' and field3 = 'top tic'
can you explain what you mean/intend...
both statements are "updates" and so do not have any relational order...
some example data may assist.
does the first statement actually work? (wont count(distinct tic) always be 1?)
both statements are "updates" and so do not have any relational order...
some example data may assist.
does the first statement actually work? (wont count(distinct tic) always be 1?)
ASKER
select top 10 tic, a.idnumber from table1 a inner join table2 b on a.idnumber = b.idnumber
where custfield = '3352' and ratecode >= '7' group by a.tic, a.idnumber
order by count(distinct tic) desc
this query, (pare of the 1st works. It gives me a tic with the most idnumbers associated with that tic. I only want one tic. The 2nd query is providing the count of idnumber associated with the tics from the 1st query
where custfield = '3352' and ratecode >= '7' group by a.tic, a.idnumber
order by count(distinct tic) desc
this query, (pare of the 1st works. It gives me a tic with the most idnumbers associated with that tic. I only want one tic. The 2nd query is providing the count of idnumber associated with the tics from the 1st query
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was making it much harder than it was, thanks
also, your order in insert query does not mean anything...