distinct

[rjhe22]

i have a query and i want it to pull back just one of the account name instead of having 6 of the same account names showin up but it does not seem to work for me here is my query

SELECT DISTINCT [Fs_0003_Access_Stats].AccountName, [Fs_0003_Access_Stats].ID, [Fs_0003_Access_Stats].AccessPath, [Fs_0003_Access_Stats].Date, [Fs_0003_Access_Stats].UserName, [Fs_0003_Access_Stats].FileServer, [Fs_0003_Access_Stats].EventCount, [Fs_0003_Access_Stats].EventCountSub
FROM [Fs_0003_Access_Stats] INNER JOIN [T Users] ON [Fs_0003_Access_Stats].AccountName = [TUsers].LID;

 

[MStef]

Not SELECT DISTINCT.....
but
SELECT TOP 1....

 

[rjhe22]

sorry didnt explain my self well.
top 1 wont work as i have 500 records with 20 different accountname want the 20 names

 

[Kiwiman]

Howzit

A distinct query will work on multiple columns, but it will return the unique combination on each column in your query. In your case you have 8 columns and each row returned will be unique based on the content of those 8 columns

This will give you a distinct Accountname - note it only has the one column in the result set

SELECT DISTINCT [Fs_0003_Access_Stats].AccountName FROM [Fs_0003_Access_Stats] INNER JOIN [T Users] ON [Fs_0003_Access_Stats].AccountName = [TUsers].LID;

Which is the same as

SELECT [Fs_0003_Access_Stats].AccountName FROM [Fs_0003_Access_Stats] INNER JOIN [T Users] ON [Fs_0003_Access_Stats].AccountName = [TUsers].LID GROUP BY [Fs_0003_Access_Stats].AccountName;