CODEWITHSUNDEEP
sql
Shyam
Shyam

Reputation: 23

generate rownum in select statement without using db specific functions

I want to get the rownumbers in sql select statement but it shouldn't be DB specific query like I cant use rownum of oracle.Please let me know how can i achieve this.
I have table structure as follows pid,emplid,desc as colums and pid and emplid combination will be used as primary key. So suggest the query in this use case.

Thanks,
Shyam

Upvotes: 1

Views: 414

Answers (2)

valex
valex

Reputation: 24144

To do it you table has to have an unique Id- like field - anything to distinguish one row from another. If it is then:

select t1.*,
(select count(id) from t as t2 where t2.id<=t1.id) as row_number

from t as t1 order by Id

UPD: if you have 2 columns to make an order then it will look like:

select t1.*,
(select count(id) from t as t2 where t2.id1<=t1.id1 and t2.id2<=t1.id2) 
as row_number

from t as t1 order by Id1,id2

Upvotes: 0

Chris Cameron-Mills
Chris Cameron-Mills

Reputation: 4657

The row_number() function is supported on a lot of the major RDBMS but I don't believe it's in MySQL so it really depends how agnostic you want it to be. Might be best to move it out of the database layer if you want it truly agnostic.

EDIT: valex's method of calculating rownum is probably a better option than moving it out of DB

Upvotes: 1

Related Questions