Welcome to the Treehouse Community
Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.
Looking to learn something new?
Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.
Start your free trial
Giulio Vannini
21,922 PointsSQL Server, how to i remove duplicates?
I've a vista like this:
Agenzia Codice Nome Globmaster 012 NORTHWEST AIRLINES Globmaster 020 LUFTHANSA CARGO Globmaster 023 FEDERAL EXPRESS Globmaster 024 EUROPEAN AIR EXPRESS EAE Globmaster 988 ASIANA Globmaster 994 LINEAS AEREAS AZTECA Globmaster 995 B & H AIRLINES Globmaster 997 BIMAN Gabbiano 012 NORTHWEST AIRLINES Gabbiano 020 LUFTHANSA CARGO Gabbiano 023 FEDERAL EXPRESS Gabbiano 400 PALESTINIAN AIRLINES Gabbiano 406 UPS AIRLINES Gabbiano 407 AIR SENEGAL INTERNATIONAL
I need to modify the query so that it won't give duplicate codes (Codice) at the result:
SELECT 'Gabbiano' AS Agenzia, Codice, Descrizione, FlBSP, MastroForn, CapocForn, ContoForn, SottocForn, CodIVANazAtt, CommNazAttiva, CommIntAttiva,
FlCancellato, DataUltModifica, IDUtente, IDTerminale, CodAnagrafico, NoteDirPortAnd, NoteDirPortRit, ImpDirPortPax, ImpDirPortVei, GGScad,
PathCondGen
FROM dbo.TVTV0000
UNION
SELECT 'Istantravel' AS Agenzia, Codice, Descrizione, FlBSP, MastroForn, CapocForn, ContoForn, SottocForn, CodIVANazAtt, CommNazAttiva, CommIntAttiva,
FlCancellato, DataUltModifica, IDUtente, IDTerminale, CodAnagrafico, NoteDirPortAnd, NoteDirPortRit, ImpDirPortPax, ImpDirPortVei, GGScad,
PathCondGen
FROM IstanTravel.dbo.TVTV0000 AS Statistiche_1
UNION
SELECT 'Globmaster' AS Agenzia, Codice, Descrizione, FlBSP, MastroForn, CapocForn, ContoForn, SottocForn, CodIVANazAtt,
CommNazAttiva, CommIntAttiva, FlCancellato, DataUltModifica, IDUtente, IDTerminale, CodAnagrafico, NoteDirPortAnd, NoteDirPortRit, ImpDirPortPax,
ImpDirPortVei, GGScad, PathCondGen
FROM Globmaster.dbo.TVTV0000 AS Statistiche_2
It should return something like:
Agenzia Codice Nome Globmaster 012 NORTHWEST AIRLINES Globmaster 020 LUFTHANSA CARGO Globmaster 023 FEDERAL EXPRESS Globmaster 024 EUROPEAN AIR EXPRESS EAE Globmaster 988 ASIANA Globmaster 994 LINEAS AEREAS AZTECA Globmaster 995 B & H AIRLINES Globmaster 997 BIMAN Gabbiano 400 PALESTINIAN AIRLINES Gabbiano 406 UPS AIRLINES Gabbiano 407 AIR SENEGAL INTERNATIONAL
Any ideas? :)
2 Answers
Steven Parker
231,261 Points
You still need to decide which Agenzia to associate the duplicate Codice's with, but based on what you gave as the desired output, it looks like you want them to match with the first one when listed in reverse alpha order.
Your sample/desired output doesn't match your sample selects, and it was not clear where "Nome" came from, but after trimming them down to correspond (assuming "Nome" is a column) and adding grouping, I get this:
SELECT MAX(Agenzia) AS Agenzia, Codice, Nome
FROM
( SELECT 'Gabbiano' AS Agenzia, Codice, Nome
FROM dbo.TVTV0000
UNION ALL /* <-- this is more efficient */
SELECT 'Istantravel' AS Agenzia, Codice, Nome
FROM IstanTravel.dbo.TVTV0000 /* AS Statistiche_1 <-- not needed */
UNION ALL
SELECT 'Globmaster' AS Agenzia, Codice, Nome
FROM Globmaster.dbo.TVTV0000 /* AS Statistiche_2 */ )
GROUP BY Codice, Nome
ORDER BY Agenzia DESC, Codice;
Giulio Vannini
21,922 PointsI solved using:
SELECT Agenzia, Codice, Descrizione, FlBSP, MastroForn, CapocForn, ContoForn, SottocForn, CodIVANazAtt, CommNazAttiva, CommIntAttiva, FlCancellato,
DataUltModifica, IDUtente, IDTerminale, CodAnagrafico, NoteDirPortAnd, NoteDirPortRit, ImpDirPortPax, ImpDirPortVei, GGScad, PathCondGen
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Codice
ORDER BY Codice) AS rn
FROM (SELECT 'Gabbiano' AS Agenzia, Codice, Descrizione, FlBSP, MastroForn, CapocForn, ContoForn, SottocForn, CodIVANazAtt, CommNazAttiva,
CommIntAttiva, FlCancellato, DataUltModifica, IDUtente, IDTerminale, CodAnagrafico, NoteDirPortAnd, NoteDirPortRit, ImpDirPortPax,
ImpDirPortVei, GGScad, PathCondGen
FROM dbo.TVTV0000
UNION ALL
SELECT 'Istantravel' AS Agenzia, Codice, Descrizione, FlBSP, MastroForn, CapocForn, ContoForn, SottocForn, CodIVANazAtt, CommNazAttiva,
CommIntAttiva, FlCancellato, DataUltModifica, IDUtente, IDTerminale, CodAnagrafico, NoteDirPortAnd, NoteDirPortRit, ImpDirPortPax,
ImpDirPortVei, GGScad, PathCondGen
FROM IstanTravel.dbo.TVTV0000 AS Statistiche_1
UNION ALL
SELECT 'Globmaster' AS Agenzia, Codice, Descrizione, FlBSP, MastroForn, CapocForn, ContoForn, SottocForn, CodIVANazAtt, CommNazAttiva,
CommIntAttiva, FlCancellato, DataUltModifica, IDUtente, IDTerminale, CodAnagrafico, NoteDirPortAnd, NoteDirPortRit, ImpDirPortPax,
ImpDirPortVei, GGScad, PathCondGen
FROM Globmaster.dbo.TVTV0000 AS Statistiche_2) AS dt) AS dt
WHERE rn = 1
```
Steven Parker
231,261 PointsCongratulations on resolving your issue.
I assume you did it totally on your own before you saw my suggestion, since you voted your own reply as "best answer".
