I always use stored procedure when I meet such demand.
On Jan 3, 2008 11:09 PM, Baron Schwartz <[EMAIL PROTECTED]> wrote:
> 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]
>
>
--
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
