SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get one record on each category


Get one record on each category

Author
Message
cemelma.20
cemelma.20
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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?
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16180 Visits: 19543
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
Chrissy321
Chrissy321
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1232 Visits: 4711
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/
cemelma.20
cemelma.20
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
Bring 1 record randomly from each category

ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16180 Visits: 19543
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
cemelma.20
cemelma.20
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
it work thanks
SomewhereSomehow
SomewhereSomehow
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 469
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search