Selektieren des jeweils neuesten Wertes

Question

Mein Problem ist sicher trivial, aber ich habe es mit diversen Gruppierungen versucht und komme nicht weiter: Ein Select wie dieser

select ActNo, Betrag, PaymentNo from tblPayment where ActNo in (10174, 10175,10178)

gibt z.B. so ein Ergebnis

 

ActNo                Betrag                PaymentNo

10174                 45                        11

10174                 33                        15

10174                 12                        33

10175                 100                      9

10175                 88                        13

10175                 330                      77

10178                 12                        5

10178                 55                        12

10178                 13                        66

 

Ich will aber zu jeder ActNo den Betrag mit der höchsten PaymentNo (das ist jeweils der neueste Wert) haben. In obigem Beispiel soll also rauskommen:                            

ActNo                Betrag               

10174                 12                       

10175                 330                     

10178                 13      

 (Früher unter Access hätte ich das so gelöst, dass ich eine temp-Tabelle mit den Feldern ActNo und Betrag anlege  und auf ActNo einen PK lege. Und dann per Anfügeabfrage dort angefügt, wobei ActNo aufsteigend und PaymentNo absteigend sortiert wird. Es kommt natürlich eine Schlüssel-Fehlermeldung, aber angefügt wird dennoch, das was angefügt werden soll. Aber das geht leider nicht am SQL-Server.)

Wie kann man sowas lösen?

Answer 1

Hallo Klaus Dieter,

das kannst Du beim SQL Server mit den Fensterfunktionen implementieren, siehe OVER-Klausel.

CREATE TABLE TblPayment (
	ActNo int NOT NULL,
	Betrag int NOT NULL,
	PaymentNo int NOT NULL);
	
INSERT INTO TblPayment VALUES 
	(10174, 45, 11), 
	(10174, 33, 15), 
	(10174, 12, 33), 
	(10175, 100, 9), 
	(10175, 88, 13), 
	(10175, 330, 77), 
	(10178, 12, 5), 
	(10178, 55, 12), 
	(10178, 13, 66);
GO	
WITH Payment 
AS
(
	SELECT ActNo, Betrag, PaymentNo,
		ROW_NUMBER() OVER(PARTITION BY ActNo ORDER BY ActNo, PaymentNo DESC) AS RowNumber
	FROM TblPayment
)
SELECT ActNo, Betrag, PaymentNo
FROM Payment 
WHERE RowNumber = 1;

Gruß Elmar

Answer 2

Hallo Elmar,

vielen Dank für den Tipp. Wenn man den Code so wie er geschrieben ist verwendet (der ist ja Tabellen-unabhängig) hagelt es Fehlermeldungen, OK, ich habe dann nach Insert into gleich meine Tabelle eingesetzt. Dann gab es aber eine Meldung wie "Recursive common table expression 'TblPayment' does not contain a top-level UNION ALL operator".

Egal, aber dein Vorschlag mit Row number ist eine sehr gute Idee. Ich habe einfach diese Zeilen angepasst

select ActNo, Betrag, PaymentNo,
        ROW_NUMBER() OVER(PARTITION BY ActNo ORDER BY ActNo, PaymentNo DESC) AS RowNumber
 from TblPayment  where ActNo in (10174, 10175,10178) -- and Rownumber = 1

Dummerweise kann man die RowNumber   nicht in deie where-Klausel einbauen. Deswegen habe ich folgendes gemacht:

 CREATE TABLE #tempPayment (
    ActNo int NOT NULL,
    Betrag int NOT NULL,
    PaymentNo int NOT NULL,
    RowNumber int);

insert into #tempPayment ( ActNo, Betrag, PaymentNo, RowNumber )
select ActNo, Betrag, PaymentNo,
        ROW_NUMBER() OVER(PARTITION BY ActNo ORDER BY ActNo, PaymentNo DESC) AS RowNumber
from tblPayment where ActNo in (10174, 10175,66095,66099) -- and Rownumber = 1
 
select ActNo, Betrag from #tempPayment where RowNumber = 1
drop table  #tempPayment  

Und das klappt perfekt! Falls du eine elegantere Lösung weißt, kannst du es mir gerne mittteilen

Gruß Klaus-Dieter

Answer 3

"Recursive common table expression 'TblPayment' does not contain a top-level UNION ALL operator".

.... Dummerweise kann man die RowNumber   nicht in deie where-Klausel einbauen.

Hallo Klaus-Dieter,

die erste Fehlermeldung erhält man, wenn man in der CTE bereits die CTE verwendet, das geht eben nur unter bestimmten Umständen.

Die ROW_NUMBER() Funktion kannst Du schon in der WHERE Klausel verwenden, nur Du hattest hier den Feld Alias angegeben und das geht eben nicht. Entweder verwendest Du dort die ROW_NUMBER() Funktion oder baue einfach ein Subselect drum rum:

SELECT *
FROM
(
select ActNo, Betrag, PaymentNo,
       ROW_NUMBER() OVER(PARTITION BY ActNo ORDER BY ActNo, PaymentNo DESC) AS RowNumber
from TblPayment  
where ActNo in (10174, 10175,10178)
) AS Sub
WHERE RowNumber = 1

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

Hallo Olaf,

ja mit subselect geht es (da hätte ich auch selbst drauf kommen können) - das macht die Sache dann einfacher.

vielen Dank für den Tipp. Du machst deinem Nick alle Ehre - oder heißt du etwa wirklich so? ;-)

Ein schönes Wochenende noch, Klaus-Dieter