Get one record on each category
-
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?
-
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 ShawFor 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 -
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?
-
Bring 1 record randomly from each category
-
cemelma.20 (9/25/2012)
Bring 1 record randomly from each categoryThe 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 ShawFor 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 -
it work thanks
-
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[/url]
Twitter: @SomewereSomehow
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply