>AFFCR DIGIT IMPORTO
>-------------------- ------ ---------------------------------------
>161884 2 50200.000000000000
>161884 1 27502.000000000000
>
>in questo caso, non devo prendere nessun record
>
>AFFCR DIGIT IMPORTO
>-------------------- ------ ---------------------------------------
>145895 2 50200.000000000000
>in questo caso devo prendere il record
Spero di aver capito:
USE tempdb;
GO
CREATE TABLE dbo.prova
(
AFFCR int
, DIGIT tinyint
, IMPORTO decimal(24,12)
)
GO
INSERT INTO dbo.prova
VALUES (161884, 2, 50200)
INSERT INTO dbo.prova
VALUES (161884, 1, 27502)
INSERT INTO dbo.prova
VALUES (145895, 2, 50200)
INSERT INTO dbo.prova
VALUES (161884, 3, 50200)
INSERT INTO dbo.prova
VALUES (161995, 1, 27502)
INSERT INTO dbo.prova
VALUES (161995, 2, 50200)
INSERT INTO dbo.prova
VALUES (111111, 2, 50700)
GO
SELECT
*
FROM
dbo.prova
ORDER BY
AFFCR
, DIGIT
;WITH CTE AS
(
SELECT
AFFCR
, DIGIT
, IMPORTO
, rnum = ROW_NUMBER() OVER(PARTITION BY AFFCR ORDER BY AFFCR, DIGIT)
FROM
dbo.prova
)
SELECT
*
FROM
CTE
WHERE
rnum = 1
AND DIGIT = 2
DROP TABLE dbo.prova
GO
--
Alessandro Alpi | SQL Server MVP
MCP|MCITP|MCTS|MCT
http://www.alessandroalpi.net
http://blogs.dotnethell.it/suxstellino
http://mvp.support.microsoft.com/profile/Alessandro.Alpi