Virtual Column for Counting

Virtual Column for Counting

How I can get this virtual column in select statement in sqlserver
Sample EmpTable
EmpID EmpName
1001 sample1
1002 test1
1003 sample2
1004 test2
1005 sample3
SQL Statement Select virCol, EmpID, EmpName from EmpTable Where EmpName
Like '%sam%'
Sample Output
virCol EmpID EmpName
1 1001 sample1
2 1003 sample2
3 1005 sample3
I tried the Select virCol = row_number() over (order by EmpID), EmpID,
EmpName from EmpTable Where EmpName Like '%sam%'
But I got this output
virCol EmpID EmpName
1 1001 sample1
3 1003 sample2
5 1005 sample3
What is the SQL to have virtual column for counting? Because I will
further use that into something like this: Select virCol, EmpID, EmpName
from EmpTable Where EmpName Like '%sam%' AND virCol between 1 and 2 to
have this Output:
virCol EmpID EmpName
1 1001 sample1
2 1003 sample2
EDIT
My Actual Query and Output
select * from(
select
Row,
BookTitleID,
BookTitle,
CallNumber,
FullName,
CopiesOnShelves
from
(
select
Book.BookTitleID,
BookTitles.BookTitle,
BookTitles.CallNumber,
FullName = LastName + ', ' + FirstName + ' ' + MiddleName,
CopiesOnShelves = count(case Status when 'OnShelf' then 1 else
null end),
Row = row_number() over (order by BookTitle)
From
Book
left outer join
BookTitles
on BookTitles.BookTitleID = Book.BookTitleID
left outer join
Authors
on Authors.AuthorID = BookTitles.AuthorID
Group By Book.BookTitleID, BookTitles.BookTitle, BookTitles.CallNumber,
LastName, FirstName, MiddleName
) sub
) sub2
Where BookTitle like '%some%'
Order By Row

All joined data wihout where clause

AuthorsTable
CREATE TABLE [dbo].[Authors](
[AuthorID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
CONSTRAINT [PK_Authors] PRIMARY KEY CLUSTERED
(
[AuthorID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
BookTable
CREATE TABLE [dbo].[book](
[AccessionNumber] [int] IDENTITY(1,1) NOT NULL,
[BookTitleID] [int] NULL,
[Status] [varchar](50) NULL,
[Barcode] AS ([AccessionNumber]+(100000)),
[DateAcquired] [date] NULL,
CONSTRAINT [PK_book_1] PRIMARY KEY CLUSTERED
(
[AccessionNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
BookTitles Table
CREATE TABLE [dbo].[BookTitles](
[BookTitleID] [int] IDENTITY(1,1) NOT NULL,
[BookTitle] [nvarchar](max) NULL,
[CallNumber] [nvarchar](50) NULL,
[AuthorID] [int] NULL,
[YearOfPublication] [smallint] NULL,
[Edition] [nvarchar](50) NULL,
[Publisher] [nvarchar](50) NULL,
[ISBN] [nvarchar](50) NULL,
[Subject1Number] [int] NULL,
[Subject2Number] [int] NULL,
[Copies] [int] NULL,
CONSTRAINT [PK_BookTitles] PRIMARY KEY CLUSTERED
(
[BookTitleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]