Is there a way to use parameters in subquery with groupby and having in sql query?

Question

I am trying to use rank for getting a rank for each alphabet and select that rank in having function using parameters

Ex: Here is my table data:

Alphabet                           Date       
 A                    2019-12-19 12:31:43.633
 A                    2019-12-19 12:31:43.650
 B                    2019-11-07 11:37:08.560 

select * 
from (select alphabet, date, 
             dense_Rank() over (partition by alphabet order by date) RankOrder
      from mytable
     ) A
group by alphabet, date, RankOrder
having RankOrder = 1

By using the above query here is my Result:

Alphabet                           Date                        Rank Order                 
 A                    2019-12-19 12:31:43.633                   1
 B                    2019-11-07 11:37:08.560                   1

What if I had to do this for multiple alphabets using parameters? using declare @palphabet int='A',@pyear nvarchar(20)=2019 How can I add the parameters to the above query?

Answer 1

You can add where clause :

select a.* 
from(select alphabet,date,dense_Rank() over (partition by alphabet order by date) RankOrder
     from mytable mt
     where mt.Alphabet = @palphabet and year(mt.date) = @pyear
     ) a
where RankOrder = 1;

I don't think GROUP BY with HAVING clause is required here. As you have a raw data not the aggregate data & you are returning only 1 row for each alphabet by using where RankOrder = 1.

Note : You can't use INT Alphabet as base table contains text value. So, change the type of variable @palphabet.

Answer 2

I am not totally clear what you want but I suspect you are asking you can reduce the rows returned to be only for a specific alphabet for a specific year. Something like this should work for you.

declare @mytable table (Alphabet char(1), MyDate Datetime)

insert @mytable values
('A', '2019-12-19 12:31:43.633')
, ('A', '2019-12-19 12:31:43.650')
, ('B', '2019-11-07 11:37:08.560') 

declare @Alphabet char(1) = 'A'
    , @Year int = 2019
select * 
from 
( 
    select t.Alphabet
        , t.MyDate
        , RankOrder = dense_Rank() over (partition by t.Alphabet order by t.MyDate)
    from @mytable t
    --filter the rows here instead of in the final select statement
    where t.Alphabet = @Alphabet
        and t.MyDate >= convert(char(4), @year) + '0101' 
        and t.MyDate <= convert(char(4), @year + 1) + '0101'
) A
where A.RankOrder = 1

Answer 3

I want to get the most recent Date and time for the above query that is the reason I user rank to filter the rank one which is the most recent. Also, I used Group by to group my rank according to the alphabet :

Using Group by:

A       2019-12-19 12:31:43.633    1
A       2019-12-19 12:31:43.650    2
A       2019-12-19 12:31:43.667    3
B       2019-11-07 11:37:08.560    1
B       2019-11-07 11:37:08.577    2

Having is allowing me to select only the rank with 1 which is what I need.

A      2019-12-19 12:31:43.633    1
B      2019-11-07 11:37:08.560    1
C      2019-10-30 15:06:36.643    1
D      2019-11-05 16:16:17.920    1

If I had to do the same by using parameters. The problem is my parameter is in this format @date='F2019/2020' and my date format is 2019-12-19 12:31:43.633. How do I select the particular alphabet and the most recent date for that alphabet using a parameter?