September 19, 2018 at 5:15 pm
CREATE TABLE Test (
Name varchar(255),
InvDt varchar(255),
Typeflag integer,
amountI integer,
amountP integer
);
INSERT INTO Test (Name, InvDt, Typeflag,amountI,amountP)
VALUES ('man1', '01/01/2018', 1,9000,6000);
INSERT INTO Test (Name, InvDt, Typeflag,amountI,amountP)
VALUES ('man1', '01/01/2018', 0,300,200);
INSERT INTO Test (Name, InvDt, Typeflag,amountI,amountP)
VALUES ('man2', '01/01/2018', 1,5000,2000);
INSERT INTO Test (Name, InvDt, Typeflag,amountI,amountP)
VALUES ('man2', '01/01/2018', 0,4000,2600);
INSERT INTO Test (Name, InvDt, Typeflag,amountI,amountP)
VALUES ('man2', '02/01/2018', 1,7000,1000);
INSERT INTO Test (Name, InvDt, Typeflag,amountI,amountP)
VALUES ('man2', '03/01/2018', 0,6000,2000);
INSERT INTO Test (Name, InvDt, Typeflag,amountI,amountP)
VALUES ('man3', '01/01/2018', 1,8000,1000);
INSERT INTO Test (Name, InvDt, Typeflag,amountI,amountP)
VALUES ('man3', '01/01/2018', 0,6000,2000);
select * from test
Result expected
man1,'01/01/2018',1,4000,3000
man1,'01/01/2018',0,300,200
man3,'01/01/2018',1,8000,1000
man3,'01/01/2018',0,6000,2000
Hello Experts -
Basically, I need to get the Top 2 records of manufacturers with highest invoiced amount(amountI) even if one of their Typeflag - amount is high
Help much appreciated...
September 19, 2018 at 8:11 pm
This is a possible solution.
with amtsI
as
(
select amountI, name, row_number() over(order by amountI desc) rowNum
from test
)
select *
from test t
inner join amtsI i
on i.name = t.name
where i.RowNum <= 2;
I'm not sure where the 4000, 3000 came from in the first row of your expected output. But the rest of the rows show the correct values.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 20, 2018 at 10:19 am
Thanks for the query, it's my bad on the data.
I see one issue, when the manufacturer man1's amount invoice (amountI) is the highest for both flags then the result is getting duplicated.
let's say the below data
man1,'01/01/2018',0,300,200
becomes
man1,'01/01/2018',0,11000,9000
update test set amountI=11000,amountP=9000 where name='man1' and Typeflag=0
The query gives this below result, but I expect to see the original result
Name InvDt Typeflag amountI amountP amountI name rowNum
man1 01/01/2018 1 9000 6000 11000 man1 1
man1 01/01/2018 1 9000 6000 9000 man1 2
man1 01/01/2018 0 11000 9000 11000 man1 1
man1 01/01/2018 0 11000 9000 9000 man1 2
September 20, 2018 at 11:41 am
I see one issue, when the manufacturer man1's amount invoice (amountI) is the highest for both flags then the result is getting duplicated.
Try this:
with amtsI
as
(
select top 2 *
from (
select Name, amountI, row_number() over(partition by Name order by amountI desc) rowNum
from test
) a
where a.RowNum = 1
order by a.AmountI desc
)
select *
from test t
inner join amtsI i
on i.name = t.name
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 20, 2018 at 11:48 am
This works great! Thank you so much Linksup!
September 20, 2018 at 12:50 pm
misstryguy - Thursday, September 20, 2018 11:48 AMThis works great! Thank you so much Linksup!
Thanks for the feedback!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 22, 2018 at 11:07 am
misstryguy - Thursday, September 20, 2018 10:19 AMThanks for the query, it's my bad on the data.I see one issue, when the manufacturer man1's amount invoice (amountI) is the highest for both flags then the result is getting duplicated.
Try this:
with amtsI
as
(
select top 2 *
from (
select Name, amountI, row_number() over(partition by Name order by amountI desc) rowNum
from test
) a
where a.RowNum = 1
order by a.AmountI desc
)
select *
from test t
inner join amtsI i
on i.name = t.name
Hi ,
Your query is well thought and written . But why not below one.
set statistics io on;
with amtsI
as
(
select top 2 *
from (
select Name, amountI, max(amountI) over( order by amountI desc) rowNum /* removed row number and partition and included max */
from test
) a
--where a.RowNum in ( 1,2)
order by a.AmountI desc
)
select i.Name, InvDt, Typeflag,i.amountI,amountP
from test t
inner join amtsI i
on i.name = t.name
I just removed row number and partition from your query and included max. As partition might slow down the query performance. Kindly let me your thoughts as well.
Saravanan
September 24, 2018 at 6:02 am
misstryguy - Thursday, September 20, 2018 10:19 AMI just removed row number and partition from your query and included max. As partition might slow down the query performance. Kindly let me your thoughts as well.
Max over() is still a window function. Even though it is a bit more efficient than Row_Number, both query plans show 2 scans of the data.
The Max over() suggestion did work with the original data but does not provide the correct result set after the OP modified the data.
You might get your suggested query to work with a couple of tweaks. I have not had time to look to deeply into it!
To really test the possible solutions, a test harness with a proper key/indexes would need to be created.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply