Retrieve nth option from a database table

Question

2.20/5 (3 votes)

See more:

I'm developping an application with C# and ADO.Net entity data model. I have a table Users in SQL Server database with 3 columns (idUser, nameUser, statusUser). How can i retrieve the second or the third or the Nth user of the table that have statusUser == 0?

Posted 12-May-13 6:36am

Nnorss

Updated 12-May-13 10:00am

v8

Add a Solution

Comments

David_Wimbley 12-May-13 12:39pm

What have you tried? Can you post some code so we can see where you went wrong or if you've even tried anything?

Nnorss 12-May-13 12:55pm

I updated the question, please check it.

Nnorss 12-May-13 12:42pm

For the first one, i've done it. But for the second, i dont even know from where begin!!!

David_Wimbley 12-May-13 13:04pm

So stupid question...why is it that you must ONLY get the second user? What are you doing in code that requires you to get only the second user...can you not pass in the username of the user? Or can you not perform the operation you are looking to do on all users where statusUsers == false?

The brute force way (not compiled so "should" work)

Articles firstArticle = db.Articles.Select(u => u.statusUsers == false).ToArray()
var secondUser = firstArticle[1];

Would that not do what you are looking for? If ToArray doesn't work you could try .ToList(). If it still doesn't work ill slap together a test project and give it a try but that general idea should get just the second user.

David_Wimbley 12-May-13 13:13pm

Another brute force option

var count = 0;

Articles firstArticle = db.Articles.Select(u => u.statusUsers == false);

foreach(var item in firstArticle)
{
if(count == 1)
{
//Do Code On Second User Here
}
count += 1;
}

Nnorss 12-May-13 13:19pm

Both of ToArray and Tolist are not working. Impossible to convert......

Nnorss 12-May-13 13:25pm

db.Articles.Select(a => a.statusUser == false): impossible to convert....

2 solutions

Solution 1

If you want the 2nd record, how about this....

SQL

SELECT TOP 1 *
FROM myTable
WHERE ID NOT IN ( SELECT TOP 1 ID FROM myTable ORDER BY ID )
ORDER BY ID

Posted 12-May-13 7:17am

Motley Drew

Comments

Nnorss 12-May-13 13:29pm

Where should i put this code please?

Maciej Los 12-May-13 16:34pm

Please, see my answer ;)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-05-12T10:33:00

Solution 2

You need to define stored procedure[^].

SQL

CREATE PROCEDURE GetNthUserByStatus
    @stat INT,
    @nth INT
AS
BEGIN
SELECT idUser, nameUser, statusUser
FROM (
    SELECT idUser, nameUser, statusUser, ROW_NUMBER() OVER(ORDER BY idUser) AS RowNo
    FROM [Users]
    WHERE statusUser = @stat
    ) AS T
WHERE T.RowNo = @nth
END

After that, you need to call this stored procedure from code. How? See this: HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET[^]

Posted 12-May-13 10:33am

Maciej Los

Comments

Nnorss 12-May-13 16:36pm

ok thanks i will try this solution!

Maciej Los 13-May-13 6:48am

Does you problem is solved?
If yes, please, mark this answer as "solved" (green button).

Nnorss 13-May-13 13:23pm

I'm still working with this and i will mark this answer solved when i found the complete solution... please check my other comment and thank you anyway.