Categories

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.

How to number your sql results

Have you ever had a requirement to number the results of a sql query?  There are some occassions wheer you want to identify the nth record of a result and it can sometimes be a tricky thing to do.  One of the reasons you might want to number the results is to create a mechanizm to produce pageable results.  For example, if you had an application that wants to have ‘pages’ where page one has the first 30 rows returned and page two has the second 30 rows and so on….. it could become onerous to have the application do all of this work.  It might be easier for the sql to just return the rows (or pages) you want as indicated by the app.  To do this you need to know what rows qualify and to do that you need to number them….

Here is the trick…..

using the IBM sample database, we can use the following sql to number the results….

WITH ORDERED_SET AS (SELECT
ROW_NUMBER() OVER (ORDER BY SALARY DESC) ROW_NUMBER,
FIRSTNME,
LASTNAME,
WORKDEPT,
JOB,
SALARY+BONUS+COMM TOTAL_SALARY
FROM ADMINISTRATOR.EMPLOYEE
ORDER BY SALARY DESC)

SELECT * FROM ORDERED_SET
WHERE ROW_NUMBER BETWEEN 1 AND 10

using the row_number() OLAP function, you can cause the resultset to be numbered and pick only the ones you want…..

as well, here is a useful link for OLAP functions in general http://www.ibm.com/developerworks/data/library/techarticle/dm-0401kuznetsov/index.html

Leave a Reply

Buy Generic Viagra USA
Where TO Buy Viagra Online
Buy Viagra Where
Cheap Cheap Viagra
Line Viagra
Buy Discount Viagra
Cheapest viagra online Pills
Buy Viagra Sample
Cheap Viagra Online
Cheap Viagra FAST Shipping
Viagra for Sale
Buy Viagra New York
Cheap Websites For Viagra
Viagra FOR Sales Online
Cheapest Viagra Prices
Cheap Viagra Pills