Select nth row on table: includes duplicate value

[rjbudz]

Ok,

A similar situation has been addressed on another thread, and on different sites. Select nth row on table. However, the field value I need may be a duplicate of another field.

Confusing?

SELECT TOP 1 ID_LINE  AS COMMENT_5
FROM PK5.dbo.ID 
WHERE ID_IMKEY = @partNo
AND ID_LINE not in
    (SELECT TOP 4 ID_LINE 
    FROM PK5.dbo.ID 
    WHERE ID_IMKEY = @partNo)

which is all well and good. Except:

SELECT * FROM PK5.dbo.ID 
WHERE ID_IMKEY = @partNo

Returns:

ID_IMKEY	ID_CL	LIN	ID_CR	ID_MD	ID_LINE
==================================================
1000-18006291	GEN	1	NULL	NULL	MUR*BLM31PG500SN1L
1000-18006291	GEN	2	NULL	NULL	ALI*HCB20-500
1000-18006291	GEN	3	NULL	NULL	MUR*BLM31PG500SN1
1000-18006291	GEN	4	NULL	NULL	MUR*BLM31PG500SN1L
1000-18006291	GEN	5	NULL	NULL	WOR*HH-1H3216-500

Notice rows 1 and 4 are the same for the last column, the value I need. By using NOT IN (), I'm eliminating the @partNo I'm looking for, therefore the result of the querty is the fifth row. unfortunately, I don't think I can use LIN because not all ID_IMKEY contain values 1 to n (some may be deleted).

This is a stored procedure in SQL Server 2005.

Any help is apprectiated. On deadline, of course.

Thanks!

[si_the_geek]

You've written a fair amount of text, but haven't actually told us what you are after.

Based on reading between the lines and educated guesses, what I think you want is the value of ID_LINE for the 5th lowest value of LIN (where ID_IMKEY matches the value you specify).

Is that correct?

[rjbudz]

Sorry I lacked clarity. I'm attempting to retrive the value of ID_LINE in the nth row of the table.

For example, the sql statement is intended to grab the value "MUR*BLM31PG500SN1L" in the 4th row. However, in attemting to get it, I need to take the top row after ignoring the values in rows above the one I want, which requres me, by the way the SQL statement is written, to not include rows 1 through 3.

in cases where there is no duplicate, the NOT IN part of the SQL statement does that just fine.

But NOT IN will prevent this if that value is already in the set returned from the nested SELECT.

The result in this case is the value of the 5th row. Not what I want.

[si_the_geek]

OK, that makes sense... Unfortunately there is a bit of a problem, in that you think there is a "4th row" etc, when there really isn't.

The database can (and will) return data in any order it sees fit at that particular moment. Even if it returns a set of records in the same order 1000 times, it could return them in a completely different order next time (and that does happen!).

If the order matters to you at all, the way to deal with it is to use an Order By clause, which specifies the field(s) to sort it by - hence my "for the 5th lowest value of LIN" comment.


Assuming that you do want it sorted by Lin, I think this should work:

SELECT TOP 1 ID_LINE  AS COMMENT_5
FROM (
    SELECT TOP 4 Lin, ID_LINE
    FROM PK5.dbo.ID 
    WHERE ID_IMKEY = @partNo
    ORDER BY Lin
)
ORDER BY Lin DESC

(replace the 4 with the row number you want)