Post reply

Possible to calculate a position?

  • June 19, 2009 at 5:14 am

    #132013

    Hi All,

    I've read the newsletter for a long time and I remember seeing a fuction that might help me but I can't remember what it is called.

    I have exam marks and I need to get a position;

    i.e.

    person1 99 1

    person2 88 2

    person3 88 2

    person4 77 4

    ...

    and so on - As I say, I remember reading about the statistical functions that generate things like this, but for the life of me can't remember what they are called - or find them - searching for position I get the fucntion thats searches strings 😀

    Any way, Thanks in advance,

    Scott

  • June 19, 2009 at 5:20 am

    #1012277

    You can use substring, Charindex

    blog: http://sarveshsingh.comTwitter: @sarveshsing

  • June 19, 2009 at 5:22 am

    #1012278

    Which version of SQL server you are using?

    You might be looking for rank() or row_number function

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 19, 2009 at 5:23 am

    #1012280

    I dont think that will work - allow an example with more realistic data

    Name Mark Position

    Smith 76 1

    Bloggs 56 2

    John 56 2

    Rich 33 4

    Mac 22 5

    I am trying to calculate the Position column,

    Thanks for the reply 😉

    Scott

  • June 19, 2009 at 5:24 am

    #1012281

    Ah sorry about that - replies flying in!

    I'll have a look and see if Rank() is what I need - sounds promising tho!

  • June 19, 2009 at 5:49 am

    #1012291

    misunderstood ur question, rank() ,row_number() will work better in this case.

    blog: http://sarveshsingh.comTwitter: @sarveshsing

  • June 19, 2009 at 6:29 am

    #1012328

    scott macleman (6/19/2009)

    I dont think that will work - allow an example with more realistic data

    Name Mark Position

    Smith 76 1

    Bloggs 56 2

    John 56 2

    Rich 33 4

    Mac 22 5

    I am trying to calculate the Position column,

    Thanks for the reply 😉

    Scott

    Hmmm... I'm not sure that this is what you are looking but why not to try:

    CREATE TABLE #SSC

    (

    ID INT IDENTITY(1,1),

    PERSON NVARCHAR(30),

    POINTS INT,

    POSITIONS INT

    );

    GO

    INSERT INTO #SSC

    SELECT 'person1', 76, 1 UNION ALL

    SELECT 'person2', 56, 2 UNION ALL

    SELECT 'person3', 56, 2 UNION ALL

    SELECT 'person4', 33, 4 UNION ALL

    SELECT 'person5', 22, 5;

    GO

    SELECT * FROM #SSC

    --AND THE SAMPLE OF YOUR RESULTS SHOULD BE

    SELECT ROW_NUMBER() OVER (ORDER BY POINTS DESC), PERSON, POINTS FROM #SSC

    If it isn't what are you looking, please post the structure of your table and sample data and the result set that you want to see!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • June 19, 2009 at 7:49 am

    #1012396

    Thanks for all the replies, the function I was looking for was RANK() it worked perfectly!

    Cheers,

    Scott

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply