InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 01:40:45 GMT20http://www.sqlservercentral.com/Forums/Topic1379568-391-1.aspxDone the job nicely thanks.Have noted the feedback around presenting a query and will incorporate into future posts.CheersThu, 01 Nov 2012 21:27:53 GMTDat120Yhttp://www.sqlservercentral.com/Forums/Topic1379568-391-1.aspxWithout something to test this against and using Dwain's tip on the row_number, the following may do what you want[code="sql"];WITH NewIDAllRecords AS ( SELECT jobDB.num AS JobNumber, DateAdd(minute, 570, jobDB.entered) AS CreatedDate, DateAdd(minute, 570, jobDB.closed) AS ClosedDate, person.uniqueId AS UserID, NEWID() AS Random FROM jobDB LEFT OUTER JOIN person ON jobDB.contactId=person.personID WHERE jobDB.status=N'Closed' AND DateAdd(minute, 570, jobDB.entered) >= DATEADD(d, -28, GETDATE()) AND DateAdd(minute, 570, jobDB.closed) >= DATEADD(d, -7, GETDATE()) AND person.uniqueId IS NOT NULL ), SequenceAllUserRecords AS ( SELECT ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY Random) AS Seq, JobNumber, CreatedDate, ClosedDate, UserID, Random FROM NewIDAllRecords )SELECT TOP 50 JobNumber, CreatedDate, ClosedDate, UserIDFROM SequenceAllUserRecordsWHERE Seq = 1ORDER BY Random[/code]Thu, 01 Nov 2012 19:47:08 GMTmickyThttp://www.sqlservercentral.com/Forums/Topic1379568-391-1.aspx[quote][b]T_Dot_Geek (10/31/2012)[/b][hr]Please follow below article helps you how to provide more clarity for your posthttp://www.sqlservercentral.com/articles/Best+Practices/61537/[/quote]We're asking the OP to make it easier for us so let's make it easy for him by providing a clickable link, shall we?[url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]Wed, 31 Oct 2012 22:57:47 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1379568-391-1.aspxPlease follow below article helps you how to provide more clarity for your posthttp://www.sqlservercentral.com/articles/Best+Practices/61537/Wed, 31 Oct 2012 22:50:58 GMTT_Dot_Geekhttp://www.sqlservercentral.com/Forums/Topic1379568-391-1.aspxTake a look at using ROW_NUMBER() in MS BOL.If you can't figure it out post some DDL and sample data (consumable form) where your query would return duplicates. Identify which duplicates you'd like removed.With that someone can provide you a fully tested solution.Wed, 31 Oct 2012 19:11:23 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1379568-391-1.aspxHi,I'm trying to build a customer survey query of 50 random jobs where no customer (UserID) appears more than once. I've played around with DISTINCT and GROUP BY but either get an error or still get duplicates. What can I do to achieve this?[code="sql"]SELECT TOP 50 jobDB.num AS JobNumber, DateAdd(minute, 570, jobDB.entered) AS CreatedDate, DateAdd(minute, 570, jobDB.closed) AS ClosedDate, person.uniqueId AS UserID, NEWID() AS Random FROM jobDB LEFT OUTER JOIN person ON jobDB.contactId=person.personID WHERE jobDB.status=N'Closed' AND DateAdd(minute, 570, jobDB.entered) >= DATEADD(d, -28, GETDATE()) AND DateAdd(minute, 570, jobDB.closed) >= DATEADD(d, -7, GETDATE()) AND person.uniqueId IS NOT NULL ORDER BY Random[/code]RgdsWed, 31 Oct 2012 17:32:26 GMTDat120Y