> 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? > mysql->select a.DEPTNO, a.EMPNO, a.LASTNAME, a.FIRSTNAME, a.SAL, > ... > -> order by x.DEPTNO, x.RANK; Interesting how you're talking about trying query in SQLite but command line is from MySQL and even query is incorrect because there's no table 'x' here... > 1. How to find Rank in SQLite? I believe your rewritten query is the best you can do here, of course if you have good index and don't want to calculate the rank ins the application. > 2. group_concat does not have order by clause, My data is already sorted, > but would output of group_concat be unsorted? It's undefined. If SQLite chooses to use an appropriate index so that data is processed in sorted way then result inside group_concat will just happen to be sorted. But if SQLite chooses to use some other index or to not use index at all then your data will be unsorted. > 3. How to implement find_in_set in SQLite? I believe with the answer on the 2nd question answer to this one is unnecessary, isn't it? Pavel On Wed, Jan 13, 2010 at 5:06 AM, Jigar Shah <[email protected]> wrote: > 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 > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
