Tricky Query Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Tricky Query Question

Author

Topic

upshon
Starting Member

3 Posts

Posted - 2011-01-07 : 02:18:24

Hi

I am trying to select the row with the Highest value in Col1 AND the Highest value in Col2 and cant figure out how to do it, hope some one can help.

Assume I have the following random set of data

Col1, Col2
1, 5
1, 9
3, 6
4, 6
9, 1

The Max value in Col1 is 9 in row 5
The Max value in Col2 is 9 in row 2

The Row I am after is row 4 (4, 6) as it has the highest combination of values.

I cant add Col1 to Col2 and check for the highest result as that would give me 3 rows.

Row 2 (1 + 9) = 10
Row 4 (4 +6) = 10
Row 5 (9 + 1) = 10

Thanks

Mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-07 : 02:25:06

how do you decide to choose Row 4 over Row 2 or 5 ?

KH
[spoiler]Time is always against us[/spoiler]

upshon
Starting Member

3 Posts

Posted - 2011-01-07 : 02:34:47

It has the highest not max value across both fields.

Perhaps a better explanation of what I am after is I am trying to find out what customer has the highest balance outstanding as well as the longest period outstanding.

Cust#, BalOutstanding, DaysOutstanding

1, $100, 10
2, $50, 50
3, $10, 100

The customer with the highest outstanding balance is Cust 1
The Customer with the highest number of days outstanding is Cust 3

Where Cust 2 is the one I am after

Hope that makes more sense

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-01-07 : 03:21:15

will this do ?


data as
(
	select	*, 
		c1 = row_number() over (order by BalOS desc, DayOS desc),
		c2 = row_number() over (order by DayOS desc, BalOS desc)
	from	yourtable
)
select	*
from	data
where	c1	<> 1
and	c2	<> 1
order by BalOS + DayOS desc

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-07 : 11:01:12

Here is another way to write the query:

INSERT @T VALUES
(1, 5),
(1, 9),
(3, 6),
(4, 6),
(9, 1)

select	*
from	@t
where	Col1 <> (SELECT MAX(col1) FROM @T)
and	col2 <> (SELECT MAX(col2) FROM @T)
order by Col1 + Col2 desc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-07 : 13:15:04

[code]declare @sample table (col1 int, col2 int)

insert @sample
select 1, 5 union all
select 1, 9 union all
select 3, 6 union all
select 4, 6 union all
select 9, 1

select top(1) with ties *
from @sample
order by col1 * col2 desc[/code]

N 56°04'39.26"
E 12°55'05.63"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-07 : 13:21:53

quote:
Originally posted by upshon

It has the highest not max value across both fields.

Perhaps a better explanation of what I am after is I am trying to find out what customer has the highest balance outstanding as well as the longest period outstanding.

Cust#, BalOutstanding, DaysOutstanding

1, $100, 10
2, $50, 50
3, $10, 100

The customer with the highest outstanding balance is Cust 1
The Customer with the highest number of days outstanding is Cust 3

Where Cust 2 is the one I am after

Hope that makes more sense

Not really. How do you decide? Assuming you fixed your sample data so that BalOutstanding + DaysOutstanding were the same. How do you pick? WHat's the LOGIC?
- If there is only one, then pick that one (obviously).
- If there are 2 which one do you pick?
- If there are 3 which one do you pick? Sounds like you want to middle one, based on BalOutstanding being less than the max BalOutstanding and less than the max DaysOutstanding of the three that qualified.
- If there are 4 whcih do you pick?
- etc..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources