What SQL 7.0 statements allows me to do ranking

[worldpeace]

I have a table with these fields:

Deptcode ID QTY Rank
-------- --- ---- ----
10 11 150 NULL
10 12 270 NULL
10 10 75 NULL
10 14 175 NULL
20 22 400 NULL
20 23 100 NULL
20 28 130 NULL
20 29 40 NULL
10 16 275 NULL

and want to make the table look like this

Deptcode ID QTY Rank
-------- --- ---- ----
10 16 275 1
10 12 270 2
10 14 175 3
10 11 150 4
10 10 75 5
20 22 400 1
20 28 130 2
20 23 100 3
20 29 40 4

Thanks for helps

 

[tlbroadbent]

There is no RANK function. You can write a query. There are various ways to do this. Here is one example.

Select
a.DeptCode, a.ID,
Min(a.QTY) As QTY,
Rank=Count(b.ID)
From YourTable a
Join YourTable b
On a.DeptCode=b.DeptCode
And a.Qty>=b.Qty
Group By a.DeptCode, a.ID
Order by a.DeptCode, a.QTY Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[worldpeace]

Thanks, Terry

 

[RPredrag]

Here is what you might use. #tbl temp. table is just your table from database so I just simulated by example and populated some data into that table. #tbl1 is temp. table you use in you query to get ranks. It looks like this:


===================


--This table should be table name from your database so you do not need to create it.



create table #tbl1(DeptCode int, id int, qty int, rank int)

declare @intCnt as int
set @intcnt = 1
--populate some data

while @intcnt <= 10
Begin
if @intcnt <= 5
Begin
insert into #tbl
select 20, @intcnt, 275 - (@intcnt * 3)
end
else
begin
insert into #tbl
select 10, @intcnt, 275 + @intCnt

end
set @intCnt = @intcnt + 1
End

go



/*This is code should be use for your query
#tbl1 should be changed with table name from
your database */


select deptcode, id, qty, Identity(int, 1, 1) Rank
Into #tbl1 from #tbl
order by deptcode asc, qty desc

-- This statment just shows results
select * From #tbl1