Solved

Query to find distinct record

Posted on 2007-12-06
3
157 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
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 2 60
SSIS how to COMPARE a data column from different servers? 6 89
Upgrading SQL 2005 Express to 2008 R2 Express 31 68
Query 14 54
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

11 Experts available now in Live!

Get 1:1 Help Now