[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

query on rownum and rowid

Posted on 2010-08-18
1
Medium Priority
?
1,469 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Suggested Courses
Course of the Month9 days, 19 hours left to enroll

591 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