0

I'm trying to create an SQL user defined aggregate function that can display a specific row from a table that i can use in a group by query when i want to display additional data .

Assuming that i have a table called Employees which has ID , Salary , Name , Gender when i want to display the lowest salary in both genders accompanied by their names

Select min(Salary) , Function_name(name) from Employees group by Gender

the thing is I want to try doing this without using a subquery is that possible at all ?

David Ashraf
  • 1
  • 1
    There is no subquery in your sample question, so I'm not sure what you're asking here. [User-defined Aggregates](https://learn.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-aggregates?view=sql-server-ver16) must be a CLR function, so they wouldn't use a subquery either. Where is the subquery you're referencing? – Thom A Aug 04 '23 at 12:34
  • *"Assuming that i have a table called Employees which has ID , Salary , Name , Gender when i want to display the lowest salary in both genders accompanied by their names"* This sounds more like what you *actually* want is a [top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group). – Thom A Aug 04 '23 at 12:45
  • `SELECT ID, Name, Salary, Gender FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Salary) AS rn FROM Employees) e WHERE e.rn = 1;` – Charlieface Aug 04 '23 at 13:22

0 Answers0