CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Valentin V
Valentin V

Reputation: 25759

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 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.

Upvotes: 3

Views: 10051

Answers (6)

kravits88
kravits88

Reputation: 13049

SELECT * FROM Customers
EXCEPT SELECT TOP 1 * FROM Customers

Upvotes: 1

Andomar
Andomar

Reputation: 238116

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'

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425431

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

Upvotes: 13

priyanka.sarkar
priyanka.sarkar

Reputation: 26498

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

Upvotes: 0

Wim
Wim

Reputation: 11252

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.

Upvotes: 1

CodeRedick
CodeRedick

Reputation: 7415

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

Upvotes: 9

Related Questions