get n-th row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

get n-th row

Author

Topic

dev45
Yak Posting Veteran

54 Posts

Posted - 2005-07-13 : 08:06:22

Hello, is there a way to get the n-th value from a table using a single select statement ?
suppose the table we have is
--------------------------------
table Names(idcolumn identity,name,condition)

having the following values:
1,john,1
2,mary,2
12,theodore,2
14,matt,1
23,josh,1

and i need to get the 2nd row WHERE condition=1.
The result should be : 14,matt,1
is there a way to achieve that using ansi sql ?

theodore

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-13 : 08:09:14

Refer this
http://sqlteam.com/item.asp?ItemID=16134

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-07-13 : 08:14:33

Since you are looking for the ANSI way of doing things, what about


SELECT 
 MIN(e1.id), e1.name, MIN(e1.condition)
FROM 
your_table AS e1
INNER JOIN 
your_table AS e2
ON 
e1.id <= e2.id
WHERE e1.condition =1
GROUP BY 
e1.name
HAVING COUNT(DISTINCT e2.id) = 2

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

dev45
Yak Posting Veteran

54 Posts

Posted - 2005-07-13 : 08:33:41

thx both for your reply :)
I am reading the article and trying to understand frank's query (is seems to return nothing :( )

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-07-13 : 08:34:29

Oops, blame it on the hot weather here. I think I've made a mistake. This should be better


SELECT MIN(e1.id), MIN(e1.name), MIN(e1.condition)
FROM your_table AS e1
INNER JOIN your_table AS e2
ON e1.id >= e2.id
WHERE e1.condition =1
AND e2.condition = 1
GROUP BY e1.id
HAVING COUNT(DISTINCT e2.id) = 2

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-13 : 08:38:38

select t1.*
from tbl t1
where
(select count(*) from tbl t2 where t2.id <= t1.id) = 2

- Jeff

dev45
Yak Posting Veteran

54 Posts

Posted - 2005-07-13 : 08:46:16

yeap, that was much better ;-)
thx once more Frank

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-07-13 : 08:51:04

quote:
Originally posted by jsmith8858

select t1.*
from tbl t1
where
(select count(*) from tbl t2 where t2.id <= t1.id) = 2

- Jeff


(select count(*) from #showme t2 where t2.id <= t1.id and t2.condition=1) = 2

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-13 : 09:20:42

quote:
and i need to get the 2nd row WHERE condition=1.

Try this also


select max(id) from @t 
where id<(select max(id) from @t where condition=1) and condition=1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources