Jump to content

DISTINCT


cgxavier
 Share

Recommended Posts

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]

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...