Select from multiple tables where one has distinct values

Question

I have a simple problem here using SQL views. I just can't seem to figure it out at the moment.

• I have 2 tables, TableA and TableB.
• I want to retrieve FieldA in TableA and FieldB in TableB.
• The two tables are linked using an INNER JOIN.
• I only want rows where TableA.FieldA are distinct.
• The returned values should be of the top 10 items from TableB.FieldB

Simply using SELECT DISTINCT seems to be using the combination of the two fields to determine distinction.

Any ideas?

Here is a mock of the SQL currently returning all rows :

SELECT dbo.TableA.FieldA, dbo.TableB.FieldB
FROM dbo.TableA INNER JOIN dbo.TableB ON dbo.TableA.ID = dbo.TableB.TableAID

An example of data returned from this standard query would be :

FieldA  FieldB
John    78
John    21
Claire  18
Sam     16
John    25
Claire  48
Paul    53

What I am looking to have returned from the query would be :

John    78
Paul    53
Claire  48
Sam     16

Many thanks in advance.

**EDITED to try and make things a bit clearer and include missing information.

Answer 1

If there are two different values for field A in table b based on the join, how would you know which one to use?

You could try this but it may not choose the b value you want

SELECT dbo.TableA.FieldA, max(dbo.TableB.FieldA)
FROM dbo.TableA INNER JOIN dbo.TableB ON dbo.TableA.SomeID = dbo.TableB.SomeID
GROUP BY dbo.TableA.FieldA

Answer 2

How about this?

SELECT TableA.FieldA,
       tmp.FieldB
FROM   TableA
       INNER JOIN
              (SELECT  TableAID,
                       FieldB
              FROM     TableB
              GROUP BY FieldA
              ORDER BY FieldB DESC
              ) AS tmp
       ON     TableA.ID = tmp.TableAID
ORDER BY tmp.FieldB DESC

Answer 3

HLGEN : That worked well. However I have just had the spec change on me.

Rather then taking the MAX value for each TableA.FieldA I am required to to SUM(TableB.FieldB) for each Table(A.FieldA) and then take the top 10 results.

For example :

Assuming the sample data

FieldA  FieldB
John    78
John    21
Claire  18
Sam     16
John    25
Claire  48
Paul    53

The resulting output would be

John    124
Claire  66
Paul    53
Same    16

**EDIT

I understand the idea of adding SUM instead of MAX. However it seems that this query does not allow for duplicate sets of data.

By this I mean if (John, 25) was present twice it would only include it once in the SUM.

Any thoughts?

**EDIT 2

I'm not using DISTINCT, the query being used is :

SELECT dbo.TableA.FieldA, max(dbo.TableB.FieldA) As Expr1
FROM dbo.TableA INNER JOIN dbo.TableB ON dbo.TableA.SomeID = dbo.TableB.SomeID
Group by dbo.TableA.FieldA

Answer 4

hi

than just replace the max function in hlgens query with sum

**EDIT sure there is no "distinct" in your query?

Answer 5

Based on your answer I think what you are trying to do is this:

Sum FieldB in TableB and then join it with TableA.

SELECT   TableA.FieldA,
         tmp.SUMFieldB
FROM     TableA
         INNER JOIN
                  (SELECT  TableAID,
                           SUM(FieldB) AS SUMFieldB
                  FROM     TableB
                  GROUP BY FieldA
                  ORDER BY SUMFieldB DESC
                  ) AS tmp
         ON       TableA.ID = tmp.TableAID
ORDER BY tmp.SUMFieldB DESC

Answer 6

strong text*emphasized text*

-

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

---

=======

---