CodeSnip: How to Select the Records from a Table Following a Pattern
 
Published: 28 Feb 2008
Abstract
In this code snippet, Deepankar examines how to select only specific records from a table commencing in a series (even series i.e. 2nd,4th,6th...records or odd series i.e. 1st,3rd,5th.... records and other simple series) in a single query. He provides a detailed analysis of the relevant SQL statements with the help of relevant screenshots.
by Deepankar Sarangi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 20108/ 69

Introduction

This code snip explains how to select only those records from a table which follow a particular series. Let us say we want to find only the odd numbered rows from a table.

Requirements

Oracle 9i or above versions

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

Conclusion

The use of this query can be extended many ways by manipulating the reminder (used in the where clause) and divisor (used in the mod() function).



User Comments

No comments posted yet.






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


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-14 9:31:33 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search