ASP/SQL/database: random record among TOP 100?

I'm using ASP and a CSV file for my database.

It has 4 colums: date, time, name, message

I want to sort the records by date and time (I managed to do that), but then I don't know how I can pick one random record among the TOP 100 most recent.

Does anyone have an idea? Any help would be much appreciated.

 

if it were me I'd have an ID column as well, and simply generate a random number, using SQL statement to drag out the record with that random ID. Somthing like

Code:

"SELECT * FROM table_name WHERE id = " & id & " LIMIT 1"

But that might not be an option... you could try accessing it directly from a recordset but I'm so rusty with ASP I wouldn't know how! Oy KODO!

Some links about recordsets
http://www.shocknet.org.uk/defpage.asp?pageID=16
http://www.w3schools.com/ado/ado_ref_recordset.asp

 

The auto-incrementing id could be an option if only I had found someone to tell me how to do it with ASP and CSV files.
The only tutorials I have found were for Access databases.

This is why I now want to do it with dates.

 

Thing is, dates arn't unique values, so you can't pick out a unique record at random.

You could write a fairly simple script to read your CSV files into an Access database, with auto incrementing ID. Its quite a simple script to write, really.

However, thinking about it, if it was a CSV file, you could use the same method I used for random quotes. All you do is generate a random number, and round it (don't forget that), then loop a function that many times to skip a line, then read the data from the line that you're sitting at after the loop.

Not at all efficient, but it works.

 

pull the csv data out and manually create a recordest. Then you'll be able to access the recordset in memory like you would a database. Then make a random function (remember to call randomize) and use the array of Auto ID's as your values to randomize on.. from n-start to n-end. If a number doesn't exist, then run the function again. Don't have to go into any more.. gotta get back to work.

 

Thank you Goldfish and Kodo.
What you are both saying sounds very complicated to me. I'm really not a programmer.

I thought if I'd managed to gather the 100 records I'm interested in, it would be easy to pick one randomly.

For the date, I was thinking of ordering by year, then month, then day, then hour, then minute. Is it stupid? In the end each record would have a unique value, right?

About creating manually a recordset, I have to go back to my ASP book because I have no idea how to do that.

 

If you use the date you are not garunteed to have a unique value for every entry. With an ID, it would be completly impossible to have 2 identical IDs.

Read up about recordsets, if you input the CSV data into a recordset you would really simplify the problem. The only thing you'd need to worry about would be the random number and draging out the data depending on that random number.

 

I don't really get how the recordset object could help me. I'm currently trying to understand.
But it's not the recordset that could help me do this id column is it? How can I actually get this auto-incrementing id?

And if I had it, it would be easy to select the 100 highest ids but then, how would I pick one randomly? Because I can't know in advance how high the numbers will get, they could be really big numbers.

I'm really confused...

 

Manually creating a recordset (disconnected) http://www.4guysfromrolla.com/webtech/071799-1.2.shtml

Random numbers http://www.asp101.com/articles/stephen/randomnumbers/default.asp

This will allow you to create a recordset in memory and you should be able to sort it on a field if you wish. The important thing is that you'll be able to add records via a loop with sequential increments of ID's that you can use in your random function. Look up ADO recordsets to get more information on how to use this better.

IMO, I would do what you're doing in ASP.NET. It's quite a bit easier.

 

thanks for your help. I'm exploring a few solutions at the moment.
I can't use ASP.NET for this project. It seems much better though