Alpha Video Training
Results 1 to 4 of 4

Thread: How can I return a rank column for records in a list

  1. #1
    Member
    Real Name
    Richard
    Join Date
    Mar 2012
    Posts
    350

    Default How can I return a rank column for records in a list

    I need to use a custom sql statement as my data source for a list control.

    The records need to be ranked, 1, 2, 3 or if records have the same value the rank would be 1, 1, 3 for eg. (Rank is not the same as row number).

    My db is SQL Svr and I'm trying to use SQL Svr syntax which provides the Rank keyword/syntax.

    However, Alpha doesn't like the OVER keyword as in this eg.

    SELECT
    RANK() OVER(ORDER BY dbo.Results.CompetitionResult DESC) AS Ranking,
    dbo.Results.CompetitionID, dbo.Results.CompetitionResult
    FROM dbo.Results
    WHERE CompetitionID = :COMPID

    I cannot do the ranking in an SQL Svr view because my list is filtered and therefore the ranking has gaps being the records that Alpha filters out.

    Any help on how to achieve a ranking for records in a list is greatly appreciated.

  2. #2
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,551

    Default Re: How can I return a rank column for records in a list

    If you turn off the List property "Portable SQL" (in the Data Source tab) then it should work for you... it does here. I got the same error as you... turned off Portable SQL... and then all was ok.

    I seem to be running into more Alpha portable sql errors these days. It seems Alpha's implementation of portable sql is very basic and only covers simple sql statements.

  3. #3
    Member
    Real Name
    Richard
    Join Date
    Mar 2012
    Posts
    350

    Default Re: How can I return a rank column for records in a list

    Thanks David,

    After that I also found that I couldn't preview the data in the sql builder as Alpha couldn't find the arg even though it was defined. Seems to work when I run the UX though so all good.

  4. #4
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,551

    Default Re: How can I return a rank column for records in a list

    Right... Alpha can't parse the sql statement... so you're left without the builder. I usually take out the sql stuff Alpha can't handle if I need to change anything big... then put it back in. Otherwise I'll test the statement in Navicat or SSMS and then paste it into the List control. Glad it worked out.

Similar Threads

  1. How do I use a list return value as a filter for a second list
    By Ronald Anusiewicz in forum Mobile & Browser Applications
    Replies: 5
    Last Post: 08-24-2015, 09:04 PM
  2. Replies: 1
    Last Post: 10-05-2013, 01:52 PM
  3. How to rank subsets of records in a table?
    By pulsoft in forum Alpha Five Version 11 - Desktop Applications
    Replies: 12
    Last Post: 08-23-2012, 06:17 AM
  4. Method to rank records?
    By cellboy in forum Alpha Five Version 7
    Replies: 3
    Last Post: 12-30-2005, 04:46 AM
  5. Return number of records in a range?
    By Rick Gerlach in forum Alpha Five Version 5
    Replies: 7
    Last Post: 05-27-2004, 01:22 PM

Bookmarks

Posting Permissions

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