ASP SQL Select in a Select issue

• chris.pols

  SSC Enthusiast

  Points: 161

  More actions

  August 4, 2009 at 2:27 am

  #207536

  Hi all,

  I've seen this able to work on MySQL, but need it working on my ASP/MSSQL site.

  Here's the code:

  Code:

  SQL = "SELECT TOP 10 differential FROM (SELECT TOP 20 * FROM gamedetails where username = '"&request.Form("Username")&"' ORDER BY DATE_Played DESC) WHERE username = '"&request.Form("Username")&"' order by differential ASC"

  My idea is to get the last 20 records posted, and then select the best 10 from those 20 to get to a calculation i've got running.

  I think i'm close to getting it right, but i'm missing something.

  Any ideas?

  Thanks

  Chris

• chris.pols

  SSC Enthusiast

  Points: 161

  More actions

  August 4, 2009 at 3:48 am

  #1034149

  Hi,

  Got the problem fixed. Apparently need to post the question to get the juices flowing.

  SQL = "SELECT differential FROM (SELECT TOP 10 differential FROM (SELECT TOP 20 differential FROM gamedetails WHERE username = '"&request.Form("Username")&"' ORDER BY date_played DESC) AS XXX ORDER BY differential) AS YYY"

  Works a treat.

  C

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  August 4, 2009 at 8:22 pm

  #1034588

  Glad we could help.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• chris.pols

  SSC Enthusiast

  Points: 161

  More actions

  August 5, 2009 at 1:26 am

  #1034639

  Got some more efficient code for an FYI:

  SQL = "SELECT TOP 10 X.differential FROM ( " _

  & " SELECT TOP 20 differential FROM gamedetails " _

  & " WHERE username = '" & request.Form("Username") & "' " _

  & " ORDER BY DATE_Played DESC ) AS X " _

  & "ORDER BY X.differential ASC"

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  August 5, 2009 at 11:20 am

  #1034976

  chris.pols (8/5/2009)

  ---

  Got some more efficient code for an FYI:

  SQL = "SELECT TOP 10 X.differential FROM ( " _

  & " SELECT TOP 20 differential FROM gamedetails " _

  & " WHERE username = '" & request.Form("Username") & "' " _

  & " ORDER BY DATE_Played DESC ) AS X " _

  & "ORDER BY X.differential ASC"

  FYI: you probably want to parametize that because right now it is very susceptible to SQL Injection attacks.

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]