Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

How to search the greatest count among four temporary table.

I have four temporary tables, they are billingaddress, postaladdress, shippingaddress, officeaddress now there are different number of rows in each table. I want to find out which of them has highest number of rows and assign the variable @x that value. how to get the highest count(*) among 4 #temporary tables?
0
ExpertKapur
Asked:
ExpertKapur
2 Solutions
 
rushShahCommented:
you can try something like this,


DECLARE @x INT
select @x = Count(*) from billingaddress
select @x = CASE WHEN @x > Count(*) then @x else Count(*) end from postaladdress
select @x = CASE WHEN @x > Count(*) then @x else Count(*) end from shippingaddress
select @x = CASE WHEN @x > Count(*) then @x else Count(*) end  from officeaddress

select @x

Open in new window

0
 
devlab2012Commented:

select max(cnt) from
(
select count(*) AS cnt from tb1
UNION ALL
select count(*) AS cnt from tb2
UNION ALL
select count(*) AS cnt from tb3
UNION ALL
select count(*) AS cnt from tb4
) #temp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now