Hi,
On Jan 3, 2008 9:28 AM, GF <[EMAIL PROTECTED]> wrote:
> I need to create a view, about a ranking.
> The select from which I generate the view has a "ORDER BY" and I need
> to have a column in that select that shows the position of the object
> in that ranking.
>
> I have searched on google, and I have found that it's possibile to do
> it using the SET command and using variables.. but I don't think in a
> VIEW I can use variables and SET.
Correct. You can use ordinary SQL, like this:
create table fruits (
type varchar(10) not null,
variety varchar(20) not null,
primary key(type, variety));
insert into fruits values
('apple', 'gala'),
('apple', 'fuji'),
('apple', 'limbertwig'),
('orange', 'valencia'),
('orange', 'navel'),
('pear', 'bradford'),
('pear', 'bartlett'),
('cherry', 'bing'),
('cherry', 'chelan');
select l.type, l.variety, count(*) as num
from fruits as l
left outer join fruits as r
on l.type = r.type
and l.variety >= r.variety
group by l.type, l.variety;
+--------+------------+-----+
| type | variety | num |
+--------+------------+-----+
| apple | fuji | 1 |
| apple | gala | 2 |
| apple | limbertwig | 3 |
| cherry | bing | 1 |
| cherry | chelan | 2 |
| orange | navel | 1 |
| orange | valencia | 2 |
| pear | bartlett | 1 |
| pear | bradford | 2 |
+--------+------------+-----+
It is not very efficient on large data sets, though. What about a
stored procedure, or a UDF (a C UDF, not a SQL stored function)? Can
you use either of those?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
