Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003

    Question Unanswered: Indexing a selection query


    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
    (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


  2. #2
    Join Date
    Dec 2003
    It 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.

  3. #3
    Join Date
    Sep 2003

    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:

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

    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).



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts