Solved

Query to find distinct record

Posted on 2007-12-06
3
155 Views
Last Modified: 2010-04-21
This should be easy.
I have a SQL table with the data as follows:

Part#     Rev     Status
P123       A          O
P123       B          A
P123       C          O
P123       D          O
P124       C          A
P127       A          O
P128       A          O
P128       B          A
P129       F           O
P130       F           A
P134       A           O
P134       B           O
A=Active Part
O=Obsolete Part

I need to write a query that only pulls the part numbers that are obsolete (no active status regardless of rev).

The correct results would be
P127
P129
P134
0
Comment
Question by:kstahl
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
Comment Utility
select Part#
from yourtable
group by Part#
having min(Status) = 'O'
   and max(status) = 'O'
0
 
LVL 7

Expert Comment

by:rheitzman
Comment Utility
SELECT DISTINCT Part# FROM yourtable WHERE Status='O'

Options:
ORDER BY 1
ORDER BY Part#

0
 

Author Closing Comment

by:kstahl
Comment Utility
Perfect....thank you!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now