Implementing a "distinct" select in an existing query

Question

I have an existing, fairly lengthy, SQL query. I would like to select records which have a distinct mt.ID. I've tried inserting "SELECT DISTINCT" in various places without any success. Could anyone tell me where it should go? Thanks very much.

SELECT *
FROM (select ROW_NUMBER() OVER(ORDER BY " + orderField + @") as RowNum,
              mt.ID as mt_ID,
              mt.title as mt_title,
              [...]
              st.title as st_title,
              [...]
    from  mttable as mt 
    inner join sttable as st on mt.ID =st.ID
    where NOT (st.field=0) AND where mt.title = @title" )
as DerivedTableName
WHERE RowNum between ((@pageIndex - 1) * @pageSize + 1) and @pageIndex*@pageSize

Answer 1

You need to use GROUP BY plus an aggregate on all columns you're not grouping by:

SELECT *
FROM (select ROW_NUMBER() OVER(ORDER BY " + orderField + @") as RowNum,
              mt.ID as mt_ID,
              max(mt.title) as mt_title,
              [...]
              max(st.title) as st_title,
              [...]
    from  mttable as mt 
    inner join sttable as st on mt.ID =st.ID
    where NOT (st.field=0) AND where mt.title = @title"
    group by mt.ID )
as DerivedTableName
WHERE RowNum between ((@pageIndex - 1) * @pageSize + 1) and @pageIndex*@pageSize

Answer 2

The problem is that there are probably multiple sttable records for each mttable record. So you don't need a DISTINCT but a GROUP BY.

I would try something like the following for the inner select:

SELECT ROW_NUMBER() OVER(ORDER BY " + orderField + @") AS RowNum,
       mt.ID AS mt_ID,
       mt.title AS mt_title,
       [...]
       MAX(st.title) AS st_title,
       -- Other aggregates (MAX, MIN, AVERAGE, ...) for all other columns
       -- from sttable, whatever is appropriate.
       [...]
FROM mttable AS mt 
INNER JOIN sttable AS st on mt.ID =st.ID
WHERE st.field <> 0 AND mt.title = @title
GROUP BY mt.ID,
         mt.title
         -- Group by everything else from mttable.