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

    Unhappy Unanswered: SQL Select, inner join, re ordering table

    Hello,

    I few months ago, I was given the following SQL statement by blindman (which is awesome, thanks a lot mate)

    select *
    from Unit
    inner join
    (select Unit.Name, count(*) as UnitIndex
    from Unit
    inner join Unit Unit2 on Unit.Name >= Unit2.Name
    group by Unit.Name) UnitIndexes
    on Unit.Name = UnitIndexes.Name
    where UnitIndexes.UnitIndex between @StartIndex and @EndIndex
    order by Unit.Name ASC

    Above procedure works perfectly, just showing you so you have a base idea of what I started with (incase I killed it with my fiddling below, lol)

    I am calculating a new column on the creation of this query now. I can display it by simply adding it to the select on line 1.

    however, I am attempting to have the list ordered by it. Below you can see my attempt. Hopefully I'm pretty close to correct on this one. In terms of locating the error, the colums i have used are all valid. my 'trs' column, i have used before. All columns listed are contained within the Unit table.

    Thanks a lot for your help ;-).

    Code with errors:

    PROCEDURE ladder_unit_trs (@startIndex INT, @endIndex INT)
    AS
    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 *, ((@maxValue*((wins)*((wins*100)/((games+1)*4))))/@maxrts ) as trs
    from Unit
    inner join

    ((select Unit.Name, count(*) as UnitIndex, ((@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 trs
    from Unit) as Unit2
    on Unit3.trs >= Unit2.trs
    group by Unit3.trs) UnitIndexes

    on Unit.Name = UnitIndexes.Name
    where UnitIndexes.UnitIndex between @StartIndex and @EndIndex
    order by Unit.TRS DESC

    To clarify 1 more time (lol).

    The output I am looking to recieve is:
    an ordered list from highest trs to lowest trs of all units. (between my index values).

    Thanks a lot for your time,
    -Ashleigh
    -Ashleigh

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Does it work without the order by and what error is returned ?

  3. #3
    Join Date
    Sep 2003
    Posts
    65
    Originally posted by rnealejr
    Does it work without the order by and what error is returned ?
    The error I get is:
    Server: Msg 156, Level 15, State 1, Procedure ladder_unit_trs, Line 24
    Incorrect syntax near the keyword 'group'.

    I'm pretty sure it is this bit in here:

    ((select Unit.Name, count(*) as UnitIndex, ((@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 trs
    from Unit) as Unit2
    on Unit3.trs >= Unit2.trs
    group by Unit3.trs)

    that I am have trouble with. But I seen to often have trouble with tested inner joins.

    removing the order by doesn't help

    Thanks,
    -Ashleigh
    -Ashleigh

  4. #4
    Join Date
    Sep 2003
    Posts
    65
    If it makes matters earier, this code returns the same error (I just removed the formula used to determine the 'custom column':

    Server: Msg 156, Level 15, State 1, Procedure ladder_unit_trs, Line 23
    Incorrect syntax near the keyword 'group'.

    I think the error is related to the second inner join, and the way I am referencing the variables. I have tried everything, but nothing seems to fix it.

    PROCEDURE ladder_unit_trs (@startIndex INT, @endIndex INT)
    AS

    select *, Name as trs
    from Unit
    inner join
    ((select Unit3.Name as trs, count(*) as UnitIndex from Unit) as Unit3
    inner join
    (select Unit2.Name as trs from Unit) as Unit2
    on Unit3.trs >= Unit2.trs
    group by Unit3.trs) as UnitIndexes
    on Unit.trs = UnitIndexes.trs
    where UnitIndexes.UnitIndex between @StartIndex and @EndIndex
    order by Unit.trs DESC

    Thanks
    -Ashleigh

  5. #5
    Join Date
    Sep 2003
    Posts
    65
    sorry, the error message is actually

    Server: Msg 156, Level 15, State 1, Procedure ladder_unit_trs, Line 16
    Incorrect syntax near the keyword 'group'.

    for the above sample
    -Ashleigh

  6. #6
    Join Date
    Dec 2003
    Posts
    39
    I think inner join and order by are not the problem, but the "group by" as the error message has told us.

    ( (select Unit.Name, count(*) as UnitIndex, ((@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 trs
    from Unit
    ) as Unit2
    on Unit3.trs >= Unit2.trs
    group by Unit3.trs
    ) UnitIndexes


    Since u use "group by Unit3.trs", so you can only select Unit3.trs and any aggregate functions, not the Unit.name and trs that is not contained in aggregate function.

  7. #7
    Join Date
    Dec 2003
    Posts
    2

    Re: SQL Select, inner join, re ordering table

    You can not use GROUP BY clause inside a FROM clause and that's why you got syntax error.

    Your query structure (after simplifications) is:

    select *, Name as trs
    from Unit inner join
        (Unit3 inner join Unit2
        on Unit3.trs >= Unit2.trs
        group by Unit3.trs) as UnitIndexes
    on Unit.trs = UnitIndexes.trs
    where UnitIndexes.UnitIndex between @StartIndex and @EndIndex
    order by Unit.trs DESC

    Here the syntax error can be seen clearly.

  8. #8
    Join Date
    Sep 2003
    Posts
    65
    Great,

    Yea, thanks a lot, that solved the problem.

    It is all working now, sweet!

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