Professional Web Applications Themes

Highest Distinct Value - Coldfusion Database Access

Here's my question: I need to find out which distinct value appears most number of times. I have a columm (blog_date) with several dates, some of them repeating, what I needed was a query that would tell me the peak day of a certain occurrence, meaning the 'blog_date' value with the highest number of rows. I was already able to get the number of times each distinct value appears, by using this query: SELECT blog_date, count(*) AS number_of_times FROM main WHERE topic_id=#URL.topic_id# GROUP BY blog_date and then this table shows exactly the results: <cfoutput query='blogsperday'> <tr><td>#LSDateFormat(blog_date, 'DD MMM YY')#</td><td>#number_of_times#</td></tr> </cfoutput> ...

  1. #1

    Default highest distinct value

    Here's my question: I need to find out which distinct value appears most
    number of times. I have a columm (blog_date) with several dates, some of them
    repeating, what I needed was a query that would tell me the peak day of a
    certain occurrence, meaning the 'blog_date' value with the highest number of
    rows. I was already able to get the number of times each distinct value
    appears, by using this query: SELECT blog_date, count(*) AS number_of_times
    FROM main WHERE topic_id=#URL.topic_id# GROUP BY blog_date and then this
    table shows exactly the results: <cfoutput query='blogsperday'>
    <tr><td>#LSDateFormat(blog_date, 'DD MMM
    YY')#</td><td>#number_of_times#</td></tr> </cfoutput> However, I need to know
    dinamically what's the 'blog_date' that appears most number of times. Does
    anyone know how can I do this? Thanks a lot -- manuel

    mslima Guest

  2. #2

    Default Re: highest distinct value

    This is a SWAG, but something like this might work for you. Keep in mind that
    you will only get one row returned even if you have multiple dates with the
    same count as the MAX count.

    Phil



    SELECT TOP 1 blog_date, count(*) AS number_of_times
    FROM main
    WHERE topic_id=#URL.topic_id#
    GROUP BY blog_date
    ORDER BY COUNT(*) DESC

    paross1 Guest

  3. #3

    Default Highest Distinct Value

    Here's my question: I need to find out which distinct value appears most
    number of times. I have a columm (blog_date) with several dates, some of them
    repeating, what I needed was a query that would tell me the peak day of a
    certain occurrence, meaning the 'blog_date' value with the highest number of
    rows. I was already able to get the number of times each distinct value
    appears, by using this query: SELECT blog_date, count(*) AS number_of_times
    FROM main WHERE topic_id=#URL.topic_id# GROUP BY blog_date and then this
    table shows exactly the results: <cfoutput query='blogsperday'>
    <tr><td>#LSDateFormat(blog_date, 'DD MMM
    YY')#</td><td>#number_of_times#</td></tr> </cfoutput> However, I need to know
    dinamically what's the 'blog_date' that appears most number of times. Does
    anyone know how can I do this? Thanks a lot -- manuel

    mslima Guest

  4. #4

    Default Re: highest distinct value

    It worked great!!

    Thanks a lot

    best,

    --
    manuel
    mslima Guest

  5. #5

    Default Re: Highest Distinct Value

    Answered in this post: [url]http://www.macromedia.com/cfusion/webforums/forum/messageview.cfm?catid=3&threadid=990559&enterthrea d=y[/url]
    paross1 Guest

Similar Threads

  1. what's the highest version of FH10?
    By phorest in forum Macromedia Freehand
    Replies: 0
    Last Post: July 26th, 10:38 PM
  2. Invest 6$ 1 time get HIGHEST Output!!!
    By malik in forum ASP.NET Building Controls
    Replies: 0
    Last Post: February 19th, 08:32 PM
  3. HIGHEST WARNING !!! OS X 10.2.6 CORRUPTS ALL HARDDISKS UNDER OS9
    By Kay Belik in forum Macromedia Director Lingo
    Replies: 5
    Last Post: September 15th, 02:11 PM
  4. Replies: 3
    Last Post: April 18th, 12:52 PM
  5. Replies: 0
    Last Post: April 15th, 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
  •  

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