CREATE DATABASE TopPerGroup
GO
USE TopPerGroup
GO
CREATE VIEW Random -- Circumvent SQL Server's rules about functions being deterministic
AS
SELECT Rand = Rand()
GO
CREATE FUNCTION RandomName(@Len int)
RETURNS varchar(40)
AS
BEGIN
DECLARE
@Name varchar(40)
SELECT @Len = Floor(Rand * (@Len - 4) + 3) FROM Random
SET @Name = ''
WHILE @Len > 0 BEGIN
SELECT @Name = @Name + Char(Rand * 26 + 97) FROM Random
SET @Len = @Len - 1
END
SELECT @Name = Char(Rand * 26 + 65) + @Name FROM Random
RETURN @Name
END
GO
CREATE FUNCTION RandomFloat()
RETURNS float
AS
BEGIN
RETURN (SELECT Rand FROM Random)
END
GO
CREATE FUNCTION RandomNumber(@Limit int)
RETURNS int
AS
BEGIN
RETURN (SELECT Floor(Rand * @Limit) + 1 FROM Random)
END
GO
CREATE TABLE Location (
ID int identity(1,1) NOT NULL CONSTRAINT PK_Location PRIMARY KEY CLUSTERED,
Name varchar(40) not null
)
CREATE TABLE Customer (
ID int identity(1,1) NOT NULL CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED,
LocationID int NOT NULL CONSTRAINT FK_Customer_LocationID FOREIGN KEY REFERENCES Location(ID)
)
CREATE TABLE Address (
ID int identity(1,1) NOT NULL CONSTRAINT PK_Address PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL CONSTRAINT FK_Address_CustomerID FOREIGN KEY REFERENCES Customer(ID),
Zip char(5) NOT NULL CONSTRAINT CK_ZipFive CHECK (Zip LIKE '[0-9][0-9][0-9][0-9][0-9]')
)
GO
SET NOCOUNT ON
INSERT Location SELECT dbo.RandomName(20)
WHILE Scope_Identity() < 200 INSERT Location SELECT dbo.RandomName(20)
GO
INSERT Customer
SELECT ID
FROM
(
SELECT TOP 100 ID, Rnd = dbo.RandomFloat()
FROM Location
ORDER BY Rnd
) X
WHILE Scope_Identity() < 50000 BEGIN
INSERT Customer
SELECT ID
FROM
(
SELECT TOP 100 ID, Rnd = dbo.RandomFloat()
FROM Location
ORDER BY Rnd
) X
END
INSERT Address
SELECT ID, Right('0000' + Convert(varchar(5), dbo.RandomNumber(200) + 79999), 5)
FROM
(
SELECT TOP 100 ID, Rnd = dbo.RandomFloat()
FROM Customer
ORDER BY Rnd
) X
WHILE Scope_Identity() < 400000 BEGIN
INSERT Address
SELECT ID, Right('0000' + Convert(varchar(5), dbo.RandomNumber(200) + 89999), 5)
FROM
(
SELECT TOP 100 ID, Rnd = dbo.RandomFloat()
FROM Customer
ORDER BY Rnd
) X
END
GO
alter table address add Address1 varchar(128)
alter table address add Address2 varchar(128)
alter table address add City varchar(64)
alter table address add State char(2)
alter table address add AddressTypeID int
GO
update address set address1 = '1234 Somewhere Pl'
update address set address2 = 'Suite 200'
update address set city = dbo.RandomName(20)
update address set state = 'CA'
update address set addresstypeid = dbo.RandomNumber(4)
GO
--verify original query
Select l.name, a.zip, count(*) as count
From location l
inner join customer c on c.locationid = l.id
inner join address a on a.customerid = c.id
group by l.name, a.zip
order by name, count desc
--run these with SQL Profiler. The results in Query Analyzer with Trace on aren't giving the right answers.
--batch completed is all you need.
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
Select a.* from
(
Select l.name, a.zip, count(a.zip) as count
From location l
inner join customer c on c.locationid = l.id
inner join address a on a.customerid = c.id
group by l.name, a.zip
) a
inner join
(
Select l.name, a.zip, count(a.zip) as count
From location l
inner join customer c on c.locationid = l.id
inner join address a on a.customerid = c.id
group by l.name, a.zip
) b
on a.name = b.name
and a.count <= b.count
group by a.name, a.zip, a.count
having count(b.name) <= 3
order by a.name, a.count desc
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
SELECT
L.name,
A.zip,
Count = Count(A.zip)
INTO #Zips
FROM
location L
inner join customer C on C.locationid = L.id
inner join address A on A.customerid = C.id
GROUP BY
L.name,
A.zip
SELECT *
FROM #Zips Z
WHERE Zip IN (SELECT TOP 3 Zip FROM #Zips WHERE Name = Z.Name ORDER BY Count DESC)
ORDER BY
Name, Count DESC
DROP TABLE #Zips
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
SELECT TOP 100 PERCENT
sequence = identity(int, 1, 1),
L.name,
A.zip,
Count = Count(A.zip)
INTO #Zips
FROM
location L
inner join customer C on C.locationid = L.id
inner join address A on A.customerid = C.id
GROUP BY
L.name,
A.zip
ORDER BY
L.Name,
Count DESC
SELECT Z.Name, Z.Zip, Z.Count
FROM
#Zips Z
INNER JOIN (
SELECT Name, Limit = Min(Sequence) + 2 FROM #Zips GROUP BY NAME
) X ON Z.Name = X.Name AND Z.Sequence <= Limit
ORDER BY
Z.Name, Z.Count DESC
DROP TABLE #Zips
GO
USE master
GO
DROP DATABASE TopPerGroup
![[blush]](/data/assets/smilies/blush.gif "[blush] [blush]")
