Skip to main content
T
tgruen
Inspiring
May 11, 2009
Answered

STUCK - Indexing a List and using the index as a variable

  • May 11, 2009
  • 3 replies
  • 558 views
Howdy everyone. I would imagine this is not a tough task but I cannot find information on exactly what I need to accomplish.

Let's say I have a table with fruit. Field names fruitid and fruitname.

I query select * from fruit order by fruitname. Then I take this data and create an list (or array?) with the fruitnames.

What I want to do is is list the results with their place in the list or array. Not their fruitid. For example:

1 Apple
2 Orange
3 Pear

Where the 1,2,3 is the order of the list and not any data retrieved form the query. I need to use the 1,2,3 as variables separately form the Apple,Orange,Pear.

I have been poking around in the online help, CFWACK and finding information on indexed lists is eluding me. I was looking at listgetat but the results will be different every time and it did not seem like the solution as ListGetAt returns the variable... for example:
Apple Apple
Orange Orange
Pear Pear

Any thoughts? Any help in a new direction is welcome. I am using CF7.01 on WinServer2003 and SQLServer2008.
    This topic has been closed for replies.
    Correct answer Dan_Bracuk

    Google "<cfquery>".  Find the page from the cfml reference manual and read it.  Pay particular attention to the variables are produced by cfquery.

    3 replies

    T
    tgruenAuthor
    Inspiring
    May 12, 2009

    A million thanks. I was off on a strange and unenjoyable tangent playing with lists and arrays. Like doing my own dentistry.

    I had a feeling I was missing the true and efficient solution. Thank you both for your guidance.

    Problem solved.

    D
    Dan_BracukCorrect answer
    Inspiring
    May 12, 2009

    Google "<cfquery>".  Find the page from the cfml reference manual and read it.  Pay particular attention to the variables are produced by cfquery.

    -
    -__cfSearching__-
    Inspiring
    May 12, 2009

    Dan Bracuk wrote

    Google "<cfquery>".  Find the page from the cfml reference manual and read it.  Pay particular attention to the variables are produced by cfquery.

    I am not really sure about the usage/context here. CurrentRow is probably sufficient, but in case it is not there is also row_number().

    -
    -__cfSearching__-
    Inspiring
    May 12, 2009

    I do not think you need a literal list.  Try using ms sql's row_number function.

    SELECT   

    ROW_NUMBER() OVER (ORDER BY FruitName ASC) AS FruitIndex,
    FruitID, FruitName
    FROM    YourTable

    ...

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices