> Is there is any other possible way to find out rank? As I said you can do it in your application, it will be a whole lot faster than doing it with sql. Just select all your data with 'order by deptno, sal desc'. Then during iteration over result set assign rank 1 to the person when you first see his deptno, each next row is next rank (think how you want to deal with repeated salary). And that's it.
Pavel On Thu, Jan 14, 2010 at 7:07 AM, Jigar Shah <sh...@nse.co.in> wrote: > First of all thanks Tim and Pavel for replying to my query. > > >> >> Assuming you're doing this by department, try to get just a list of the >> distinct salary rankings into an inline view, using your count(*) +1 >> approach to set the salary rank, where these three columns are unique >> in combination: >> >> (dept, salary, rank ) as S >> >> select EE.dept, EE.name, EE.salary, S.rank >> from employees as EE >> JOIN >> (inline view to create distinct salary bands by department goes here) as > S >> on EE.dept = S.dept >> and EE.salary = S.salary >> order by dept, rank > > > My aim is to find rank of each employee within his dept. So I guess the > inline view (adding empname and empno columns) is the final output that > I require. > > So to join this output with original table would be an extra activity. > > Also, count(*) +1 approach for ranking is time consuming as it iterates > over > entire table for each row, so using the same in inline view may not help > to > increase speed. > > >> > I have used following query but it takes more than one hour even > after >> > indexing, >> >> Shouldn't be - query is not so hard if a proper index used. What index >> did you create? > > I have used following index. > > create index tempidx on EMPLOYEES(DEPTNO, SAL); > > I have tried query, using count(*) +1 approach, in mysql but it was slower > (>two hours, still query was running) than Sqlite. > > > Is there is any other possible way to find out rank? > > > > Thanks, > > Jigar Shah > > > > Disclaimer note on content of this message including enclosure(s)and > attachments(s): The contents of this e-mail are the privileged and > confidential material of National Stock Exchange of India Limited > (NSE). The information is solely intended for the individual/entity > it is addressed to. If you are not the intended recipient of this > message, please be aware that you are not authorized in any which > way whatsoever to read, forward, print, retain, copy or disseminate > this message or any part of it. If you have received this e-mail in > error, we would request you to please notify the sender immediately > by return e-mail and delete it from your computer. This e-mail > message including attachment(s), if any, is believed to be free of > any virus and NSE is not responsible for any loss or damage arising > in any way from its use. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
