Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Subquery

Posted on 2007-08-01
5
Medium Priority
?
385 Views
Last Modified: 2010-03-19
Table1 has Fields A and B, there may be multiple records for each value of FieldA

I want to create a subset that has only one and only one record for each FieldA and then any corresponding FieldB from Table 1

I can accomplish this writing to temp tables but haven't been able to do it using subqueries - can anyone assist?  Below is my sql

Drop Table myFilter;
SELECT Distinct  FieldA, FieldA as Field B
INTO  tempdb.dbo.myFilter
FROM Table1;

Update  myFilter
set            FieldB = Table1.FieldB
from myfilter
inner join
Table1
on Table1.fieldA = myfilter.fieldA
0
Comment
Question by:lovingatx
5 Comments
 
LVL 11

Expert Comment

by:fanopoe
ID: 19612522
Select FieldA, FieldB
From Table1
Group By FieldA, FieldB


I think that's what you're looking for
0
 

Author Comment

by:lovingatx
ID: 19612586
That will result in duplicate FieldA - I need one record for each FieldA
0
 
LVL 4

Expert Comment

by:bamboo7431
ID: 19612826
Do you always have only 1 corresponding record in FieldB?
If not, which record from FieldB do you want to see?
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 19612827
SELECT FieldA, MAX(FieldB) AS FieldB  -- or MIN() of course
FROM Table1
GROUP BY FieldA
0
 
LVL 5

Expert Comment

by:gigglick
ID: 19612843
Can you give an example of a small data set and then the corresponding output you want?  
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

580 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