Professional Web Applications Themes

unique values in ValueList() - Coldfusion - Advanced Techniques

I am using the ValueList() function to return a comma-separated list of values from a specific column in the results of a query. My query returns about 300 records, but there are only two distinct values (6 and 7) in that column. My ValueList() returns a string of 6s and 7s. Everything I use after that based on the ValueList works, but having the long string of 6s and 7s just doesn't seem right. Is there a way to get only unique values, so that 6 and 7 appear in the list just once? Thanks. --diane...

  1. #1

    Default unique values in ValueList()

    I am using the ValueList() function to return a comma-separated list of values
    from a specific column in the results of a query.

    My query returns about 300 records, but there are only two distinct values (6
    and 7) in that column. My ValueList() returns a string of 6s and 7s.

    Everything I use after that based on the ValueList works, but having the long
    string of 6s and 7s just doesn't seem right. Is there a way to get only unique
    values, so that 6 and 7 appear in the list just once?

    Thanks. --diane

    Dbast Guest

  2. #2

    Default Re: unique values in ValueList()

    Do a Q of Q that starts with select distinct, and turn that result into a value
    list.

    Originally posted by: Dbast
    I am using the ValueList() function to return a comma-separated list of values
    from a specific column in the results of a query.

    My query returns about 300 records, but there are only two distinct values (6
    and 7) in that column. My ValueList() returns a string of 6s and 7s.

    Everything I use after that based on the ValueList works, but having the long
    string of 6s and 7s just doesn't seem right. Is there a way to get only unique
    values, so that 6 and 7 appear in the list just once?

    Thanks. --diane



    Dan Guest

  3. #3

    Default Re: unique values in ValueList()

    Dan: Perfect! Thanks so much for the speedy answer. --diane
    Dbast Guest

  4. #4

    Default Re: unique values in ValueList()

    Dan: Thought I'd ask this question directly of you, since it follows up the one
    you just answered for me.

    One of the lists returns the distinct values perfectly.

    But the other returns the list of values with a leading comma
    (,39,40,41,42,43,44,45,46,48,49,53,503,599), which the rest of the file doesn't
    like.

    Any idea why it would do this?

    The code looks like this:

    <CFQUERY name="getSubTopics" dbtype="query">
    SELECT DISTINCT ArtSubTopic
    FROM getResearch
    </CFQUERY>
    <CFSET VARIABLES.SubTopicList = ValueList(getSubTopics.ArtSubTopic)>

    Dbast Guest

  5. #5

    Default Re: unique values in ValueList()

    Sorry all, my last post on this topic.

    I figured out that I had a NULL value in the list, so I tweaked the
    getResearch query to specify WHERE ArtSubTopic IS NOT Null.

    That did it.

    Appreciate everyone's patience. --diane

    Dbast Guest

Similar Threads

  1. Query selecting unique values
    By RuBot in forum Macromedia ColdFusion
    Replies: 3
    Last Post: August 22nd, 05:52 PM
  2. get_meta_tags expects unique name values
    By Peter N. Schweitzer in forum PHP Development
    Replies: 0
    Last Post: October 30th, 09:37 PM
  3. unique constraints with null values
    By belgie in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 13th, 06:25 PM
  4. Best method for obtaining unique values?
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: August 8th, 01:12 PM
  5. Returning multiple values for unique key
    By noon in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 3rd, 03:35 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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139