[sqlite] How to find Rank in SQLite3?

[Jigar Shah]

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
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Pavel Ivanov]

> 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

[Tim Romano]

Would all employees with the same salary have the same rank within a
department?

On 1/13/2010 5:06 AM, Jigar Shah 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.
>
>

_______________________________________________

[Tim Romano]

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

and then :

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

If you make the innermost inline view (select distinct dept, salary....)
and then use an outer to set the salary-band rankings within department,
you will be working with far fewer than 200,000 rows, and the
salary-bank rankings-by-department inline view will probably be held in
a transient table and used as the inner loop. I would put an index on
dept and salary in the EEs table.

Regards
Tim Romano

[Tim Romano]

In my last post, I wrote "salary bands" but it would be clearer to say
"salary tiers".

Dept, salary, rank

1, 75000, 1
1, 50000, 2
1, 49000, 3
2, 70000, 1
2, 68000, 2
3, 52000, 1

Tim

[Jigar Shah]

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.

[Pavel Ivanov]

> 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

[Tim Romano]

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 <sh...@nse.co.in>
To: sqlite...@sqlite.org
Sent: Thu, January 14, 2010 7:07:38 AM
Subject: Re: [sqlite] How to find Rank in SQLite3?

[Tim Romano]

Not saying this will be faster for sure, but it's worth a try. Try a
composite index on (dept, salary). The goal is to rank distinct salaries
within department, and have those salary rankings be used as the inner
loop when the EEs table is joined to the ranking on the salary column.
The assumption is that there are far fewer distinct salaries than
distinct employees.
Tim Romano

CREATE TABLE "EES" ("id" int PRIMARY KEY NOT NULL , "dept" int NOT NULL
, "salary" int NOT NULL , "empname" text NOT NULL )

select EES.dept, EES.empname, S3.rank from EES
JOIN
(
select s1.dept, s1.salary,
(select count(*) + 1 from
(select distinct dept, salary from EES) as S2 where S1.dept=s2.dept and
s1.salary < s2.salary) as rank
from
(select distinct dept, salary from EES) as S1

) as S3
ON S3.dept = EES.dept and S3.salary = EES.salary
order by EES.dept, rank

Explain Query Plan:
Order, from, detail
0,0, TABLE EES WITH INDEX EES_IX ORDER BY
0,1, TABLE AS S1
1,0, TABLE EES WITH INDEX EES_IX
0,0, TABLE EES WITH INDEX EES_IX ORDER BY
0,0,TABLE AS S2
0,0, TABLE EES WITH INDEX EES_IX ORDER BY
0,0, TABLE AS S2