• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 195
  • Last Modified:

MS SQL records exclusion

MS SQL Server 2008R2

I have the table 'widgets"

widget         process      id
     1                   a           1
      1                  a           2
     1                    b           3

     2                    a           4
      2                   a           5
      2                   a           6

      3                    a          7
      3                   a           8
      3                    a          9
       3                    a        10

        4                   a         11
        4                    a        12
         4                   b          13
I would like to return the record for a widget that does NOT have a process of "b" in any of it's records, and the record I would like to return is the most recent record (highest ID number of any of its records).
The record needed from the above set would be the ones with the "ID" field containing
 "6" & "10"
0
ITMikeK
Asked:
ITMikeK
1 Solution
 
dqmqCommented:
Select * from widgets w inner join
(Select max(id) as Id from widgets t1
   where not exists
   (select * from widgets t2 where t1.widget = t2.widget and t2.process = 'b')
) m on m.id = w.id
0
 
dqmqCommented:
Opps, try again...

Select * from widgets w inner join
(Select widget, max(id) as Id from widgets t1
   where not exists
   (select * from widgets t2 where t1.widget = t2.widget and t2.process = 'b')
   group by widget
) m on m.id = w.id and m.widget = w.widget
0
 
sachinpatil10dCommented:
select widget, process, MAX(id) Id from (
select * from Test
where widget not in(select widget from Test where process = 'b')
) tmp
group by widget,process
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sachinpatil10dCommented:
select widget, process, MAX(id) Id from (
select * from widgets
where widget not in(select widget from widgets where process = 'b')
) tmp
group by widget,process
0
 
sachinpatil10dCommented:
select * from widgets
where widget not in(select widget from widgets where process = 'b')
and id in (select MAX(id) from widgets group by widget)
0
 
Easwaran ParamasivamCommented:
select top 1 from widgets
where process != 'b'
order by id desc
0
 
deightonCommented:
SELECT * FROM 
(SELECT W1.*, RANK() OVER (partition by W1.widget order by W1.id DESC) TheRank  FROM Widgets W1 WHERE NOT EXISTS(SELECT NULL FROM WIDGETS W2 WHERE W2.process = 'b' AND W2.Widget = W1.Widget)) SUBQ
WHERE SUBQ.TheRank = 1

Open in new window

0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now