I have to migrate from MySQL to SQLite3.
Following query in mysql gives the Rank for each employee within his dept
on the basis of salary.
mysql-> select e.DEPTNO, e.EMPNO, e.LASTNAME, e.FIRSTNAME, e.SAL,
find_in_set(e.SAL, x.SALLIST) RANK
-> from EMPLOYEES as e, (select DEPTNO, group_concat(SAL order by
SAL desc) SALLIST
-> from EMPLOYEES
-> group by DEPTNO) as x
-> where e.DEPTNO = x.DEPTNO
-> order by DEPTNO, RANK;
I have used following query but it takes more than one hour even after
indexing,
mysql->select a.DEPTNO, a.EMPNO, a.LASTNAME, a.FIRSTNAME, a.SAL,
-> (select 1 + count(*)
-> from EMPLOYEES b
-> where b.DEPTNO = a.DEPTNO
-> and b.SAL > a.SAL) RANK
-> from EMPLOYEES as a
-> order by x.DEPTNO, x.RANK;
My questions are
1. How to find Rank in SQLite?
2. group_concat does not have order by clause, My data is already sorted,
but would output of group_concat be unsorted?
3. How to implement find_in_set in SQLite?
Please note that my database contains more than two lakhs rows.
Regards,
shahj
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
