October 19, 2016 at 11:08 am
Suppose, I've two tables as follows:
create table #table1(name varchar(100), qty int)
create table #table2 (forename varchar(100), surname varchar(100), cost int)
insert into #table1 values
('John',1),
('Paul',2),
('Ringo',1)
insert into #table2 values
('John' , 'Smith' , 123),
('John' , 'Jones' , 815 ) ,
('Paul' , 'Smith' , 273),
('Paul' , 'Jones' , 297),
('Ringo' , 'Smith' , 755),
('Ringo' , 'Jones' , 334)
So what I want to do is to return the rows for each name depending upon the Qty value. Like Paul has the Qty value 2, so the query should return two rows for Paul. And for others will get one as the Qty value is one for them.
I tried the following query but gets data of Paul only:
SELECT m.forname, k.SurName, k.Cost FROM Sample2 m
INNER JOIN [Sample] k
ON k.ForeName = m.forname
GROUP BY m.forname, m.Qty, k.Cost, k.id, k.SurName
HAVING m.Qty > COUNT(k.ForeName)
October 19, 2016 at 11:21 am
AT-2016 (10/19/2016)
Suppose, I've two tables as follows:
create table #table1(name varchar(100), qty int)create table #table2 (forename varchar(100), surname varchar(100), cost int)
insert into #table1 values
('John',1),
('Paul',2),
('Ringo',1)
insert into #table2 values
('John' , 'Smith' , 123),
('John' , 'Jones' , 815 ) ,
('Paul' , 'Smith' , 273),
('Paul' , 'Jones' , 297),
('Ringo' , 'Smith' , 755),
('Ringo' , 'Jones' , 334)
So what I want to do is to return the rows for each name depending upon the Qty value. Like Paul has the Qty value 2, so the query should return two rows for Paul. And for others will get one as the Qty value is one for them.
I tried the following query but gets data of Paul only:
SELECT m.forname, k.SurName, k.Cost FROM Sample2 mINNER JOIN [Sample] k
ON k.ForeName = m.forname
GROUP BY m.forname, m.Qty, k.Cost, k.id, k.SurName
HAVING m.Qty > COUNT(k.ForeName)
Based on the sample data you provided, what should the results set returned look like?
October 19, 2016 at 11:24 am
As Paul has Qty 2, so the query should return 2 rows from table 2 for Paul. Besides, it should return 1 row for John as he has Qty value 1. As well as Ringo too, 1 row.
Forename Surname Cost
-----------------------------
John Jones 815
Paul Jones 297
Paul Smith 273
Ringo Smith 755
October 19, 2016 at 11:26 am
AT-2016 (10/19/2016)
As Paul has Qty 2, so the query should return 2 rows from table 2 for Paul. Besides, it should return 1 row for John as he has Qty value 1. As well as Ringo too, 1 row.
Forename Surname Cost-----------------------------
John Jones 815
Paul Jones 297
Paul Smith 273
Ringo Smith 755
How do you decide which of the two rows in table 2 should be returned for John and Ringo?
October 19, 2016 at 11:31 am
I just want to return number of rows depending upon the value of Qty and order by last inserted rows in table2. In Table1, Paul has Qty 2, so the query should return any two rows for Paul from the table2 depending upon Qty.
October 19, 2016 at 11:41 am
AT-2016 (10/19/2016)
I just want to return number of rows depending upon the value of Qty and order by last inserted rows in table2. In Table1, Paul has Qty 2, so the query should return any two rows for Paul from the table2 depending upon Qty.
Problem, how do you decide which of the two rows for John in #table2 to return and which of the two rows for Ringo in #table2 should be returned? Is this simply a random choice in that you really don't care what value may be returned during each run of the query?
In your sample result set it appears that you want the row with the greater cost. Is this true? If so, how about the following:
create table #table1(name varchar(100), qty int);
create table #table2 (forename varchar(100), surname varchar(100), cost int);
insert into #table1 values
('John',1),
('Paul',2),
('Ringo',1);
insert into #table2 values
('John' , 'Smith' , 123),
('John' , 'Jones' , 815 ) ,
('Paul' , 'Smith' , 273),
('Paul' , 'Jones' , 297),
('Ringo' , 'Smith' , 755),
('Ringo' , 'Jones' , 334);
go
with basedata as (
select
t2.forename
, t2.surname
, t2.cost
, t1.qty
, rn = ROW_NUMBER() over (partition by t1.name order by t2.cost desc)
from
#table1 t1
inner join #table2 t2
on t1.name = t2.forename
)
select
bd.forename
, bd.surname
, bd.cost
from
basedata bd
where
bd.rn <= bd.qty
go
drop table #table1;
drop table #table2;
go
October 19, 2016 at 3:14 pm
In some circumstances, the following may perform better than the ROW_NUMBER version.
SELECT t2.*
FROM #table1 t
CROSS APPLY (
SELECT TOP ( t.qty ) *
FROM #table2 t2
WHERE t2.forename = t.name
ORDER BY t2.cost DESC
) t2
The requirements are that the number of records in the outer table are relatively small, the number of records per forename in the inner table are relatively large, and there exists an index on table2 forename and cost (preferably desc) that includes the surname. (NOTE: that is a single index that meets all three criteria. It does not do any good to have separate indexes on forename and cost regardless of whether either includes the other field or the surname.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply