Help with query

22 Dec 2005 7:35 PM

ViLo

Hi all,

I have the following table:
CREATE TABLE [dbo].[Company](
        [CompanyID] [int] IDENTITY(1,1) NOT NULL,
        [CompanyName] [varchar](100) NOT NULL,
        [CompanyTypeID] [int] NOT NULL,
        [Active] [bit] NOT NULL)

These companies can have a variety of CompanyTypeID's. I want to
retrieve a different number of records for each CompanyTypeID in one
call.

Ex.:

DECLARE @countType1 int
DECLARE @countType2 int
DECLARE @countType3 int
DECLARE @countType4 int
DECLARE @countType5 int

SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
UNION
SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
UNION
SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
UNION
SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4

At this point, Im doing it all with a UNION and getting what I want.
IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a

HAVING and use the @count variables to return the desired number of
records per companytype.

Thanks,
Victor

22 Dec 2005 10:47 PM

Trey Walpole

What's wrong with using the union?
you'll want to change it to a UNION ALL in the interim, though, since
there won't be any duplicates to eliminate.

ViLo wrote:
Show quote

> Hi all,
>
>
> I have the following table:
> CREATE TABLE [dbo].[Company](
>         [CompanyID] [int] IDENTITY(1,1) NOT NULL,
>         [CompanyName] [varchar](100) NOT NULL,
>         [CompanyTypeID] [int] NOT NULL,
>         [Active] [bit] NOT NULL)
>
>
> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
>
> Ex.:
>
>
> DECLARE @countType1 int
> DECLARE @countType2 int
> DECLARE @countType3 int
> DECLARE @countType4 int
> DECLARE @countType5 int
>
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
>
> HAVING and use the @count variables to return the desired number of
> records per companytype.
>
>
> Thanks,
> Victor
>

22 Dec 2005 10:52 PM

Erland Sommarskog

ViLo (vic***@lamovidarecords.com) writes:
Show quote

> These companies can have a variety of CompanyTypeID's. I want to
> retrieve a different number of records for each CompanyTypeID in one
> call.
>
>
> Ex.:
>
>
> DECLARE @countType1 int
> DECLARE @countType2 int
> DECLARE @countType3 int
> DECLARE @countType4 int
> DECLARE @countType5 int
>
>
> SELECT TOP 100 * FROM COMPANY WHERE COMPANYTYPEID = 1
> UNION
> SELECT TOP 200 * FROM COMPANY WHERE COMPANYTYPEID = 2
> UNION
> SELECT TOP 300 * FROM COMPANY WHERE COMPANYTYPEID = 3
> UNION
> SELECT TOP 25 * FROM COMPANY WHERE COMPANYTYPEID = 4
>
>
> At this point, Im doing it all with a UNION and getting what I want.
> IS THERE A WAY TO WRITE THIS QUERY WITHOUT A UNION using a GROUPBY or a
> HAVING and use the @count variables to return the desired number of
> records per companytype.

First of all, your query looks funny, as you say TOP 100 but as
there is no ORDER BY, you will get the 100, 200, etc as the optimizer
sees fit. Which will be neither random, nor deterministic.

If you are on SQL 2005 you can use TOP (@count) if the arbitrary
choice is OK to you.

On SQL 2000, you probably need to use a temp table, particularly if you
want the TOP 200 by alphabet or so.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx