database query with ASP

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

 

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

 

you could using "SELECT TOP 10" and SET ROWCOUNT 10

 

example:
<%
SQL= "select top 10 * from table_name order by field_name DESC"
%>

 

maybe try put "distinct" in

 

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.