DISTINCT

[cgxavier]

I have never used DISTINCT before so help will be apreciated. Im trying to get all the SERIAL NUMBERS, but i know theres duplicates, i dont need the duplicates. So what Im trying to say is that i want to sort them using the SERIALNUMBERS but i dont want duplicates from the SERIAL NUMBERS.THANK YOUSELECT DISTINCT [sn].[WrkstaId] ,[sn].[Computer Model] ,[sn].[serial Number] ,[sn].[Asset Tag] ,[sn].[Computer Type] ,[id].[Name] ,[id].[Domain] ,[id].[OS Name] ,[id].[OS Type] ,[id].[Last Logon User] ,[id].[Last Logon Domain] ,[id].[Client Date] FROM [Altiris].[dbo].[AeXInv_AeX_HW_Serial_Number] sn INNER JOIN [Altiris].[dbo].[AeXInv_AeX_AC_Identification] id ON [sn].[WrkstaId]=[id].[WrkstaID] WHERE [sn].[serial Number]=DISTINCT FROM [Altiris].[dbo].[AeXInv_AeX_HW_Serial_Number]

[justsomeguy]

That query isn't going to work. If you have 2 rows in the sn table with the same WrkstaId, how does the database know which values to select for the other columns? If you're using DISTINCT you can't select other columns, it doesn't make sense to the database. If you need all of that other data it's best to just select everything and filter the data with another language. If you need to use distinct you can only use functions like COUNT, SUM, AVERAGE, etc.Here's an example why that query wouldn't work. If this is the data in the table:

WrkstaId	  Computer Model	  Serial Number	   Asset Tag	   Computer Type1			 A				   B				   C			   D1			 W				   X				   Y			   Z

And you want to select distinct WrkstaIds plus their associated information, the database can figure out that it needs to return 1 for a distinct WrkstaId, but how does it know to return A,B,C,D for the other columns, or W,X,Y,Z? Both of them go with the same distinct ID.

[cgxavier]

Sorry for the confusion. There's multiple unique wrkstaId but same Serial Number can appear several times, with higher WrkstaId value at later dates. What i need is to have the Highest/Latest WrkstaId for each unique Serial Number.

CODEWrkstaId      Computer Model      Serial Number       Asset Tag       Computer Type1             A                   B                   C               D2             W                   B                   Y               Z3             F                   A                   G               H 

[justsomeguy]

It will be best to get all the information and filter it somewhere else. I don't know of a query to get what you're looking for.

[cgxavier]

It will be best to get all the information and filter it somewhere else. I don't know of a query to get what you're looking for.

Thank you