Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

query on rownum and rowid

Posted on 2010-08-18
1
Medium Priority
?
1,457 Views
Last Modified: 2012-05-10
Can anyone please advise on following questions-
-what is equivalent of rowid and rownum as we have in oracle
-what is a sql to get employee names who has second highest salary by department from employee table
-How can we delete duplicate rows from a table which has 5 columns and none of them is a primary key
-how the database execute following sql at back end- select min(price),productid from product where sale='CA' group by productid
0
Comment
Question by:sunilbains
1 Comment
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 33470963
depends on your version of db2
pre 9.7 - no equivalent, you can generate a row number using the row_number function, for example
select *
 from (select *, row_number() over(order by col1, col2) rownum from your_table ) t
where rownum < 100


select * from
 (select *, row_number() over(partition by department order by salary desc) rown from yourTable) t
where rown = 2

in order to delete you will have to copy to a new table a distinct of the records that has duplicates, delete all duplicates and copy back, for example

insert into temp_table
select col1,col2,col3,col4,col5
from yourTable
group by col1,col2,col3,col4,col5
having count(*) > 1

delete from yourTable t
where 1 < (select count(*) from yourTable where col1 = t.col1 and col2 = t.col2 and col3 = t.col3 and col4=t.col4 and col5=t.col5)

insert into yourTable select * from temp_table


if depends, if there is an index on productId, price it just probes every product and gets the min price,
otherwise it needs to sort by productId, price
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question