Aggregate function usage as filter

1

Hello

I am struggling to make this aggregate function work.

My data is:
A, 01-01-2000
A, 02-01-2000
B, 03-01-2000
B, 04-01-2000
...

I want to do:
SELECT COL1, COL2
FROM TABLE
WHERE COL3='something'
AND COL2 = (MAX(COL2) OVER (PARTITION BY COL1)

However, I get some errors which I am not sure how to resolve.

I also tried:
HAVING COL2 = (MAX(COL2) GROUP BY COL1)

Which also doesn't work.

Any idea?

Thanks

2

no idea what the final result is that you want or what you are trying to solve?

use sqlteam
GO

DECLARE @SQL6 table(COL1 char(1), COL2 Date, COL3 varchar(10))

insert into @SQL6
select a.COL1, a.COL2, b.name
 from (
select 'A' as COL1, '01-01-2000' as COL2 union all
select 'A', '02-01-2000' union all
select 'B', '03-01-2000' union all
select 'B', '04-01-2000'
) a
join (select top 4 name 
           from sys.all_columns 
union select 'something') b on 1 = 1

declare @maximus table(maxon date)

insert into @maximus
select max(col2) as _maxie
	 from @SQL6

select * from @maximus

SELECT a.COL1, a.COL2
FROM @SQL6 a
join @maximus m on a.COL2 = m.maxon
WHERE COL3='something'

SELECT a.COL1, a.COL2
FROM @SQL6 a
WHERE COL3='something'
 AND exists (select 1 from @maximus m where a.COL2 = m.maxon)


SELECT a.COL1, a.COL2
 FROM @SQL6 a
 join @maximus m on a.COL2 = m.maxon
 WHERE COL3='something'

SELECT a.COL1, a.COL2
  FROM @SQL6 a
 WHERE COL3='something'
   AND COL2 in (select maxon from @maximus m )
3 
SELECT COL1, COL2
FROM (
    SELECT COL1, COL2, DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2 DESC) AS rank_num
    FROM dbo.table_name
    WHERE COL3 = 'something'
) AS derived
WHERE rank_num = 1
ORDER BY COL1
4

Thanks. It seems there is no 'neat' way and I will have to rank and select the first row from the ranked subtable. This is a bit disappointing. Maybe whoever develops SQL would consider a neater way, something along the lines I wrote in my first post.

5

You're never really clear with what you want to do.

If you just want the first row from each COL1, then you can do this:

SELECT COL1, COL2
FROM (
    SELECT COL1, COL2, DENSE_RANK() OVER(PARTITION BY COL1 ORDER BY COL2 DESC) AS row_num
    FROM dbo.table_name
    WHERE COL3 = 'something'
) AS derived
WHERE row_num = 1
ORDER BY COL1
6

neat that is interesting. what do you mean by that. just because you might not know how to do it, or cannot convey your idea clearly does not make SQL not neat