Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    May 2005
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile Select Top (help)

    Hi friends,

    Please, how I do the following statement dynamically for any value of 'choice' and for all records?

    The examp;le:
    SELECT TOP 2 * FROM tableyy WHERE choice = 2 GROUP BY choice, freq,key_b ORDER BY freq DESC

    SELECT TOP 3 * FROM tableyy WHERE choice = 3 GROUP BY choice, freq,key_b ORDER BY freq DESC

    SELECT TOP 4 * FROM tableyy WHERE choice = 4 GROUP BY choice, freq,key_b ORDER BY freq DESC


    Thanks for any assistance

    janLee

  • #2
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Do we have SELECT TOP ??? Correct me if i'm wrong.
    What you can do is create a for loop and get the first 2 records, "manually" select

  • #3
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,172
    Thanks
    19
    Thanked 65 Times in 64 Posts
    Quote Originally Posted by PremiumHost
    Do we have SELECT TOP ??? Correct me if i'm wrong.
    yes

    not sure quite what you mean Jan, but I'm assuming the option value comes from a form so it would be something like
    Code:
    strSQL = "SELECT TOP "&request.form("choice")&" * FROM tableyy WHERE choice = "&request.form("choice")&" GROUP BY choice, freq,key_b ORDER BY freq DESC"

  • #4
    Regular Coder
    Join Date
    May 2005
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Soorry, sorry, it is a question regarding Visual FoxPro!

    JanLee

  • #5
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,065
    Thanks
    4
    Thanked 8 Times in 8 Posts
    Quote Originally Posted by PremiumHost
    Do we have SELECT TOP ??? Correct me if i'm wrong.
    What you can do is create a for loop and get the first 2 records, "manually" select
    1) Yes there is a keyword TOP for use in SQL statements to get the top records.

    2) it is much faster to get the results from sql than it is to get all records and loop through them.


    assuming the value is gathered through a form item named choice
    Code:
    Dim x : x = request.form("choice")
    If IsNumeric(x) Then   'makes sure it is a number that is passed
          "SELECT TOP " & x & " * FROM tableyy WHERE choice = " & x & " GROUP BY choice, freq,key_b ORDER BY freq DESC;"
    End If

  • #6
    Regular Coder
    Join Date
    May 2005
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Miranda,

    Thank you.

    JLee


  •  

    Posting Permissions

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