Solved

How to search the greatest count among four temporary table.

Posted on 2011-02-16
2
303 Views
Last Modified: 2012-05-11
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
Comment
Question by:ExpertKapur
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 8

Accepted Solution

by:
rushShah earned 125 total points
ID: 34909091
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
 
LVL 13

Assisted Solution

by:devlab2012
devlab2012 earned 125 total points
ID: 34909097

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
union query column need default text 2 20
get_systemdrive info from tsql? 1 23
(sql serv16)ssis 2016 question/check 1 107
how to make geography query faster?  SQL 7 46
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question