Sesky
asked on
Subquery and Grouping in Sybase
It may best to describe the problem with an example:
Is it possible to query for the below data and group by fruit so that we have for each fruit, the number of times the price is quoted and the latest price (the data is sorted by time).
Table data is as below:
Fruit Price
apple $11
apple $12
apple $12.5
grape $10
grape $9
Result expected:
fruit -count -price
Apple 3 $12.5
grape 2 $9
Using: Sybase ASE, Embarcadero Rapid SQL is the software.
Thank you.
Is it possible to query for the below data and group by fruit so that we have for each fruit, the number of times the price is quoted and the latest price (the data is sorted by time).
Table data is as below:
Fruit Price
apple $11
apple $12
apple $12.5
grape $10
grape $9
Result expected:
fruit -count -price
Apple 3 $12.5
grape 2 $9
Using: Sybase ASE, Embarcadero Rapid SQL is the software.
Thank you.
select fruit , count(price) , sum (price) from table group by fruit
Almost. "sum" is wrong here.
However we can't just say something like
select fruit , count(price) , max(price) from table group by fruit
because we don't want the max price, we want the most recent price.
You say the data is sorted by time? Is there a time column, or do we just have to trust that it's in the right order?
However we can't just say something like
select fruit , count(price) , max(price) from table group by fruit
because we don't want the max price, we want the most recent price.
You say the data is sorted by time? Is there a time column, or do we just have to trust that it's in the right order?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.