>io devo considerare l'id piu alto....
OK, allora te la puoi cavare con una semplice funzione di aggregazione.
Osserva il seguente esempio:
USE tempdb;
CREATE TABLE dbo.Students(
StudentID int NOT NULL IDENTITY PRIMARY KEY,
FirstName varchar(10) NOT NULL,
LastName varchar(10) NOT NULL
);
INSERT dbo.Students VALUES('Lorenzo', 'Benaglia');
INSERT dbo.Students VALUES('Lorenzo', 'Benaglia');
INSERT dbo.Students VALUES('Lorenzo', 'Benaglia');
INSERT dbo.Students VALUES('Luca', 'Bianchi');
INSERT dbo.Students VALUES('Luca', 'Bianchi');
INSERT dbo.Students VALUES('Andrea', 'Montanari');
INSERT dbo.Students VALUES('Marcello', 'Poletti');
INSERT dbo.Students VALUES('Marcello', 'Poletti');
INSERT dbo.Students VALUES('Gianluca', 'Hotz');
SELECT *
FROM dbo.Students;
/* Output:
StudentID FirstName LastName
----------- ---------- ----------
1 Lorenzo Benaglia
2 Lorenzo Benaglia
3 Lorenzo Benaglia
4 Luca Bianchi
5 Luca Bianchi
6 Andrea Montanari
7 Marcello Poletti
8 Marcello Poletti
9 Gianluca Hotz
(9 row(s) affected)
*/
CREATE TABLE dbo.UniqueStudents(
StudentID int NOT NULL PRIMARY KEY,
FirstName varchar(10) NOT NULL,
LastName varchar(10) NOT NULL
);
INSERT dbo.UniqueStudents
SELECT
MAX(StudentID) AS StudentID
, FirstName
, LastName
FROM dbo.Students
GROUP BY
FirstName
, LastName;
SELECT *
FROM dbo.UniqueStudents;
/* Output:
StudentID FirstName LastName
----------- ---------- ----------
3 Lorenzo Benaglia
5 Luca Bianchi
6 Andrea Montanari
8 Marcello Poletti
9 Gianluca Hotz
(5 row(s) affected)
*/
DROP TABLE dbo.Students, dbo.UniqueStudents;
Se non esistono relazioni con altre tabelle e non te ne frega niente di mantenere il MAX ID, puoi semplificare ulteriormente il discorso facendo autogenerare un nuovo ID durante l'INSERT:
USE tempdb;
CREATE TABLE dbo.Students(
StudentID int NOT NULL IDENTITY PRIMARY KEY,
FirstName varchar(10) NOT NULL,
LastName varchar(10) NOT NULL
);
INSERT dbo.Students VALUES('Lorenzo', 'Benaglia');
INSERT dbo.Students VALUES('Lorenzo', 'Benaglia');
INSERT dbo.Students VALUES('Lorenzo', 'Benaglia');
INSERT dbo.Students VALUES('Luca', 'Bianchi');
INSERT dbo.Students VALUES('Luca', 'Bianchi');
INSERT dbo.Students VALUES('Andrea', 'Montanari');
INSERT dbo.Students VALUES('Marcello', 'Poletti');
INSERT dbo.Students VALUES('Marcello', 'Poletti');
INSERT dbo.Students VALUES('Gianluca', 'Hotz');
SELECT *
FROM dbo.Students;
/* Output:
StudentID FirstName LastName
----------- ---------- ----------
1 Lorenzo Benaglia
2 Lorenzo Benaglia
3 Lorenzo Benaglia
4 Luca Bianchi
5 Luca Bianchi
6 Andrea Montanari
7 Marcello Poletti
8 Marcello Poletti
9 Gianluca Hotz
(9 row(s) affected)
*/
CREATE TABLE dbo.UniqueStudents(
StudentID int NOT NULL IDENTITY PRIMARY KEY,
FirstName varchar(10) NOT NULL,
LastName varchar(10) NOT NULL
);
INSERT dbo.UniqueStudents
SELECT DISTINCT FirstName, LastName
FROM dbo.Students;
SELECT *
FROM dbo.UniqueStudents;
/* Output:
StudentID FirstName LastName
----------- ---------- ----------
1 Andrea Montanari
2 Gianluca Hotz
3 Lorenzo Benaglia
4 Luca Bianchi
5 Marcello Poletti
(5 row(s) affected)
*/
DROP TABLE dbo.Students, dbo.UniqueStudents;
>gli unici campi nn doppi sono i capi dell'id che sono pure chiavi
Questa frase mi risulta incomprensibile
Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org