most active address - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

most active address

Author

Topic

rb1373
Yak Posting Veteran

93 Posts

Posted - 2005-01-14 : 13:00:44

I am trying to return a single address for each taxid based on the most activity. Most activity is defined as the address with the most checks. If there is a tie in checks, then use total. The problem with the query below is it returns multiple addresses for taxID D. How can I achieve the desired results where there is a single address for taxid D? Thanks. - ray

select myid, a.taxid, address, checks, total
from mytable a
join (select TaxID, max(checks) as max_checks
from mytable where total > 0 group by TaxID) b on a.TaxID = b.TaxID and a.checks = b.max_checks
order by myid

Desired Results:

myid taxid address checks total
1 A Apple St. 1 10.0000
3 B Blue St. 2 20.0000
5 C PO Box 1 2 20.0000
9 D Dingo St. 1 30.0000
11 E Emu Pass 1 10.0000

CREATE TABLE [dbo].[MyTable] (
[MyID] [int] IDENTITY (1, 1) NOT NULL ,
[TaxID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Checks] [int] NULL ,
[Total] [money] NULL
) ON [PRIMARY]
GO

INSERT mytable VALUES('A', 'Apple St.', 1, 10)
INSERT mytable VALUES('B', 'Banana St.', 1, 10)
INSERT mytable VALUES('B', 'Blue St.', 2, 20)
INSERT mytable VALUES('C', 'Car Ave.', 1, 10)
INSERT mytable VALUES('C', 'PO Box 1', 2, 20)
INSERT mytable VALUES('C', 'Cowboy Way', 1, 50)
INSERT mytable VALUES('D', 'Dark Ave.', 1, 10)
INSERT mytable VALUES('D', 'Dove Rd.', 1, 20)
INSERT mytable VALUES('D', 'Dingo St.', 1, 30)
INSERT mytable VALUES('E', 'Elephant Ave.', 2, 0)
INSERT mytable VALUES('E', 'Emu Pass', 1, 10)

(I haven't attempted to address this issue yet, but if there is a tie between checks and total for a taxid, use the max MyID.)

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-01-14 : 13:06:21

something like:

select t.* from
(
select max(t.myid) myid, d.taxid
from mytable t
join 
(
	select taxid, max(checks) checks
	from mytable
	group by taxid
) d on t.taxid = d.taxid and t.checks = d.checks
group by d.taxid
) d
join mytable t on d.myid = t.myid

rb1373
Yak Posting Veteran

93 Posts

Posted - 2005-01-14 : 13:44:26

Very helpful! Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources