asked
top 3 from nested query
Hello guys I heed help please I was given a nested query : Select field 1 from table where field 1 in (select field2 from table where field 2 in (select...in (select...in Is there a way to return something similar to a pivot table from this type of query - without creating a pivot table I'm suppose to return the top 3 products(outer query) based on the catergoryid of the inner most query, but I'm afraid this is way beyond me right now. Thanks for you help
I don't understand the question completely, but here's a possible example of what you're looking for. This query is used to version data: SELECT d.[DocumentName], d.[DocumentId], v.[VersionDescription], pu.[VersionId], p.[PublisherName], pu.[PublicationDate], pu.[PublicationNumber] FROM dbo.[Document] d CROSS APPLY (SELECT TOP (1) v2.VersionId, v2.DocumentId, v2.VersionDescription FROM dbo.[Version] v2 WHERE v2.DocumentId = d.DocumentId ORDER BY v2.DocumentId, v2.VersionId DESC ) AS v JOIN dbo.[Publication] pu ON pu.[DocumentId] = d.[DocumentId] AND pu.[VersionId] = (SELECT TOP (1) pu2.versionid FROM dbo.Publication pu2 WHERE pu2.DocumentId = d.DocumentId AND pu2.VersionId <= v.[VersionId] AND pu2.PublisherId = pu.PublisherId ORDER BY pu2.DocumentId, pu2.VersionId DESC ) JOIN dbo.[Publisher] p ON pu.[PublisherId] = p.[PublisherId] WHERE d.[DocumentId] = 10432 AND p.[PublisherId] = 4813; This is all taken from a query in [this article on versioned data][1]. I show several other mechanisms there as well. [1]:
http://www.simple-talk.com/sql/database-administration/sql-strategies-for-versioned-data/
Thanks for responding Grant and the query that you've presented looks to be the solution for my problem. I just started 1 week ago and I am already on the clock so to speak. You called everything about right - sorry for the ambiguity. Basically, I have a product table with product pk price field3 field4 Category table categoryid pk field2 field3 Category_product table with ID FK Categoryid FK There are a number of of tables that I would need to join to but this is the jest of my situation. I have to return a pivot like resultset that lists the top 3 prices of each product based on their productid or category. Heck I'll settle for just basing things on the top 3 prices for each respective product. I will give your query a go and report back to you shortly...
9 People are following this question.
