How To Code A Nested Sql Statement To Get Row Number Of A Specific Item In Mssql?

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 G

Solution 1:

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 (ORDERBY 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 (ORDERBY ID) AS Rn 
    FROM YourTable
    WHERE Brande LIKE'Fender'
) x
WHERE x.Model LIKE'Stat15'

Solution 2:

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(PARTITIONBY Brand ORDERBY ID) RN
       FROM [YourTable]
       WHERE Type='Guitar' ) X
WHERE X.Brand='Fender'AND X.Model='strat30'

Solution 3:

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 (orderby id) as rn
  from mytable
  where brand ='Fender'
) thebrand
where model ='strat30';

Or:

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

Solution 4:

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 3CREATETABLE #TEMP (ID INT,type NVARCHAR(50),Brand NVARCHAR(50),Model NVARCHAR(50))

INSERTINTO #TEMP
SELECT1,'Guitar','Ibanez','custom33'UNIONALLSELECT2,'Guitar','Ibanez','custom45'UNIONALLSELECT3,'Guitar','Ibanez','custom27'UNIONALLSELECT40,'Guitar','Fender','strat45'UNIONALLSELECT41,'Guitar','Fender','strat30'UNIONALLSELECT42,'Guitar','Fender','strat15'SelectROW_NUMBER ()OVER (PARTITIONBY Brand ORDERBY ID)Rownum, *INTO #T
FROM #TEMP 

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

ROLLBACK TRAN

Solution 5:

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(PARTITIONBY Brand ORDERBY ID) RowNumber
    FROM 
        [#ReducedTable]
)