Position a row in a result set

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)

Position a row in a result set

Author

Topic

Yvee
Starting Member

7 Posts

Posted - 2003-02-28 : 13:57:35

I have the following Select stmt;

SELECT ParentID, count(*) as size
FROM <table names>
WHERE <where conditions>
group by ParentID order by size DESC

I want to know the position of a particular "ParentID" in this result set. For example if the stmt returns:

ParentId - Size
---------------
49 - 240
62 - 120
2 - 63
5 - 50

ParentId "62" is at the second position. I want to be able to specify a certain ParentId and find out its position in the resultset

I thought about using Cursors, but I'm not sure it would work.

Thanks in advance,

Yvee..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-28 : 14:03:05

There's no such thing as "position" or "row number" in a relational database. More accurately, it is not a concept that has any meaning, nor should a user attach any meaning to such a thing. Your data is defined by its values, not it's position.

You can read more about it here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23521

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-28 : 14:22:02

I think you mentioned and/or strongly implied that you are trying to determine the position within an ordered result set, not the position where it's stored, so I think what you are after is pretty common and valid.

But as Rob mentioned, don't think of it as returning a position, but rather as returning a Ranking:

SELECT ID, Value, (SELECT COUNT(*) FROM Table T2 WHERE T2.Val >= Table.Val) as Rank
FROM
Table
ORDER BY Value DESC

That will give the the ranking of each ID when sorted by Value. The key is the WHERE clause in the subquery; that has to be "in synch" with your ORDER BY to return consistent results.

As a warning, this query can be inefficient if the # of rows you are trying to rank is very large.

EDIT:

So, again what you are doing is working with the data itself and NOT thinking about the position of the data (as Rob mentioned); you can return the Rank of ID's by Value, but not the position of the ID's when sorted by value .... hopefully that makes some sense.

Edited by - jsmith8858 on 02/28/2003 14:36:28

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources