how to get record by positioning

samrat net

Hi Friend! i've faced a problem. Suppose, i have 10 records in a table.i want only the 2nd 0r 3rd position record. how can i achieve it? thanks.......

GuyThiebaut

This is a quick and dirty script I knocked up to illustrate one method of pulling out the second and third record. There are more elegant solutions.

select top 1 itemnumber into #a from stocktable
order by itemnumber

select top 3 itemnumber into #b from stocktable
order by itemnumber

select #b.itemnumber from #b
left join #a
on #b.itemnumber = #a.itemnumber
where #a.itemnumber is null

Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)

TheFM234

Create Table #r
(
id --Identity column. Use your SQL databas's syntax here.
--For example, SQL Server is Identity(1,1).
...--Columns in the table
)

Insert #r(...--Columns in table)
Select *
From YourTable
Order By YourColumn

Select *
From #r
Where id in (2,3)

What that does is creates a new table with the same build of YourTable with an added identity column. It inserts the records and orders it, an important step is to order it or you'll may get different rows each time you run. Change the 2,3 in the in statement to get any row number you want.

leoinfo

Well ... if you are using SQL 2005 ... I think this is what you are looking for :)

/* TEST DATA */
/****************************/
CREATE TABLE #T (id INT IDENTITY(1,1), aField NVARCHAR(10) ) ;
INSERT INTO #T (aField) SELECT 'one' ;
INSERT INTO #T (aField) SELECT 'two' ;
INSERT INTO #T (aField) SELECT 'three' ;
INSERT INTO #T (aField) SELECT 'four' ;
INSERT INTO #T (aField) SELECT 'five' ;
INSERT INTO #T (aField) SELECT 'six' ;
INSERT INTO #T (aField) SELECT 'seven' ;

/* SORTED LIST */
/****************************/
SELECT * FROM #T ORDER BY aField DESC

/* EXTRACT FROM SORTED LIST */
/****************************/
;WITH myTable AS (
SELECT
ROW_NUMBER() OVER( ORDER BY aField DESC ) AS RowNo
, id
, aField
FROM #T
)
SELECT *
FROM myTable
WHERE RowNo IN ( 2 , 4 ) ;
/****************************/

DROP TABLE #T ;

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

modified on Friday, July 25, 2008 8:38 AM

leoinfo

This is what I would use in SQL 2000 :

/* TEST DATA */
/****************************/
CREATE TABLE #T (id INT IDENTITY(1,1), aField NVARCHAR(10) ) ;
INSERT INTO #T (aField) SELECT 'one' ;
INSERT INTO #T (aField) SELECT 'two' ;
INSERT INTO #T (aField) SELECT 'three' ;
INSERT INTO #T (aField) SELECT 'four' ;
INSERT INTO #T (aField) SELECT 'five' ;
INSERT INTO #T (aField) SELECT 'six' ;
INSERT INTO #T (aField) SELECT 'seven' ;

/* TEST LIST */
/****************************/
SELECT IDENTITY(INT, 1 , 1) AS RowNo, 0+id AS id, aField
INTO #Z
FROM #T
ORDER BY aField DESC

/* SORTED LIST */
/****************************/
SELECT * FROM #Z

/* EXTRACT FROM SORTED LIST */
/****************************/
SELECT * FROM #Z WHERE RowNo IN ( 2 , 4 ) ;
/* */
DROP TABLE #T ;
DROP TABLE #Z ;

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

David Mujica

If you really only have 20 or so records, why wouldn't you load all of the records into an array, then access the Nth element whenever you want. If this is lookup type data you could load this array once and save yourself lots of headaches with complex SQL. I'm a big fan of keeping things simple. Just a thought. :)

leoinfo

David Mujica wrote:

If you really only have 20 or so records

If you have 20 records and you only need 1, why load other 19 records if you know that you'll throw them away anyway ? What if you have 10000 users loading extra 19 records everytime ?

David Mujica wrote:

save yourself lots of headaches with complex SQL

I cannot agree with this ... I prefer the headaches :)

Please... SAVE my time by rating the posts that you read!

There are 10 kinds of people in the world: those who understand binary and those who don't.

Blue_Boy

select top 2 rowposition from tablename where rowposition > 1

I Love T-SQL "Don't torture yourself,let the life to do it for you."

Ashfield

Regardless of all the fine answers you have been given, why do you need to read all the records if you know you need one of them? What I mean is, if you know you want the nth record there must be something that makes it the one you want, so why not put that criteria in the original select? :confused:

Bob Ashfield Consultants Ltd

Mycroft Holmes

Actually we have this requirement when calculating VAR, get the 500 scenarios, sort by your worst exposure and get 5th worst exposure of 500, rowposition does this.

Never underestimate the power of human stupidity RAH

GuyThiebaut

More 1 votes please :laugh: