Solved

query on rownum and rowid

Posted on 2010-08-18
1
1,345 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 500 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now