CodeSnip: How to Select the Records from a Table Following a Pattern
page 3 of 4
by Deepankar Sarangi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19307/ 22

Working

Let us first examine the SQL code given below.

Listing 1

select * from emp where (rowid,1) in (select rowid,mod(rownum,2) from emp);

Explanation

The code uses two crucial key words, like rownum and rowid, and a system function like mod(). Here the inner query associates each rowid with either 0 or 1. This is because when rownum (essentially takes positive integers starting from 1) of each record is divided by 2 using mod() function there can only be two possible remainders: 0 or 1. In case of 0 as the remainder, the rownum represents an even numbered record and in case of 1 as the reminder, the rownum points to an odd numbered row.

Since we require the odd series, the outer query selects only those records whose rowid is associated with 1.

Structures of emp table

Figure 1

It is showing all the records of emp table up to row 25. Now we are required to show only the odd numbered records showing FIRST_NAME, like Steven, Lex, Bruce, Valli, etc.

Listing 2

select EMPLOYEE_ID, FIRST_NAME, LAST_NAME 
from emp where (rowid,1) in (select rowid,mod(rownum,2) from emp);

 

Figure 2


View Entire Article

User Comments

No comments posted yet.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 5:03:03 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search