SQL Subquery

Posted on 2007-08-01
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
on Table1.fieldA = myfilter.fieldA
Question by:lovingatx
    LVL 11

    Expert Comment

    Select FieldA, FieldB
    From Table1
    Group By FieldA, FieldB

    I think that's what you're looking for

    Author Comment

    That will result in duplicate FieldA - I need one record for each FieldA
    LVL 4

    Expert Comment

    Do you always have only 1 corresponding record in FieldB?
    If not, which record from FieldB do you want to see?
    LVL 68

    Accepted Solution

    SELECT FieldA, MAX(FieldB) AS FieldB  -- or MIN() of course
    FROM Table1
    GROUP BY FieldA
    LVL 5

    Expert Comment

    Can you give an example of a small data set and then the corresponding output you want?  

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    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…

    733 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

    19 Experts available now in Live!

    Get 1:1 Help Now