June 12, 2013 at 2:05 am
Im confused on why this query is not working..
Data -
vend_i dept tot_wgt
1 1 75
2 2 40
3 4 50
3 5 80
result I needed
vend_i dept
1 1
2 2
3 5
Basically I need largest dept by each vendor which has served more...we can use tot_wgt column to find the large dept/
can anyone please provide a query to get above result...plss.its very urgent
June 12, 2013 at 2:10 am
Hi,
Can you provide the query you already have and we can then offer guidance on where things may be wrong.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
June 12, 2013 at 2:17 am
with SCC_CTE(vend_i, dept, tot_wgt)
as (select 1, 1, 75 union all
select 2, 2, 40 union all
select 3, 4, 50 union all
select 3, 5, 80)
select vend_i, dept from
(select ROW_NUMBER() over (Partition by vend_i order by dept desc) as theRank, vend_i, dept
from SCC_CTE) as a
where theRank = 1
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
June 12, 2013 at 2:39 am
This is the query i was trying to do...
i was trying to get vend_i and dept which has max(wgt) when grouped by vend_i and dept
select vend_i,dept,max(tot_wgt)
from
table1
group by vend_i,dept
June 12, 2013 at 2:43 am
Have a read of this: http://msdn.microsoft.com/en-us/library/ms189461.aspx
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
June 12, 2013 at 2:52 am
Abu Dina (6/12/2013)
with SCC_CTE(vend_i, dept, tot_wgt)as (select 1, 1, 75 union all
select 2, 2, 40 union all
select 3, 4, 50 union all
select 3, 5, 80)
select vend_i, dept from
(select ROW_NUMBER() over (Partition by vend_i order by dept desc) as theRank, vend_i, dept
from SCC_CTE) as a
where theRank = 1
Abu,
Nice query although slightly flawed if you switch the tot_wgt around where so dept 4 = 80 and dept 5 = 50, you get the wrong result. however with a slight tweak, it will work, simply switch the order by dept to order by tot_wgt.
with SCC_CTE(vend_i, dept, tot_wgt)
as (select 1, 1, 75 union all
select 2, 2, 40 union all
select 3, 4, 80 union all
select 3, 5, 50)
select vend_i, dept from
(select ROW_NUMBER() over (Partition by vend_i order by tot_wgt desc) as theRank, vend_i, dept
from SCC_CTE) as a
where theRank = 1
_________________________________________________________________________
SSC Guide to Posting and Best Practices
June 12, 2013 at 10:15 am
bharathimc02 (6/12/2013)
This is the query i was trying to do...i was trying to get vend_i and dept which has max(wgt) when grouped by vend_i and dept
select vend_i,dept,max(tot_wgt)
from
table1
group by vend_i,dept
The reason that this did not work is because it is already grouped by both vendor and department. Thus you will get all vendor and department combinations, and the max tot_wgt for each of them (and since I don't think there are duplicates with same vendor and dept, the max effectively does nothing).
What you need to do is first identify which rows have the highest tot_wgt for each vendor, and then find the dept for those rows.
One question not covered by your sample data, what happens when two or more depts within a vendor are TIED for the highest tot_wgt? Jason/Abu's solution would simply take one of these and ignore the others. You'll need a slightly different query otherwise.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply