Piemērs: Vaicājums, izmantojot analītisko funkciju Row_Number

Man pašam ļoti patīk aprakstītās problēmas risinājums un, iespējams, vēl kādam noderēs. Rakstā aprakstīta situācija, vajadzīgais efekts un kā tas panākts.

Tātad, ir tabula ar datiem. Izveidoju pēc iespējas tuvu reālajai situācijai:

Create Table dbo.Problema
(
    ProblemaID int primary key identity, -- bet patiesībā surogātatslēga
    FK_RindasID int,
    Gads int,
    Teksts nvarchar(50)
)
Insert Into dbo.Problema(FK_RindasID, Gads, Teksts) Values(1, 2005, '2005 gads')
Insert Into dbo.Problema(FK_RindasID, Gads, Teksts) Values(1, 2006, '2006 gads 1')
Insert Into dbo.Problema(FK_RindasID, Gads, Teksts) Values(1, 2006, '2006 gads 2')
Insert Into dbo.Problema(FK_RindasID, Gads, Teksts) Values(1, Null, 'Null gads')
Insert Into dbo.Problema(FK_RindasID, Gads, Teksts) Values(2, 2003, '2003 gads')
Insert Into dbo.Problema(FK_RindasID, Gads, Teksts) Values(2, 2004, '2004 gads')
Insert Into dbo.Problema(FK_RindasID, Gads, Teksts) Values(2, 2002, '2002 gads')
Insert Into dbo.Problema(FK_RindasID, Gads, Teksts) Values(3, 2004, '2004 gads')
Insert Into dbo.Problema(FK_RindasID, Gads, Teksts) Values(3, 2002, '2002 gads')
Insert Into dbo.Problema(FK_RindasID, Gads, Teksts) Values(4, Null, 'Null gads')

Tātad, ir tabula, kas izskatās šādi:

ProblemaID	FK_RindasID	Gads	Teksts
1	1	2005	2005 gads
2	1	2006	2006 gads 1
3	1	2006	2006 gads 2
4	1	NULL	Null gads
5	2	2003	2003 gads
6	2	2004	2004 gads
7	2	2002	2002 gads
8	3	2004	2004 gads
9	3	2002	2002 gads
10	4	NULL	2002 gads

ProblēmaID- piemērā identity kolonna, bet realitātē surogātatslēga
FK_RindasID- ārējā atslēga uz citu tabulu
Gads- jāiegūst pēdējā gada ieraksts (ja pēdējā gadā ir vairāki ieraksti, tad jebkurš no tiem).
Tesksts- paskaidrojošs teksts.

Vajadzīgais rezultāts:

FK_RindasID	Gads	Teksts
1	2006	2006 gads 1
2	2004	2004 gads
3	2004	2004 gads
4	NULL	2002 gads

Tātad- no visām rindām, kas atbilst vienai ārējai atslēgai, jāatlasa viena rinda, kurā gada vērtība ir vislielākā.

Risinājumā izmantota SQL Server analītiskā funkcija (ranking funkction) Row_Number. Papildus tam tiek izmantota With klauza (vārds "klauza" man nav pierasts un patiesībā griežas ausī, bet nu cik saprotu tā to sauc..), jeb angliski CTE (Common Table Expresion). Starp citu, With klauza man palikusi dikti tuva!

Tātad:

With Prob As
(
Select ROW_NUMBER() OVER (Partition By FK_RindasID ORDER BY Gads DESC) Nr, FK_RindasID, Gads, Teksts
From dbo.Problema
)
Select FK_RindasID, Gads, Teksts
From Prob
Where Nr = 1

Šis gandrīz ir raksta komentāros solītais, tikai mazliet citā griezumā. Cik esmu redzējis, visbiežāk šāda problēma tiek risināta pavisam savādāk (vai ne?).