firstsortedvalue to SQL

[Jsobrinho77]

Hi guys,

How to convert this load using firstsortedvalue to SQL language?

LOAD Customer, Country
FirstSortedValue(distinct Product, -UnitSales) as MyProductWithSmallestOrderByCustomer
Resident Temp Group By Customer, Country;

[ElisaF]

Hello,

A similar function to Qlik FirstSortedValue to SQL Server is FIRST_VALUE, but you need to define the partition correctly. See the link below:

https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-first_value-function/

Please try the syntax below: 

SELECT Customer, Country, Product, UnitSalesTotal,
FIRST_VALUE(Product) OVER(
PARTITION BY Customer, Country
ORDER BY UnitSalesTotal
) MyProductWithSmallestOrderByCustomer
FROM
(SELECT Customer, Country, Product, SUM(UnitSales) AS UnitSalesTotal FROM dbo.SQLTable
GROUP BY Customer, Country,Product
)

I do not have access to a SQL Server, so I cannot verify the syntax. I would appreciate feedback from you.

Eliza