Jigar, If these two employees have the same rank:
dept, employee, salary 10, Joe, 75000 10, Mary, 75000 then you do not need to include employee columns in the inline view. The salary gets the rank, not the employee, and the employee record is joined to the ranked salary on salary. Tim ________________________________ From: Jigar Shah <[email protected]> To: [email protected] Sent: Thu, January 14, 2010 7:07:38 AM Subject: Re: [sqlite] How to find Rank in SQLite3? 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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
