The DISTINCT should make there are no more doubles but nope.

DISTINCT operates on all of the included columns and returns unique combinations of all of those values. Not just the affiliate ID.

How are you defining "doubles"? Seeing a few sample rows would help (including all 10+ columns) .

The idea is that the field "NAAM" only shows up one time in

the output.

Okay, but what about all the other data? I assume you need those columns because you are including them in the SELECT. So out of the four records for Nent Antiques", which one do you want to keep and why? For example, do you want the record with the latest date, latest record id, etcetera ...

Also, which database are you using MS SQL 2005?

NAAM is dutch for NAME.

Learn something new every day

The list I output is like

NAAM (category)

URL (date) (animation)

Okay .. but the SELECT includes more than just those 5 columns. Are you actually using all 11 columns in your output? The answer will determine what adjustments you need to make.

I found on Google that NEWID () should be the way to do this random selection

Yes.  But first we need to identify what the results should be.  Then we can randomize them. Using the data in your dump, can you give an example of what the desired output would look like?

From what I can see, you are only using four columns: naam, url, date, animation.  If you eliminate the unused columns, DISTINCT will return the expected results. Use TOP if you want to limit the results returned.  To order the results randomly, just write it as a derived table. Something like

SELECT  d.NAAM, d.ANIMATIE, d.WEBSITEURL, d.datum2    
FROM    (
        SELECT  DISTINCT TOP 40 NAAM, ANIMATIE, WEBSITEURL, CONVERT(nvarchar, datum_voorlaatste_aanpassing, 106) AS datum2        
        FROM    LastUpdates
        WHERE   projectID = 1
        ORDER BY datum_voorlaatste_aanpassing
        )  d
ORDER BY NewID()

The date format in the db only matters if you are storing it as text instead of datetime.  If you don't want old records, put something in the where clause of your query to exclude them. 

Since you are using Microsoft SQL Server database, there is a relatively simple solution using a query of a subquery.

The query below will return a random sort of the 40 most current records, using the TOP statement.

A word of caution, when using the TOP statement in a sql query, make sure there are at least the same number of records as for the TOP number. Say if you SELECT TOP 10 records, there must be at least ten records that qualify, if you use SELECT TOP 10 and there is actually only 9 records, the TOP statement will make the database query extremely slow.

Here is a sample query that should give you what you need.

SELECT *

FROM

( SELECT TOP 40 *

FROM TABLE

ORDER BY DATUM DESC ) a

ORDER BY NEWID()

Michael G. Workman

michael.g.workman@gmail.com