Get column of table for results having sum(a_int)=0 and order by date and group by another column

Question

Think of a table like below:

• unique_id
• a_column
• b_column
• a_int
• b_int
• date_created

Let's say data is like:

-unique_id   -a_column   -b_column     -a_int -b_int       -date_created
    1z23         abc          444          0      1     27.12.2016 18:03:00
    2c31         abc          444          0      0     26.12.2016 13:40:00
    2e22         qwe          333          0      1     28.12.2016 15:45:00
    1b11         qwe          333          1      1     27.12.2016 19:00:00
    3a33         rte          333          0      1     15.11.2016 11:00:00
    4d44         rte          333          0      1     27.09.2016 18:00:00
    6e66         irt          333          0      1     22.12.2016 13:00:00
    7q77         aaa          555          1      0     27.12.2016 18:00:00

I want to get the unique_id s where b_int is 1, b_column is 333 and considering a_column, a_int column must always be 0, if there are any records with a_int = 1 even if there are records with a_int = 0 these records must not be shown in the result. Desired result is: " 3a33 , 6e66 " when grouped by a_column and ordered by date_created and got top1 for each unique a_column.

I tried lots of "with ties" and "over(partition by" samples, searched questions, but couldn't manage to do it. This is what I could do:

select unique_id 
from the_table 
where b_column = '333' 
  and b_int = 1 
  and a_column in (select a_column 
                   from the_table 
                   where b_column = '333'
                     and b_int = 1 
                   group by a_column
                   having sum(a_int) = 0) 
order by date_created desc;

This query returns the result like this " 3a33 ,4d44, 6e66 ". But I don't want "4d44".

Answer 1

You were on the right track with the partitions and window functions. This solution uses ROW_NUMBER to assign a value to the a_column so we can see where there is more than 1. The 1 is the most recent date_created. Then you select from the result set where the row_counter is 1.

;WITH CTE
AS (
    SELECT unique_id
        , a_column
        , ROW_NUMBER() OVER (
            PARTITION BY a_column ORDER BY date_created DESC
            ) AS row_counter --This assigns a 1 to the most recent date_created and partitions by a_column
    FROM #test
    WHERE a_column IN (
            SELECT a_column 
            FROM #test
            WHERE b_column = '333'
                AND b_int = 1
            GROUP BY a_column
            HAVING MAX(a_int) < 1
            )
    )
SELECT unique_ID
FROM cte
WHERE row_counter = 1