Distinct on one column, order by computed column

Question

This is similar to this question and this question, but mine is a little different and I can't get it to work.

I'm using SQL Server 2016. My existing query is below, and it works fine. It selects the Interest row and orders them by the number of Posts that interest has.

-- this is in a stored procedure, variables are valid

select i.[Id], i.[Name], i.[Description],
(
    select count(*) from [Post] p where p.[InterestId] = i.[Id] and p.[CreatedAt] >= @AfterTime
) as PostCount
from Interest i
order by PostCount desc
offset (@Start - 1) rows fetch next (@ReturnNum) rows only

I now want to change this. There's another column in the Interest table with InterestCategoryId; that's a foreign key for a 1:M relationship with InterestCategory. I want to get the same info as before, but limited to only a single InterestCategoryId--I'd only get one result per category. How can I accomplish this? I'm trying this code, but it throws an error that PostCount isn't a valid column.

select [Id], [Name], [Description], PostCount
from
(
    select [Id], [Name], [Description],
    (
        select count(*) from [Post] po where po.[InterestId] = [Id] and po.[CreatedAt] >= @AfterTime
    ) as PostCount,
    row_number() over(partition by [InterestCategoryId] order by PostCount desc) rn
    from [Interest]
) i
where rn = 1
order by PostCount desc
offset (@Start - 1) rows fetch next (@ReturnNum) rows only

Example interest data is below. If we assume that interest ID 1 has more posts than 2, I would want interests 1, 3, and 4 returned.

ID | Name | Description | InterestCategoryId
---|------|-------------|-------------------
 1 | Test | Test int    | 1
 2 | Tes2 | Test int2   | 1
 3 | Tes3 | Test int3   | 2
 4 | Tes4 | Test int4   | 3

A simple `min` aggregation function will do it. I think. Try this: `select min(id) id, min(Name) name, min(Description) Description, min(InterestCategoryId) from Interest ` Since you said that only wants rows 1, 3 and 4 returned that will do it. — Jorge Campos, Feb 10 '17 at 22:24
@JorgeCampos It could be 2, 3, and 4 though if interest 2 has more posts than 1. — vaindil, Feb 11 '17 at 00:41

score 0 · Accepted Answer · answered Feb 11 '17 at 01:16

This isn't pretty, but it runs. I am hoping it helps you get to where you want to go.

select [Id], [Name], [Description], PostCount
from
(
    select [Id], [Name], [Description],
    (
        select count(*) 
        from [Post] po 
        where po.[InterestId] = [Id] 
        and po.[CreatedAt] >= @AfterTime
    ) as PostCount,
    row_number() over(partition by [InterestCategoryId] order by (
        select count(*) 
        from [Post] po 
        where po.[InterestId] = [Id] 
        and po.[CreatedAt] >= @AfterTime
    ) desc) rn
    from [Interest]
) i
where rn = 1
order by PostCount desc
offset (@Start - 1) rows fetch next (@ReturnNum) rows only

If you want to avoid repeating that COUNT sub-query, try it this way.

select [Id], [Name], [Description], PostCount
from
(    
    select AA.Id, AA.Name, AA.Description, AA.PostCount, AA.[InterestCategoryId],
    row_number() over(partition by [InterestCategoryId] order by PostCount desc) rn
    from    (select [Id], [Name], [Description], [InterestCategoryId],
    (
        select count(*) 
        from [Post] po 
        where po.[InterestId] = [Id] 
        and po.[CreatedAt] >= @AfterTime
    ) as PostCount
    from [Interest]) AA
) i
where rn = 1
order by PostCount desc
offset (@Start - 1) rows fetch next (@ReturnNum) rows only

Thanks for the reminder of the OFFSET clause. I don't get an opportunity to use that very often.

Hope this helps. Good luck!

Distinct on one column, order by computed column

1 Answers1