Professional Web Applications Themes

Can't figure out my query - Macromedia ColdFusion

Database: id mydate cat 1 6/15/2005 r 2 6/24/2005 t 3 7/11/2005 r 4 7/4/2005 r 5 7/8/2005 r I need to be able to pull information from the record with the latest date, and that has an "r" as the cat. So in this case, it should find the 7/11/2005 date or id 3. I can then use the id to run a second query to get the other information (not shown) I just can't seem to figure out how to do this query - help please...

Sponsored Links
  1. #1

    Default Can't figure out my query

    Database:
    id mydate cat
    1 6/15/2005 r
    2 6/24/2005 t
    3 7/11/2005 r
    4 7/4/2005 r
    5 7/8/2005 r

    I need to be able to pull information from the record with the latest date,
    and that has an "r" as the cat. So in this case, it should find the 7/11/2005
    date or id 3.

    I can then use the id to run a second query to get the other information (not
    shown)

    I just can't seem to figure out how to do this query - help please

    Sponsored Links
    jkgiven Guest

  2. #2

    Default Re: Can't figure out my query

    Try something like this...

    SELECT TOP 1 id, mydate, category
    FROM test
    WHERE category="R"
    ORDER BY mydate DESC;

    Let me know if I can answer any questions.
    muscleman Guest

  3. #3

    Default Re: Can't figure out my query

    Three possibilities are


    SELECT ID
    FROM yourTable
    WHERE cat = 'r' AND
    myDate = (SELECT MAX(myDate)
    FROM yourTable
    WHERE cat = 'r')

    SELECT t.ID
    FROM yourTable t,
    ( SELECT MAX(myDate) AS myDate
    FROM yourTable
    WHERE cat = 'r') AS mt
    WHERE t.cat = 'r' AND
    t.myDate = mt.myDate

    SELECT TOP 1 ID
    FROM yourTable
    WHERE cat = 'r'
    ORDER BY myDate DESC


    mxstu Guest

  4. #4

    Default Re: Can't figure out my query

    I was actually pretty close.... BUT I'm wasn't familiar with the "TOP 1" code that you used - Thank you very much for your help
    jkgiven Guest

Similar Threads

  1. Cant figure this one out!
    By Mrcool1804 in forum Macromedia Flash Player
    Replies: 0
    Last Post: February 26th, 03:43 AM
  2. Can't figure this out
    By GerryLopez in forum Macromedia Flash Player
    Replies: 2
    Last Post: December 11th, 12:08 AM
  3. can't figure it out; help
    By Henri in forum Macromedia Flash
    Replies: 1
    Last Post: April 15th, 08:00 PM
  4. Trying to figure out CF and IIS
    By Eddie McHam in forum Coldfusion - Getting Started
    Replies: 6
    Last Post: March 29th, 05:03 AM
  5. Need to figure this one out
    By Bill Ray in forum Macromedia Fireworks
    Replies: 6
    Last Post: July 22nd, 07:51 AM

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