Implementing an "excellent" choice in an existing query

Question

I have an existing, rather long SQL query. I would like to select entries that have a distinct mt.ID. I tried to insert "SELECT DISTINCT" in different places without any success. Can someone tell me where he should go? Many thanks.

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

The problem is that for each record mttablethere are probably several records sttable. Therefore, you do not need DISTINCT, but GROUP BY.

I would try something like the following for internal selection:

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.

Answer 2

GROUP 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