Solved

query on rownum and rowid

Posted on 2010-08-18
1
1,421 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

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

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

630 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