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 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

  • 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/

  • Bring 1 record randomly from each category

  • 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

  • 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