Indexing a selection query

Hey,

I've started a new topic for this, as the old was is a different problem, lol.

This script is working now, however it says the unitIndex is 287 for all units.

What I wanted the unitIndex to be is, 1 for the unit with highest tsr, and (if 30 units) 30 for the unit with lowest tsr.

Why is it making the UnitIndex 287, and how can I fix it? lol

(I have marked the line where the unitIndex is

PROCEDURE units_display (@.startIndex INT, @.endIndex INT)
declare @.maxrts bigint;
select @.maxrts = (select Distinct max((((wins)*((wins*100)/((games+1)*4))))) from unit);
if @.maxrts = 0
set @.maxrts = 1;
declare @.maxValue bigint;
set @.maxValue=2500
select *
from (select *, (select unit from UnitTypes where UnitTypes.unitid = unit.UnitType) as unitTypeName, ((@.maxValue*((wins)*((wins*100)/((games+1)*4))))/@.maxrts ) as trs2 from Unit) as Unit1
inner join
(select Unit3.trs, count(*) as UnitIndex --< here is UnitIndex
from
(select ((@.maxValue*((wins)*((wins*100)/((games+1)*4))))/@.maxrts ) as trs from Unit) as unit3
inner join
(select ((@.maxValue*((wins)*((wins*100)/((games+1)*4))))/@.maxrts ) as trs3 from Unit) as Unit2 on Unit3.trs <= Unit2.trs3
group by Unit3.trs) as UnitIndexes
on Unit1.trs2 = UnitIndexes.trs
where UnitIndexes.UnitIndex between @.StartIndex and @.EndIndex
order by Unit1.trs2 DESC

ThanksIt seems like you are making this more complicated than it should be. What are the table definitions and what are you trying to do? Is tsr a calculated field?

To order the records and determine a unit index for them, I would create a subquery in the select clause that counts all records that have a greater tsr.|||Hey,

yea, if I can simplify this, that's be awesome.

I am only looking at one table, I want to select all the fields from it, but relevant fields are:

UNIT
unitid int pk
Name vc(50)
games int
wins int
unittype int (fk referencing UnitTypes, unitid)

UNITTYPES
unitid int PK
unit varchar(20)

tsr is a column I am working out on the fly, it isn't stored in the database. perhaps a better method would be to create a view first with tsr in it, then run the reorder etc after, however I am unsure how views work, and as of yet, I havn't had time to look into them to determine if they are what I should use.

Any help simplifying this would be awesome, lol.

I have solved the issue I had with the script below, however if you can should me however, it is just a repair on the code as it is there, lol

I'll post the repair in here if you'd like to work from that. Basically though, once I determine the column tsr, I then want to determine the 'ranks' of each unit. So basically, units with the same tsr score, get the worse rank, ie, the third and fourth highest scores both get rank 4 (rank = unitindex form the script below). once that is done, I then was to assign a position variable to each row.

I am doing the same thing with a similar query, which a few people are discussing with me in another post. From what they have said, I will be looking at creating a table, with the columns i desire, plus am incrementing 'position' column.

I will then use something like

insert into @.tempTable (the select statement which returns a reordered table with the tsr value).

Thanks
-Ashleigh

-Ashleigh