How to display all but first table row?

Is it possible to display all but first row from a table in sql server 2005? I have this data:

---------------------------------
|  ID  |    Name                |
---------------------------------
|  1   |    John Smith          |
|  2   |    John Doe            |
|  3   |    John Thatcher       |
---------------------------------

In my query I need to be able to get 'John Doe' and 'John Thatcher'. I Don't need 'ID' column to be displayed, so I can't use ROW_NUMBER here like follows:

select Name from Customers where ROW_NUMBER() over (order by Id)>1

Please advice.

Thank you.

UPDATE: Clarification: I would like my query to return only Name column but I can't use table expressions, because I'm using the query as part of string concatenation:

select stuff((select ', '+pfn.FullName from PlaintiffsFullNameView pfn where pfn.SuitId=s.Id for xml path('')),1,1,'') as "CoPlaintiffs"

Now I need to transform this 开发者_JAVA百科query to return all but first plaintiff in a concatenated manner.

UPDATE 2: Sorry for messed up explanation, let me try it anew: I have a suits table and a plaintiffs table. (one to many) I have a requirement to display each suit with all coplaintiffs concatenated. "Coplaintiff" is any but first suit plaintiff. I can concatenate all plaintiffs and display them along with corresponding suit data (all in one row), but I can't to figure out how to concatenate all coplaintiffs and display them as string in a row column.

SELECT  Name
FROM    (
        SELECT  Name, ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM    Customers
        ) q
WHERE   rn > 1
ORDER BY
        id

Update:

From your explanation:

SELECT  Suit.*,
        FirstPlantiff.*,
        (
        SELECT  cp.Name AS [text()]
        FROM    Plantiff cp
        WHERE   cp.id <> FirstPlantiff.id
                AND cp.SuitID = Suid.ID
        ORDER BY
                cp.id
        FOR XML PATH('')
        ) AS Coplantiffs
FROM    Suit
CROSS APPLY
        (
        SELECT  TOP 1 *
        FROM    Plantiff p
        WHERE   p.SuitID = Suit.ID
        ORDER BY
                p.id
        ) FirstPlantiff

SELECT Name 
FROM Customers 
WHERE  ID <> (SELECT TOP 1 ID 
              FROM Customers 
              ORDER BY ID)

Or since the Id never changes you could just do where ID <> 1

Your query with ROW_NUMBER gives an error, because you can't use ROW_NUMBER in the WHERE clause. So you'd need another subquery:

select stuff((
    select ',' + FullName
    from (
        select pfn.FullName, row_number() over (order by pfn.id) as rn
        from @suits s
        inner join @plaintiffs pfn on s.id = pfn.SuitId
    ) sub
    where rn <> 1
    for xml path('')
), 1, 1, '') subsub

Alternatively, you could select the id of the first row in a subquery:

select stuff((
    select ',' + pfn.FullName
    from @suits s
    inner join @plaintiffs pfn on s.id = pfn.SuitId
    where s.id = 1
    and pfn.id not in (
        select min(id) from @plaintiffs where SuitId = s.id)
    for xml path('')
), 1, 1, '') sub

Here's the code segment to generate test data:

declare @suits table (id int identity, CaseName varchar(max))
insert into @suits (CaseName) values ('The People v.s. Donald Duck')
declare @plaintiffs table (id int identity, 
    SuitId int, FullName varchar(max))
insert into @plaintiffs (SuitId,Fullname) 
select 1, 'John Smith'
union all select 1, 'John Doe'
union all select 1, 'John Thatcher'

Your query should work, there's no need for Id to be returned for it to be used in the WHERE condition.

Also, maybe this page can help.

SELECT * FROM Customers
EXCEPT SELECT TOP 1 * FROM Customers

Try these

Solution 1:

select name 
from @tbl
where id <> 1

Solution 2:

select top(select count(name) -1 from @tbl) name 
from @tbl 
order by id desc