CODEWITHSUNDEEP
sqlsql-servert-sql
Jhorra
Jhorra

Reputation: 6321

SQL Server: Select all but the first result in query

We have an issue with two tables, let's call them Item and ItemStatuses. We track each change to the status, so there is a start date and end date in the ItemStatuses table. We track the current status by looking for the one with an end date that is null.

Through an error in the system the newest status was added multiple times to a number of items. I need to select all but the first status for each item. I have the following query which gives me all the open statuses. I was trying this route because I figured I could use the row number to skip the first one, but there are multiple Items in these sets, so I need to skip the first status for each item. I think I'm pretty close with my query, but I'm not sure what I need to do.

SELECT ID, rn = ROW_NUMBER() OVER (ORDER BY ItemID)
FROM ItemStatuses WHERE ID IN
(
SELECT
s.ID
FROM Items as i
INNER JOIN ItemStatuses AS s ON
i.ID = s.ItemID AND
s.EndDate IS NULL
GROUP BY i.ID
HAVING COUNT(i.ID) > 1
)

Upvotes: 2

Views: 243

Answers (2)

nathan_jr
nathan_jr

Reputation: 9302

To illustrate how to update all but the first status of your table:

declare @itemstatuses table (id int, Enddate datetime, theStatus int)
insert into @itemstatuses
    values  (1,getdate()-3,1),(1,getdate()-2,2),(1,getdate()-1,3),
            (2,getdate()-3,2),(2,getdate()-2,2),(2,getdate()-1,99),
            (3,getdate(),1)

select 'before',* from @itemStatuses

;with sorted
as  (   
        select  [r] = row_number()over(partition by id order by Enddate), * 
        from    @ItemStatuses
    )
update sorted
set theStatus = 100
where r>1

select 'after',* from @itemStatuses

Upvotes: 2

s_p
s_p

Reputation: 4693

I would simplify your SQL query since this can become overly complicated and expensive.
Then use your server sided language to perform any filtering or condition.

Upvotes: 0

Related Questions