SQL Server Max statement returns multiple results

Question

When querying two tables (t1, t2) and using a MAX statement for a column in t2, SQL returns multiple entries.

This seems to be because I also query other info from t2 which consists of unique entries.

Simplified table example

t1.number       t2.number_id       t2.sync_id       t2.text
1               1                  1                'My problem is 
1               1                  2                That
2               2                  3                Multiple entries
2               2                  1                Are
2               2                  2                Returned'

When using

SELECT t1.number, max(t2.sync_id), convert(varchar(100),t2.text) 
FROM t1, t2 
WHERE t1.number = t2.number_id 
GROUP BY t1.number, convert(varchar(100),t2.text)

I get multiple entries instead of just line 2 and 5 from the example table.

Answer 1

You need to remove convert(varchar(100),t2.text) from GROUP BY t1.number, convert(varchar(100),t2.text). You are now grouping by two criteria when you just want grouping by t1.number.

Also you may be interested on how to get all the text in the t2.text column in one single row. If so take a look here: http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Answer 2

Assuming at least SQL 2005 so you can use a CTE:

;with cteMaxSync as (
    select t1.number, max(t2.sync_id) as MaxSyncId
        from t1
            inner join t2
                on t1.number = t2.number_id
        group by t1.number
)
select c.number, c.MaxSyncId, convert(varchar(100),t2.text) 
    from cteMaxSync c
        inner join t2
            on c.number = t2.number_id
                and c.MaxSyncId = t2.sync_id

Answer 3

Standard sql approach

SELECT
     t1.number, 
     t2.sync_id, 
     convert(varchar(100),t2.text) 
FROM
    t1 
    INNER JOIN  t2 
    ON t1.number = t2.number_id 
    INNER JOIN (
       SELECT Max(t2.synch_id) synch_id, t1.number) 
       FROM   t1 
            INNER JOIN  t2 
            ON t1.number = t2.number_id ) max
    ON t1.number = max.number and 
       t2.synch_id = max.synch_id