CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlstored-procedures
1110
1110

Reputation: 6829

Is it possible to get two sets of data with one query

I need to get some items from database with top three comments for each item.

Now I have two stored procedures GetAllItems and GetTopThreeeCommentsByItemId.

In application I get 100 items and then in foreach loop I call GetTopThreeeCommentsByItemId procedure to get top three comments.

I know that this is bad from performance standpoint.

Is there some technique that allows to get this with one query?

I can use OUTER APPLY to get one top comment (if any) but I don't know how to get three.

Items {ItemId, Title, Description, Price etc.}
Comments {CommentId, ItemId etc.}

Sample data that I want to get

Upvotes: 4

Views: 128

Answers (4)

Arun Kumar
Arun Kumar

Reputation: 337

Its better that you select the statement by using the row_number statement and select the top 3 alone

select a.* from ( Select *,row_number() over(partition by column)[dup] ) as a where dup<=3

Upvotes: 0

Michael Buen
Michael Buen

Reputation: 39393

This gets two elder brother using OUTER APPLY:

select m.*, elder.*
from Member m
outer apply
(
 select top 2 ElderBirthDate = x.BirthDate, ElderFirstname = x.Firstname
 from Member x 
 where x.BirthDate < m.BirthDate 
 order by x.BirthDate desc
) as elder
order by m.BirthDate, elder.ElderBirthDate desc

Source data:

create table Member
(
 Firstname varchar(20) not null, 
 Lastname varchar(20) not null,
 BirthDate date not null unique
);

insert into Member(Firstname,Lastname,Birthdate) values
('John','Lennon','Oct 9, 1940'),
('Paul','McCartney','June 8, 1942'),
('George','Harrison','February 25, 1943'),
('Ringo','Starr','July 7, 1940');

Output:

Firstname            Lastname             BirthDate  ElderBirthDate ElderFirstname
-------------------- -------------------- ---------- -------------- --------------------
Ringo                Starr                1940-07-07 NULL           NULL
John                 Lennon               1940-10-09 1940-07-07     Ringo
Paul                 McCartney            1942-06-08 1940-10-09     John
Paul                 McCartney            1942-06-08 1940-07-07     Ringo
George               Harrison             1943-02-25 1942-06-08     Paul
George               Harrison             1943-02-25 1940-10-09     John

(6 row(s) affected)

Live test: http://www.sqlfiddle.com/#!3/19a63/2

marc's answer is better, just use OUTER APPLY if you need to query "near" entities (e.g. geospatial, elder brothers, nearest date to due date, etc) to the main entity.

Outer apply walkthrough: http://www.ienablemuch.com/2012/04/outer-apply-walkthrough.html

You might need DENSE_RANK instead of ROW_NUMBER/RANK though, as the criteria of a comment being a top could yield ties. TOP 1 could yield more than one, TOP 3 could yield more than three too. Example of that scenario(DENSE_RANK walkthrough): http://www.anicehumble.com/2012/03/postgresql-denserank.html

Upvotes: 0

e-mre
e-mre

Reputation: 3373

You can write a single stored procedure which calls GetAllItems and GetTopThreeeCommentsByItemId, takes results in temp tables and join those tables to produce the single resultset you need.

If you do not have a chance to use a stored procedure, you can still do the same by running a single SQL script from data access tier, which calls GetAllItems and GetTopThreeeCommentsByItemId and takes results into temp tables and join them later to return a single resultset.

Upvotes: 0

marc_s
marc_s

Reputation: 754488

One approach would be to use a CTE (Common Table Expression) if you're on SQL Server 2005 and newer (you aren't specific enough in that regard).

With this CTE, you can partition your data by some criteria - i.e. your ItemId - and have SQL Server number all your rows starting at 1 for each of those "partitions", ordered by some criteria.

So try something like this:

;WITH ItemsAndComments AS
(
   SELECT 
       i.ItemId, i.Title, i.Description, i.Price,
       c.CommentId, c.CommentText,
       ROW_NUMBER() OVER(PARTITION BY i.ItemId ORDER BY c.CommentId) AS 'RowNum'
   FROM 
       dbo.Items i
   LEFT OUTER JOIN 
       dbo.Comments c ON c.ItemId = i.ItemId
   WHERE
      ......
)
SELECT 
   ItemId, Title, Description, Price,
   CommentId, CommentText
FROM 
   ItemsAndComments
WHERE
   RowNum <= 3

Here, I am selecting up to three entries (i.e. comments) for each "partition" (i.e. for each item) - ordered by the CommentId.

Does that approach what you're looking for??

Upvotes: 1

Related Questions