Mysql Help

May 23rd, 2008, 3:39 pm

Hello Ozzu members,,, I need your help on this mysql query.

I have a list of rates to different states and Products for specific dates, e.g.

ST Product Date Rate
----------------------

NY A 2008-15-01 25.00
NY B 2008-15-01 28.00
NY C 2008-15-01 30.00

TX A 2008-15-01 25.00
TX B 2008-15-01 28.00
TX C 2008-15-01 30.00

AL A 2008-15-01 25.00
AL B 2008-15-01 28.00
AL C 2008-15-01 30.00


I need a query to get the lowest rate for each unique city for an specific date.

E.g.
SELECT Product, Rate, Date, DISTINCT ST WHERE Date = '$somedate';

TO Output:
NY A 2008-15-01 25.00
TX A 2008-15-01 25.00
AL A 2008-15-01 25.00


Thank you....

[joebert]

May 23rd, 2008, 4:01 pm

Code: [ Download ] [ Select ]

SELECT *
FROM `table`
WHERE when = '2008-05-24 18:58:53'
GROUP BY place
ORDER BY cost ASC

1. SELECT *
2. FROM `table`
3. WHERE when = '2008-05-24 18:58:53'
4. GROUP BY place
5. ORDER BY cost ASC

[Bogey]

May 23rd, 2008, 4:29 pm

You can have two order by in the SQL query?

[joebert]

May 23rd, 2008, 8:11 pm

Bogey wrote:

You can have two order by in the SQL query?

I get the feeling you read something too quick, but yes, you can order by multiple columns.

May 26th, 2008, 12:09 pm

Thank you all, I will try that...

May 26th, 2008, 12:24 pm

Still have a problem here...

Query is actualy like this:

Code: [ Download ] [ Select ]

$query_deals = sprintf("SELECT * FROM rates WHERE date = %s GROUP BY city ORDER BY rate ASC", GetSQLValueString($colname_deals, "date"));

I do get a list of the lowest rates, but for some reazon the last result on the list is different

B Mexico DF $10 USD
B Mazatlan $12 USD
B Tijuana $13 USD
B Guadalajara $16 USD
B La Paz $24 USD
B Puerto Vallarta $24 USD
B Monterrey $100 USD
U Los Cabos $129 USD (?????) This should be a "B" and have the lowest rate..... But for some reazon is pulling the highest value and the last type of product (U).



Any clues???

Thank you again...

[joebert]

May 26th, 2008, 12:37 pm

Code: [ Download ] [ Select ]

WHERE date = %s AND product = %s

August 14th, 2008, 4:41 am

SELECT *
FROM `table_name`
WHERE Date = '2008-05-24 18:58:53'
GROUP BY ST
ORDER BY Rate ASC