Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get one record on each category Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 9:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 3:49 AM
Points: 3, Visits: 10
Hi.
i have two table Projects and ProjectCategory. They are related with each other.

Projects Table
ProjectId PK
Name
CategoryId
..
..

ProjectCAtegory Table
ProjectCategoryId PK
CategoryName
..

i want to get one project form each category as random. How can i make this?
Post #1363561
Posted Monday, September 24, 2012 9:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 7,230, Visits: 13,709
SELECT p.*, x.*
FROM Projects p
CROSS APPLY (
SELECT TOP 1 *
FROM ProjectCategory pc
WHERE pc.ProjectID = p.ProjectID
ORDER BY NEWID()
) x




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1363582
Posted Monday, September 24, 2012 9:53 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:08 PM
Points: 653, Visits: 3,841
What 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.aspx

Do you need to capture atmospheric noise and process that data in order to select a project?
http://www.random.org/
Post #1363585
Posted Tuesday, September 25, 2012 12:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 3:49 AM
Points: 3, Visits: 10
Bring 1 record randomly from each category

Post #1363815
Posted Tuesday, September 25, 2012 1:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 7,230, Visits: 13,709
cemelma.20 (9/25/2012)
Bring 1 record randomly from each category



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?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1363838
Posted Tuesday, September 25, 2012 2:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2012 3:49 AM
Points: 3, Visits: 10
it work thanks
Post #1363856
Posted Tuesday, September 25, 2012 2:58 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 AM
Points: 75, Visits: 443
Hi! 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...".

/*
SELECT p.*, x.*
FROM ProjectCategory pc
CROSS APPLY (
SELECT TOP 1 *
FROM Project p
WHERE pc.ProjectID = p.ProjectID
ORDER BY NEWID() -- p.ProjectID
) x
*/
use AdventureWorks2008
go
set statistics io on
go
--"any of", for example ordered by ProductID
SELECT
CategoryName = x.Name,
SampleProductName = pc.Name
FROM Production.ProductSubcategory pc
CROSS 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 random
SELECT
CategoryName = x.Name,
SampleProductName = pc.Name
FROM Production.ProductSubcategory pc
CROSS 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.

*/
go
set statistics io off

Subquery executes for each of 37 rows.



I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru
Twitter: @SomewereSomehow
Post #1363864
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse