SQL: selection of rows with unique values but all columns

Question

Hi, I have 2 tables: ATTACHEMENTS and EMAILS (analogic example). ATTACHEMENTS table holds for each row ReferenceID to email it belongs to. Also I email can have 0 or more attachements.

Tables looks like:

Emails
    UID Column1, Column2 ...up to 20 columns
    1       
    2       
    3       
    4       

Attachements
    UID     ReferenceID //link to EMAILS.UID
    1       2 //this and the one below belong to the same parent.
    2       2
    3       1
    4       3

Now I would need to retrieve all emails with attachement, no matter how many they have. I need to get all columns so I guess I cannot use distinct, but I need only rows with differente ReferenceID. From the table above, I would need to have result of 3. In the short: How to select rows with all columns but with distinct referenceID? Thank you

Answer 1

Unfortunately DISTINCT does what is says. So to retrieve the distinct IDs, how would you decide which "other" column values to select from ties?

Or do you wish to retrieve only the IDs? you could attempt to retrieve the details from ties by selecting the first/last row for a tie.

For this you could try something like

DECLARE @Table TABLE(
        UID INT IDENTITY(1, 1),
        ReferenceID INT,
        Col1 VARCHAR(10),
        Col2 VARCHAR(10),
        Col3 VARCHAR(10),
        Col4 VARCHAR(10)
)

INSERT INTO @Table (ReferenceID,Col1, Col2, Col3, Col4) SELECT 2, 1, 1, 1, 1
INSERT INTO @Table (ReferenceID,Col1, Col2, Col3, Col4) SELECT 2, 2, 2, 2, 2
INSERT INTO @Table (ReferenceID,Col1, Col2, Col3, Col4) SELECT 3, 3, 3, 3, 3
INSERT INTO @Table (ReferenceID,Col1, Col2, Col3, Col4) SELECT 4, 4, 4, 4, 4

SELECT  t.*
FROM    @Table t INNER JOIN
        (
            SELECT  ReferenceID,
                    MAX(UID) MUID
            FROM    @Table
            GROUP BY ReferenceID
        ) mID   ON  t.ReferenceID = mID.ReferenceID
                AND t.UID = mID.MUID

Answer 2

UPDATE: I removed my previous answer because it is no longer relevant

Ah okay, this should do it. Note that I'm using Microsoft SQL Server table variables for this complete example, just change the select statement to use "Emails" & "Attachements" instead of "@Emails" & "@Attachements" for your tables.

DECLARE @Emails TABLE
(
    [UID] INT,
    [Column1] VARCHAR(20),
    [Column2] VARCHAR(20),
    [Column3] VARCHAR(20),
    [Column4] VARCHAR(20),
    [Column5] VARCHAR(20)
)

DECLARE @Attachements TABLE
(
    [UID] INT,
    [ReferenceID] INT
)

INSERT INTO @Emails ([UID], Column1, Column2, Column3, Column4, Column5) SELECT 1, 'Data 1', 'Data 2', 'Data 3', 'Data 4', 'Data 5'
INSERT INTO @Emails ([UID], Column1, Column2, Column3, Column4, Column5) SELECT 2, 'Data 6', 'Data 7', 'Data 8', 'Data 9', 'Data 10'
INSERT INTO @Emails ([UID], Column1, Column2, Column3, Column4, Column5) SELECT 3, 'Data 11', 'Data 12', 'Data 13', 'Data 14', 'Data 15'
INSERT INTO @Emails ([UID], Column1, Column2, Column3, Column4, Column5) SELECT 4, 'Data 16', 'Data 17', 'Data 18', 'Data 19', 'Data 20'

INSERT INTO @Attachements ([UID], [ReferenceID]) SELECT 1, 2
INSERT INTO @Attachements ([UID], [ReferenceID]) SELECT 2, 2
INSERT INTO @Attachements ([UID], [ReferenceID]) SELECT 3, 1
INSERT INTO @Attachements ([UID], [ReferenceID]) SELECT 4, 3

-- And here's the select!
SELECT e.UID, e.Column1, e.Column2, e.Column3, e.Column4, e.Column5
FROM @Emails e
WHERE EXISTS
    (SELECT 1 FROM @Attachements a WHERE a.ReferenceID = e.UID)

You could also do a DISTINCT with an inner JOIN, though I prefer the style above personally. I'm not sure which is more efficient.

SELECT DISTINCT e.UID, e.Column1, e.Column2, e.Column3, e.Column4, e.Column5
FROM @Emails e
    INNER JOIN @Attachements a ON a.ReferenceID = e.UID

P.S. If the table names are in English, it's "Attachments" not "Attachements". If it's in another language, then ignore me! :)

Answer 3

select distinct e.uid, col1, col2, ..., col20 from email e, attachments a where e.uid = a.referenceID

OR

select uid, col1, ...,, col20 from email e where e.uid in (select referenceid from attachments)