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

Question

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.

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.

guys check out my second question here in this link: http://stackoverflow.com/questions/43918500/specified-cast-is-not-valid-error-using-asp-net — BrunoEarth, May 11 '17 at 14:36

Nenad Zivkovic · Accepted Answer · 2017-05-11T07:10:31.853

3

You can simply use ROW_NUMBER(). In combination with WHERE clause, numbering will be applied only on results filtered by WHERE.

SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS Rn 
FROM YourTable
WHERE Brande LIKE 'Fender'

And if you really need just RN on specific model put this in subquery and select from it.

SELECT Rn FROM 
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS Rn 
    FROM YourTable
    WHERE Brande LIKE 'Fender'
) x
WHERE x.Model LIKE 'Stat15'

edited May 11 '17 at 07:10

answered May 11 '17 at 06:54

Nenad Zivkovic

18,221
6
42
55

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; – BrunoEarth May 11 '17 at 09:53
can you store the value in Int32 count = (Int32)cmd.ExecuteScalar();? – BrunoEarth May 11 '17 at 09:55
Use the second query to get Int32 – Nenad Zivkovic May 11 '17 at 13:18
If you have the time, kindly check out my second question here in this link http://stackoverflow.com/questions/43918500/specified-cast-is-not-valid-error-using-asp-net – BrunoEarth May 11 '17 at 14:36

score 0 · Answer 2 · answered May 11 '17 at 06:55

You can achieve it using a subquery and ROW_NUMBER.Add which ever model and brand you want in the outer WHERE clause

SELECT * 
FROM (
       SELECT *,ROW_NUMBER() OVER(PARTITION BY Brand ORDER BY ID) RN
       FROM [YourTable]
       WHERE Type='Guitar' ) X
WHERE X.Brand='Fender' AND X.Model='strat30'

score 0 · Answer 3 · answered May 11 '17 at 06:58

Yes, you'd use ROW_NUMBER here. You'd need order criteria, e.g. the ID:

select rn
from
(
  select id, type, brand, model, row_number() over (order by id) as rn
  from mytable
  where brand = 'Fender'
) thebrand
where model = 'strat30';

Or:

select rn
from
(
  select id, type, brand, model, row_number() over (partition by brand order by id) as rn
  from mytable
) thebrand
where brand = 'Fender'
  and model = 'strat30';

score 0 · Answer 4 · edited May 11 '17 at 10:10

0

Store your reduced table into a temporary table named #Table (in this instance), and then use the following SQL:

SELECT 
    Model , 
    RowNumber
FROM 
(
    SELECT 
        * ,
        ROW_NUMBER() OVER(PARTITION BY Brand ORDER BY ID) RowNumber
    FROM 
        [#ReducedTable]
)

edited May 11 '17 at 10:10

WonderWorker

8,539
4
63
74

answered May 11 '17 at 06:59

Kinchit Dalwani

398
4
19

score 0 · Answer 5 · answered May 11 '17 at 07:09

BEGIN TRAN
--Here you get the row number of Brand "Fender" and Model "strat30". The returned value should be 2. 
--AND If you want the Model "strat15", the row number should be 3
CREATE TABLE #TEMP (ID INT,type NVARCHAR(50),Brand NVARCHAR(50),Model NVARCHAR(50))

INSERT INTO #TEMP
SELECT 1,'Guitar','Ibanez','custom33' UNION ALL
SELECT 2,'Guitar','Ibanez','custom45' UNION ALL
SELECT 3,'Guitar','Ibanez','custom27'UNION ALL
SELECT 40,'Guitar','Fender','strat45'UNION ALL
SELECT 41,'Guitar','Fender','strat30'UNION ALL
SELECT 42,'Guitar','Fender','strat15'

Select ROW_NUMBER ()OVER (PARTITION BY Brand ORDER BY ID)Rownum, * INTO #T
FROM #TEMP 

SELECT * FROM #T 
WHERE Brand='Fender' AND Model='strat30'

DROP TABLE #TEMP
DROP TABLE #T

ROLLBACK TRAN

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

5 Answers5

Linked