Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Simple MAX Query not working Expand / Collapse
Author
Message
Posted Wednesday, June 12, 2013 2:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:23 AM
Points: 10, Visits: 57
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
Post #1462493
Posted Wednesday, June 12, 2013 2:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 870, Visits: 2,389
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
Post #1462494
Posted Wednesday, June 12, 2013 2:17 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
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

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
Post #1462495
Posted Wednesday, June 12, 2013 2:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 2:23 AM
Points: 10, Visits: 57
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
Post #1462502
Posted Wednesday, June 12, 2013 2:43 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 3:26 AM
Points: 708, Visits: 3,287
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

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
Post #1462503
Posted Wednesday, June 12, 2013 2:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 870, Visits: 2,389
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
Post #1462507
Posted Wednesday, June 12, 2013 10:15 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 8:23 AM
Points: 625, Visits: 2,128
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.
Post #1462712
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse