InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 22:54:43 GMT20http://www.sqlservercentral.com/Forums/Topic1363561-392-1.aspxHi! As far as I understand you need the opposite, Category and random sample project from each one. Then, just change places Project and ProjectCategory in ChrisM@Work example.Also, consider the following. What do you need, "random" or "any of...".[code="sql"]/*SELECT p.*, x.*FROM ProjectCategory pcCROSS APPLY ( SELECT TOP 1 * FROM Project p WHERE pc.ProjectID = p.ProjectID ORDER BY NEWID() -- p.ProjectID) x*/use AdventureWorks2008goset statistics io ongo--"any of", for example ordered by ProductIDSELECT CategoryName = x.Name, SampleProductName = pc.Name FROM Production.ProductSubcategory pcCROSS APPLY ( SELECT TOP 1 * FROM Production.Product p WHERE pc.ProductSubcategoryID = p.ProductSubcategoryID ORDER BY p.ProductID) x/*Table 'Product'. Scan count 1, logical reads 395, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ProductSubcategory'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.*/go-- pseudo randomSELECT CategoryName = x.Name, SampleProductName = pc.Name FROM Production.ProductSubcategory pcCROSS APPLY ( SELECT TOP 1 * FROM Production.Product p WHERE pc.ProductSubcategoryID = p.ProductSubcategoryID ORDER BY newid()) x/*Table 'Product'. Scan count 37, logical reads 555, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'ProductSubcategory'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.*/goset statistics io off[/code]Subquery executes for each of 37 rows.Tue, 25 Sep 2012 02:58:29 GMTSomewhereSomehowhttp://www.sqlservercentral.com/Forums/Topic1363561-392-1.aspxit work thanksTue, 25 Sep 2012 02:41:41 GMTcemelma.20http://www.sqlservercentral.com/Forums/Topic1363561-392-1.aspx[quote][b]cemelma.20 (9/25/2012)[/b][hr][b]Bring 1 record randomly from each category[/b][img]http://imageshack.us/a/img19/7799/sqlf.png[/img][/quote]The code I posted does exactly that - it returns one randomly selected category row per project row. If this solution doesn't exactly match your requirements, then you must provide a more complete explanation of what it is that you are expecting to see. It would also help if you were to provide some sample data which folks can code against.Have you checked that the four projects at the top of your output - in the image - have more than one category?Tue, 25 Sep 2012 01:51:22 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1363561-392-1.aspx[b]Bring 1 record randomly from each category[/b][img]http://imageshack.us/a/img19/7799/sqlf.png[/img]Tue, 25 Sep 2012 00:50:00 GMTcemelma.20http://www.sqlservercentral.com/Forums/Topic1363561-392-1.aspxWhat do you mean by random? We could have a huge philosophical discussion about what that means....Can you order the projects by ID and take the highest value? Or lowest? Or median?Do you want to use the RAND function?http://msdn.microsoft.com/en-us/library/ms177610.aspxDo you need to capture atmospheric noise and process that data in order to select a project?http://www.random.org/Mon, 24 Sep 2012 09:53:21 GMTChrissy321http://www.sqlservercentral.com/Forums/Topic1363561-392-1.aspx[code="sql"]SELECT p.*, x.*FROM Projects pCROSS APPLY ( SELECT TOP 1 * FROM ProjectCategory pc WHERE pc.ProjectID = p.ProjectID ORDER BY NEWID()) x[/code]Mon, 24 Sep 2012 09:51:35 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1363561-392-1.aspxHi.i have two table Projects and ProjectCategory. They are related with each other.Projects TableProjectId PKNameCategoryId....ProjectCAtegory TableProjectCategoryId PKCategoryName..i want to get one project form each category as random. How can i make this?Mon, 24 Sep 2012 09:17:55 GMTcemelma.20