hello,
i have a really confusing problem, and i cant seem to get the recordset i want no matter what SELECT i try.

I have an airline flight database. What i want to do is pull the cheapest price for a particular city for each day of the year.

im thinking this will requiere a correlated subquery but im not sure.

any suggestions?

thanks in advance,
Jonah

Using MS-SQL, you could use:
-PatP

i guess i didnt explain myself right.
What i want to pull is the cheapest flight for ALL dates in a month (since i am binding this to a price schedule calendar)
So if the cheapest flight to London on april 28th is a 3 month fixed ticket for 600 dollars with American Airlines, it will return those values, but if the cheapest price to the same city on april 29th is a 6 month open ticket with United Airlines for 700 dollars, then i want those values for that date.

Its very complicated i know, but do you see what i mean?

Nope, I'm a lot more confused than when I started!

Are you looking for the best price on a given class of ticket for a given month? So for every month you'd have a walk-up price, a 14 day price, a 30 day price, a 90 day price, etc? How do you want to handle ties (where two airlines offer exactly the same price on a given ticket)?

Without worrying about the code, can you explain in English what you want to show, or better yet post a fabricated example set of data with the results that you'd like to see? If you can give data, keep in mind that columns are really tough to do on web pages, so commas usually make good separators!

-PatP

This question reminds me of a very similar question asked about 2 months ago.... is this a class/test question or something??

can you give us a description of the table(s) please?

And rephrase your question.

and WHERE did our avatars go??

~Le

i sincerely apologize for the confusion. here is some sample data and what i want:

city airline date price type

Tokyo UA 2004/04/01 $600 1monthFIX
Tokyo JL 2004/04/01 $600 1monthFIX
Tokyo NW 2004/04/01 $480 21daysFIX
Tokyo UA 2004/04/02 $550 1monthFIX
Tokyo JL 2004/04/02 $600 1monthFIX
Tokyo NW 2004/04/02 $580 21daysFIX
Tokyo UA 2004/04/03 $500 1monthFIX
Tokyo JL 2004/04/03 $500 1monthFIX
Tokyo NW 2004/04/03 $500 21daysFIX

So i need to produce something where the 1st of april will return:
Tokyo NW 2004/04/01 $480 21daysFIX

The 2nd of april will return:
Tokyo UA 2004/04/02 $550 1monthFIX

And the 3rd of April will return any 1 of the 3 records (doesnt matter which).

this is what i tried, but it didnt work for airlines that had more than one flight on the same day.

"SELECT DISTINCT date, city, airline, Min(price) AS minprice FROM airprices WHERE airline = (SELECT TOP 1 airline FROM airprices WHERE city = '" & request.QueryString("city") & "' AND price = (SELECT Min(price) FROM airprices WHERE cityID = '" & request.QueryString("city") & "')) AND city = '" & request.QueryString("city") & "' GROUP BY date, city, airline "

This is hideously ugly, but it works. I need to think through a better solution, but for now I'd start with:-PatP

thats seems really complicated, i thought there would be an easier solution.
ok, so i just tried it and got a syntax error on the FROM clause. ill keep working on it.
thanks for your help so far,
Jonah

well, i couldnt figure it out, so i eneded up dropping the airline from the query altogether. this is working for me now:

"SELECT DISTINCT B.date, B.city, (SELECT Min(price) FROM airprices A WHERE city = '" & request.querystring("city") & "' AND A.date = B.date) AS minprice FROM airprices B WHERE city = '" & request.querystring("city") & "'"

although this wont return the airline, it will return the cheapest price of all the flights for each individual date in a month. then if i want to get the airline i just query the database using the date, price, and city for a particular flight and do a Min(airline) on it.