Solved

Query to find distinct record

Posted on 2007-12-06
3
160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 143

Accepted Solution

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

Expert Comment

by:rheitzman
ID: 20423564
SELECT DISTINCT Part# FROM yourtable WHERE Status='O'

Options:
ORDER BY 1
ORDER BY Part#

0
 

Author Closing Comment

by:kstahl
ID: 31413264
Perfect....thank you!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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 …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

734 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