How to code a nested SQL statement to get row number of a specific item in mssql?

Question

0.00/5 (No votes)

See more:

I wanted to know how to get the row number of a specific item in mssql.

Lets say I have a table like this:

ID Type Brand Model
1 Guitar Ibanez custom33
2 Guitar Ibanez custom45
3 Guitar Ibanez custom27
40 Guitar Fender strat45
41 Guitar Fender strat30
42 Guitar Fender strat15

For example i want to get the items for Fender. I want the table to be like this now(Assuming I'm using a select statement to get all the items with the brand "Fender" in it):

ID Type Brand Model
40 Guitar Fender strat45
41 Guitar Fender strat30
42 Guitar Fender strat15

Then for example, I want to get the row number of Brand "Fender" and Model "strat30". The returned value should be 2. If i want the Model "strat15", the row number should be 3 and vice versa.

What I have tried:

I probably might be needing to nest some sql syntax but i don't know how to combine the SELECT * FROM statement to SELECT ROW_NUMBER statement. Hope you guys can provide example for this.

Feel free to suggest the best way to achieve this output.

Posted 10-May-17 20:52pm

BebeSaiyan

Updated 10-May-17 21:47pm

Add a Solution

1 solution

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)

OriginalGriff · Answer 1 · 2017-05-10T21:47:00

Solution 1

Try:

SQL

SELECT ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNumber, * FROM MyTable WHERE Brand='Fender'

But you shouldn't do it like that: have separate tables for Brand and Type so that you aren't duplicating information and use a foreign key to them instead of storing the same text repeatedly.

Posted 10-May-17 21:47pm

OriginalGriff

Comments

BebeSaiyan 11-May-17 5:57am

but what if now i want to store the outcome of the code in a variabale using asp.net? I've tried this code: string query = string.Format("SELECT *, ROW_NUMBER() OVER(ORDER BY id) AS Rn FROM guitarItems WHERE Brands LIKE '{0}'", brand); con.Open(); cmd.CommandText = query; Int32 count = (Int32)cmd.ExecuteScalar(); con.Close(); X = count;

OriginalGriff 11-May-17 6:13am

1) Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
2) Execute scalar can't return more than one value! Use a DataReader or a DataAdapter instead...

BebeSaiyan 11-May-17 5:58am

but that code is giving me an exception..
can you store the value in Int32 count = (Int32)cmd.ExecuteScalar();?

BebeSaiyan 11-May-17 6:22am

but all i need is just one value like stated above.

OriginalGriff 11-May-17 6:26am

Then you'd need to nest that query in a second which returned RowNumber based on the Model from the query I gave you.