Searching in subordinate table in an SQL GROUP BY

Question

A few weeks ago I asked a question about eliminating duplicate records in a SQL INNER JOIN. The code I ended up using is similar to the following answer:

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.

This works well enough at eliminating duplicates, but the problem I have now is that I want to perform queries on sttable (the table that is not grouped), and the GROUP BY eliminates this data. For example, I want to be able to run the query WHERE st.title = '...'

Is there any way I can achieve this? Thanks.

Answer 1

Try using a CTE (common table expression) that will first define the data you want to get from the sttable table - in the CTE, filter, join, group, etc. on whatever you want to do within the sttable table. Not sure if this will work specifically for what you are trying to do, but it most likely will. If you can provide some additional detail on what you are trying to do (i.e. what are you wanting to do specifically within sttable, filter on certain fields, etc.).

Would look something like this:

with stData as
(
 select st.ID, st.field
 from sttable st
 where st.field <> 0
 -- Add additional filters here
 and st.someOtherField = something
)
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 stData 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.