SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple MAX Query not working


Simple MAX Query not working

Author
Message
bharathimc02
bharathimc02
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 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
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2125 Visits: 3232
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
Abu Dina
Abu Dina
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 Visits: 3323
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
bharathimc02
bharathimc02
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 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
Abu Dina
Abu Dina
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1263 Visits: 3323
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
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2125 Visits: 3232
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
Nevyn
Nevyn
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1538 Visits: 3149
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search