Click here to Skip to main content
15,940,271 members
Please Sign up or sign in to vote.
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
Updated 10-May-17 21:47pm

1 solution

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.
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900