Sure, if you're selecting them from a database you can just use an order by clause:

SELECT * FROM report ORDER BY date ASC;

This kind of processing should always be done on the database side, trying to do it on the script side is just plain bad design; your script code should be glue code, not processing code. If you find you are writing a loop to search for something or calculate something in the result set you are probably doing things wrong. What you are doing is the equivalent of "Mr Parker, I need the file on the Johnson case, would you please bring me the entire filling cabinet so I can get it".

For example:

SELECT
DATEDIFF(d, MIN(DateField), MAX(DateField)) AS TimeSpan
FROM TheTable

Will return the number of days between the first entry and the last entry, in days. Refer to the documentation for the identifier to use in place of "d" if you want the time difference in another format (hours, seconds, years, quarters, etc)

You can use the ubound function.

Your description is incomplete. Please provide some sort of example (such as some table layouts and an example of the results) so that I can actually understand what you want. As I do not know your level of understanding I have no way of knowing if the problem has some complexity you are not mentioning, or if you simply do not know enough basic SQL. From your second description it sounds like a simple use of MIN/MAX and GROUP BY to return a set of records, each record having the min and max date values of a related table.

Quote:Original post by chbrules
I'm dealing with tradeshows for this company, they have mutliple days each can go on for with different times each day. So the table has a Tradeshow_ID column which groups the dates, then there is a Start_Date column, and 2 more with the start and end times which I just need to read once I sort by the tradeshow and start and end dates.

Since there is only one date column, I need to get the list of dates for each tradeshow and determine which is the first and which is the last dates of the show. Then I take that data and print it to the page for the people to use in their reports.

I understand the sort by SQL and I'm pretty good w/ SQL. We're using MS SQL/ASP.NET here. I just need to know, if I did a SORT BY Tradeshow_ID, then how would I tell how large the returned array size is so I can grab the latest date? Is there another way to get the first and last elements of that array? That's all I need. I don't now what that min/max function is as I'm not an ASP guru or anything. I wish Microsoft did things more like C. =/

Where to begin?

MIN/MAX have nothing at all to do with ASP. They are SQL keywords - Microsoft had absolutely nothing to do with them, they date back to 1976 in the original SEQUEL research paper and have been in SQL from the very beginning (which was at IBM). Any SQL book you find will use MIN/MAX as early examples as they are the most common aggregate functions in the entire SQL language.

SQL is not going to work like C. SQL is the exact opposite of C - C is a procedural language, you tell the computer the steps you want it to take and the computer has not idea what the outcome will be; SQL is a functional language, you tell the computer what you want the outcome to be, and it determines the best way to reach it. As long as you try to think in C terms you will never succeed.

I assume you are using ADO in ASP (though all other methods work in a similar way). There is no "array" that records are stored in. Imagine you had a 20GB database, and you did a query that returned all of the records in that database. Would you expect a huge array to be constructed to hold all that? The records are streamed from the server as needed. Please learn about cursor and lock types (you will again utterly fail if you don't learn about these), then optionally browse the ADO documentation about page sizing and caching. The ADODB.RecordSet object is NOT an array. In theory you could use MoveLast, but this is counterproductive, as you are again asking for the filing cabinet when you only need one file.


Based on your still vague description I'm guessing your database looks something like this:

TradeShows(TradeShowID, Name)
1, 'Chicago Show'
2, 'NewYork Show'

TradeShowDays(TradeShowID, ShowDate, StartTime, EndTime)
1, 9/17/2005, 9AM, 5PM
1, 9/18/2005, 12PM, 5PM
1, 9/19/2005, 9AM, 9PM
2, 9/18/2005, 9AM, 5PM
2, 9/19/2005, 9AM, 5PM
2, 9/20/2005, 9AM, 5PM

And the result you want is this:

'Chicago Show', 9/17/2005, 9/19/2005
'NewYork Show', 9/18/2005, 9/20/2005

To get this you would run the following query:

SELECT Name, Min(ShowDate) AS ShowStartDate, Max(ShowDate) AS ShowEndDate
FROM TradeShowDays INNER JOIN TradeShows ON TradeShowDays.TradeShowID = TradeShows.TradeShowID
GROUP BY TradeShowDays.TradeShowID, Name