Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    65

    Unanswered: selecting an index (query)

    Hey,

    I'd like to do a simple select statement, which adds a number to each row. The table is already in the correct order. Thy way I tried to do it was

    select *, count(*) as Position
    from myTable

    however, this returns an error, as count(*) wants to tell me the total number of rows, not the number of each row.

    In a select statement, is there a simple way of doing this?

    Thanks,
    -Ashleigh
    -Ashleigh

  2. #2
    Join Date
    Sep 2003
    Posts
    65
    In relation to the question above. I have kind of worked it out, I need to use a group by statement, then it will index them...

    My problem relates to the output of the following procedure.

    It works perfectly, does exactly what it says it is doing. However, when I select the MemberIndexes 1-5, say everyone has the same trs rating, they all get a memberIndex of 200 (assuming 200 members).

    I would therefore like to add another variable, which places another count on all the columns. with 1 being the person with highest trs and first callsign in the alphabet, and 200 being the person with lowest trs and last callsign (alphabetically).

    Callsign is unique, thus each member should be given a number 1-200, with no doubles.

    This variable can have any name, although ideally I'd like to call it memberindex, and replace the current memberindex with the name rank.

    I know this is a big ugly procedure, and if say, dividing to into views would be better, that's cool. However I have no idea how to do that, (or how to fix this function). I've spent 3 days on it now, lol. I'm much more attuned to sql now than i was before, but I still feel like a kid playing in someone elses backyard :-(...

    Thankyou for all your help

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    ALTER PROCEDURE ladder_member_trs (@startIndex INT, @endIndex INT)
    AS
    declare @maxrts bigint;
    select @maxrts = (select Distinct max((((wins)*((wins*100)/((games+1)*4))))) from Member);
    if @maxrts = 0
    set @maxrts = 1;
    declare @maxValue bigint;
    set @maxValue=2500
    select *
    from (select *, ((@maxValue*((wins)*((wins*100)/((games+1)*4))))/@maxrts ) as trs2 from Member) as Member1
    inner join
    (select Member3.trs, Member3.Callsign, count(*) as MemberIndex
    from
    (select Member.Callsign, ((@maxValue*((wins)*((wins*100)/((games+1)*4))))/@maxrts ) as trs from Member) as Member3
    inner join
    (select Member.Callsign, ((@maxValue*((wins)*((wins*100)/((games+1)*4))))/@maxrts ) as trs3 from Member) as Member2
    on Member3.trs <= Member2.trs3
    group by Member3.trs, Member3.Callsign) as MemberIndexes
    on Member1.trs2 = MemberIndexes.trs
    where MemberIndexes.MemberIndex between @StartIndex and @EndIndex
    order by Member1.trs2 DESC
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
    -Ashleigh

  3. #3
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    ???? U'r first post was to assign running number to your select statement rite?

    simplest way:
    Code:
    e.g. from northwind db
    
    declare @TempTable Table
    (Postion int  IDENTITY (1, 1) , lastName varchar(50))
    
    insert into @TempTable select LastName from Employees
    
    select * from @TempTable
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  4. #4
    Join Date
    Sep 2003
    Posts
    65
    Hey Patrick,

    Hm, that seems like a good solution, when I get back to my sql machine I'll have a play with it.

    So, using the select statement I have in my second post, would this do what I am trying to do?

    PROCEDURE ladder_member_trs (@startIndex INT, @endIndex INT)
    AS
    declare @maxrts bigint;
    select @maxrts = (select Distinct max((((wins)*((wins*100)/((games+1)*4))))) from Member);
    if @maxrts = 0
    set @maxrts = 1;
    declare @maxValue bigint;
    set @maxValue=2500

    declare @tempTable table
    (Position int IDENTITY(1,1), tsr bigint, callsign varchar(30)....etc....)

    insert into @tempTable --below is the old, working, select statement
    (select *
    from (select *, ((@maxValue*((wins)*((wins*100)/((games+1)*4))))/@maxrts ) as trs2 from Member) as Member1
    inner join
    (select Member3.trs, Member3.Callsign, count(*) as MemberIndex
    from
    (select Member.Callsign, ((@maxValue*((wins)*((wins*100)/((games+1)*4))))/@maxrts ) as trs from Member) as Member3
    inner join
    (select Member.Callsign, ((@maxValue*((wins)*((wins*100)/((games+1)*4))))/@maxrts ) as trs3 from Member) as Member2
    on Member3.trs <= Member2.trs3
    group by Member3.trs, Member3.Callsign) as MemberIndexes
    on Member1.trs2 = MemberIndexes.trs
    order by Member1.trs2 DESC)

    select * from @TempTable
    where Position between @StartIndex and @EndIndex

    GO
    -Ashleigh

  5. #5
    Join Date
    Sep 2003
    Posts
    65
    Hey, I just tested the code below:

    declare @maxrts bigint;
    select @maxrts = (select Distinct max((((wins)*((wins*100)/((games+1)*4))))) from Member);
    if @maxrts = 0
    set @maxrts = 1;
    declare @maxValue bigint;
    set @maxValue=2500


    declare @tempTable table
    (Position int IDENTITY(1,1), tsr bigint, callsign varchar(30), memberindex int)

    insert into @tempTable select tsr, callsign, memberindex from
    (select *
    from (select *, ((@maxValue*((wins)*((wins*100)/((games+1)*4))))/@maxrts ) as trs2 from Member) as Member1
    inner join
    (select Member3.trs, Member3.Callsign, count(*) as MemberIndex
    from
    (select Member.Callsign, ((@maxValue*((wins)*((wins*100)/((games+1)*4))))/@maxrts ) as trs from Member) as Member3
    inner join
    (select Member.Callsign, ((@maxValue*((wins)*((wins*100)/((games+1)*4))))/@maxrts ) as trs3 from Member) as Member2
    on Member3.trs <= Member2.trs3
    group by Member3.trs, Member3.Callsign) as MemberIndexes
    on Member1.trs2 = MemberIndexes.trs
    order by Member1.trs2 DESC)

    select * from @TempTable
    where Position between 1 and 3

    It returns the following error:
    Server: Msg 1033, Level 15, State 1, Line 24
    The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.


    I need the order by, to ensure the table is in the correct order.

    If it helps though, I removed it for fun, and got this error:

    Server: Msg 156, Level 15, State 1, Line 25
    Incorrect syntax near the keyword 'select'.


    Any help would be sick,
    Thanks
    -Ashleigh
    -Ashleigh

Posting Permissions

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