Ranking Rows

[rhaazy]

Instead of explaining my example I took the one that i used to get as
far as I am now to demonstrate what the task is I'm trying to complete.
Consider the following:

CREATE TABLE #teams
(
city VARCHAR(20),
team VARCHAR(20)
)

SET NOCOUNT ON

INSERT #teams SELECT 'Boston', 'Celtics'
INSERT #teams SELECT 'Boston', 'Bruins'
INSERT #teams SELECT 'Boston', 'Red Sox'
INSERT #teams SELECT 'New York', 'Yankees'
INSERT #teams SELECT 'New York', 'Mets'
INSERT #teams SELECT 'New York', 'Knicks'
INSERT #teams SELECT 'New York', 'Rangers'
INSERT #teams SELECT 'New York', 'Islanders'
INSERT #teams SELECT 'New York', 'Jets'
INSERT #teams SELECT 'New York', 'Giants'
INSERT #teams SELECT 'Chicago', 'Black Hawks'
INSERT #teams SELECT 'Chicago', 'Cubs'
INSERT #teams SELECT 'Chicago', 'White Sox'
INSERT #teams SELECT 'Chicago', 'Bears'
INSERT #teams SELECT 'New England', 'Patriots'

SELECT city, team, rank =
(
SELECT COUNT(*)
FROM #teams t2
WHERE t2.city = t1.city
AND t2.team <= t1.team
)
FROM #teams t1
ORDER BY city, team

DROP TABLE #teams

THE RESULTS ARE AS FOLLOWS:
city team rank
------------ ------------ ----
Boston Bruins 1
Boston Celtics 2
Boston Red Sox 3
Chicago Bears 1
Chicago Black Hawks 2
Chicago Cubs 3
Chicago White Sox 4
New England Patriots 1
New York Giants 1
New York Islanders 2
New York Jets 3
New York Knicks 4
New York Mets 5
New York Rangers 6
New York Yankees 7

How would I keep this same numbering convention if my team names
weren't unique. This does not normally make sense for the purpose of
relational databases but it is what i need to do in my situation.

[Alexander Kuznetsov]

if you are on SQL Server 2005, look up ROW_NUMBER(), RANK() and
DENSE_RANK() functions.
Can you give sample output for non unique team names, such as

Boston Bruins 1

Boston Bruins 1
Boston Celtics 2

Boston Celtics 2

or

Boston Bruins 1
Boston Bruins 2
Boston Celtics 3
Boston Celtics 4

[Alexander Kuznetsov]

CREATE TABLE #teams
(
city VARCHAR(20),
team VARCHAR(20)
)
SET NOCOUNT ON
INSERT #teams SELECT 'Boston', 'Celtics'
INSERT #teams SELECT 'Boston', 'Celtics'
INSERT #teams SELECT 'Boston', 'Bruins'
INSERT #teams SELECT 'Boston', 'Bruins'
INSERT #teams SELECT 'Boston', 'Bruins'
INSERT #teams SELECT 'Boston', 'Red Sox'

select 3 n
into #n
union all
select 1
union all
select 2
go

a stab in the dark:

select t.city, t.team, (

SELECT COUNT(*)
FROM #teams t2

WHERE t2.city = t.city
AND t2.team < t.team
) + n.n rank
from (select city, team, count(*) cnt
FROM #teams
group BY city, team) t
join #n n on n.n<=t.cnt

city team rank
-------------------- -------------------- -----------
Boston Bruins 3

Boston Bruins 1
Boston Bruins 2

Boston Celtics 4
Boston Celtics 5
Boston Red Sox 6

[rhaazy]

Sorry for not mentionion im using ms sql 2000 and the second example
you posted is exactly what i want.

[rhaazy]

Please explain what is going on here in your example:

[Alexander Kuznetsov]

it's a shortcut for

create table #n(n int)
insert into #n values(1)
insert into #n values(2)
insert into #n values(3)

it's commonly called a sequence table

[rhaazy]

Thanks for all the help, you knew exactly what it was that I wanted
given the info I gave you, however I'm having some trouble adapting how
you fixed my example to what I really have. Maybe you can work
something with this little bit of info I give you:

INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID AND #dup.name<=#temp.scanattribute),
tblScanAttribute.ScanAttributeID, tblScanAttribute.ScanSectionID,
#temp.scanattribute FROM tblScanAttribute, #temp
WHERE #temp.ID like '23.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '23'
ORDER BY ScanAttributeID

Results in:

2 151 18 5/4/2006 1:11 PM
9 151 18 5/4/2006 12:57 PM
10 151 18 5/4/2006 12:59 PM
1 151 18 5/4/2006 1:04 PM
6 151 18 5/4/2006 12:06 PM
7 151 18 5/4/2006 12:15 PM
8 151 18 5/4/2006 12:36 PM
3 151 18 5/4/2006 11:42 AM
4 151 18 5/4/2006 11:48 AM
5 151 18 5/4/2006 12:05 PM
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup

The first column is where my problem is, the counting works fine
because the time stamps are unique, but when i move on to the other
part, where they are not it bugs out, all that needs to be done is your
logic applied to my method which should be possible because I used the
first example to get where I am to now.

[Alan Samet]

SELECT IDENTITY (INT, 1, 1) RowNumber
, *
INTO #Results
FROM #teams
ORDER BY city, team

SELECT * FROM #Results

[John Smith]

Check this site out.

http://support.microsoft.com/default.aspx?scid=kb;en-us;q186133

"rhaazy" <rha...@gmail.com> wrote in message
news:1147968713.0...@j73g2000cwa.googlegroups.com...