Solved

query on rownum and rowid

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dumping data from DB2 database 4 419
SQL0338 Error received on query 3 290
OPENQUERY syntax errors (not recognizing CROSS APPLY) 17 584
Connect SQL Developer to zO/S DB2 database 6 227
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 (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…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

25 Experts available now in Live!

Get 1:1 Help Now