database query with ASP

Discussion in 'C#' started by voidmain, Mar 19, 2008.

0
  1. #1
    Hi, I have a SQL server backend and i'm developing applications using ASP (classic). In one of the reports, I need to generate the TOP 10 records from a table based on a date Column, in DESC order.

    The problem is that When the date column contains the same date, and this date is included in the recordset, all the records belonging to this date is returned. But I want to return only 10 records, no matter what!

    Any help would be highly appreciated...

    Thanks,

    voidmain
     
    voidmain, Mar 19, 2008 IP
  2. SibTiger33

    SibTiger33 Peon

    Messages:
    203
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #2

    show me your current sql statements and your table structure for the tables referred to in the sql and ill create the sql for you
     
    SibTiger33, Mar 19, 2008 IP
  3. paul_delaney

    paul_delaney Peon

    Messages:
    45
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    you could using "SELECT TOP 10" and SET ROWCOUNT 10
     
    paul_delaney, Mar 25, 2008 IP
  4. JJnacy

    JJnacy Peon

    Messages:
    448
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    0
    #4
    example:
    <%
    SQL= "select top 10 * from table_name order by field_name DESC"
    %>
     
    JJnacy, Mar 30, 2008 IP
  5. InfoSmith

    InfoSmith Peon

    Messages:
    884
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #5
    maybe try put "distinct" in
     
    InfoSmith, Apr 6, 2008 IP
  6. TheCashCow25

    TheCashCow25 Guest

    Messages:
    11
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    You can do it in a number of different ways - here are two:
    1:
    Put a limit statement in your query, i.e.:

    Query = "Select * from table_name order by column_name desc limit 0,9"
    This will only return row 0 to 9 in the recordset.

    2:
    Alternatively you can fetch all the rows from the table with the following query:

    Query = "Select * from table_name order by column_name desc"
    Set RS = Conn.execute(Query)

    And limit the number of instances you want to display on the page:

    For i = 0 To 9
    If Not(RS.BOF OR RS.EOF) Then
    Response.Write RS("Column_Name") & "<BR>"
    Else
    Exit
    End If
    RS.MoveNext
    Next


    Both methods are equally useful. The first is faster and simpler, especially if you deal with large amounts of data. The second option is useful if you need to reuse the recordset on the same page without performing the query again.
     
    TheCashCow25, Apr 8, 2008 IP