• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • 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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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
 
deightonprogCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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