Getting max value of same record

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)

Getting max value of same record

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-08-22 : 13:50:37

What's the easiest way to accomplish this.

I have the following columns of a table

MyRecordID,Rate1,Rate2,Rate3,Rate4,Rate5

I need to take the Maximum out of those 5 rates.

So

Select MyRecordID,HIGHESTVALUE(RATE1,RATE2,RATE3,RATE4,RATE5)
from Mytable

Obviously HIGHESTVALUE is a made up function, but is there a easy way to accomplish this with out case statments wrapped into case statements?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 13:55:33

Is this the new exam question?

SELECT MyRecordID, Date1, Date2, Date3, Date4, ( SELECT MAX(p) FROM
(SELECT Rate1 AS p UNION ALL SELECT Rate2 UNION ALL SELECT Rate3 UNION ALL SELECT Rate4 UNION ALL SELECT Rate5) AS d)
FROM MyTable

E 12°55'05.25"
N 56°04'39.16"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-08-22 : 13:59:53

No, more like a boss get's mad if I don't have his query working perfect in next 30 minute question :)

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-22 : 14:46:19

ummmm....are you still with us?

Tell said boss to buy a book on data Normalization

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-22 : 14:53:59

[code]
DECLARE @x table(Col1 int IDENTITY(1,1), Rate1 int, Rate2 int, Rate3 int, Rate4 int, Rate5 int)
INSERT INTO @x(Rate1, Rate2, Rate3, Rate4, Rate5)
SELECT 1,2,3,4,5 UNION ALL
SELECT 5,4,3,2,1 UNION ALL
SELECT 4,3,5,1,2

SELECT a.Col1, MAX_Rate
FROM @x a JOIN (SELECT Col1, MAX(Rate) AS MAX_Rate FROM (
SELECT Col1, Rate1 AS Rate FROM @x UNION ALL
SELECT Col1, Rate2 AS Rate FROM @x UNION ALL
SELECT Col1, Rate3 AS Rate FROM @x UNION ALL
SELECT Col1, Rate4 AS Rate FROM @x UNION ALL
SELECT Col1, Rate5 AS Rate FROM @x) AS XXX
GROUP BY Col1) AS b
ON a.Col1 = b.Col1

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-22 : 14:54:39

MIN/MAX Across Multiple Columns
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 15:15:27

No need to self-join.

quote:

Originally posted by X002548

SELECT Col1, MAX(Rate) AS MAX_Rate FROM (
			SELECT Col1, Rate1 AS Rate FROM @x UNION ALL
			SELECT Col1, Rate2 AS Rate FROM @x UNION ALL
			SELECT Col1, Rate3 AS Rate FROM @x UNION ALL
			SELECT Col1, Rate4 AS Rate FROM @x UNION ALL
			SELECT Col1, Rate5 AS Rate FROM @x) AS XXX
		GROUP BY Col1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 15:21:49

Interesting Brett...

Here is your query plan (89% of batch and 15 reads)

  |--Stream Aggregate(GROUP BY:([Union1020]) DEFINE:([Expr1022]=MAX([Union1021])))
       |--Sort(ORDER BY:([Union1020] ASC))
            |--Concatenation
                 |--Table Scan(OBJECT:(@x))
                 |--Table Scan(OBJECT:(@x))
                 |--Table Scan(OBJECT:(@x))
                 |--Table Scan(OBJECT:(@x))
                 |--Table Scan(OBJECT:(@x))

And here is mine (11% of batch and 3 reads)

  |--Compute Scalar(DEFINE:([Expr1007]=[Expr1005]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([Rate1], [Rate2], [Rate3], [Rate4], [Rate5]))
            |--Table Scan(OBJECT:(@x))
            |--Stream Aggregate(DEFINE:([Expr1005]=MAX([Union1004])))
                 |--Constant Scan(VALUES:(([Rate1]),([Rate2]),([Rate3]),([Rate4]),([Rate5])))

My query

SELECT Col1, ( SELECT MAX(p) FROM
(SELECT Rate1 AS p UNION ALL SELECT Rate2 UNION ALL SELECT Rate3 UNION ALL SELECT Rate4 UNION ALL SELECT Rate5) AS d)
FROM @x

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-22 : 15:29:05

huh...I didn't expect that would work

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-08-22 : 16:11:20

quote:
Originally posted by X002548

ummmm....are you still with us?

Tell said boss to buy a book on data Normalization

I'm not sure if I understand the response.

Here's a little more background info:

Rate1 - 5 represents a individual weekly rate which we are provided by a customer (Week 1 is Rate 1, week 2 is rate 2 etc...) in the form of a 20 page RTF report we get each month.

I wrote some code to parse this file by litterally going through each line of the RTF and breaking it down creating the record info all into seperate columns in the table as I mentioned (This includes rate1-5).

Peso's method works very well, so I'm not sure if there is anything to normalize here?

Please let me know how you would suggest to do this.

Are you saying to create seperate tables to contain the week/rate information. I can see how this may be benefitial with 1000's of records, but I am dealing with a max of 500 records each month. Is it worth re-dueing this methodology?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 16:18:14

[code]-- The denormalized way
declare @x table (col int identity(1, 1), rate1 int, rate2 int, rate3 int, rate4 int, rate5 int)

insert @x
select 1, 2, 3, 4, 5 union all
select 5, 4, 3, 2, 1 union all
select 4, 3, 5, 1, 2

SELECT Col,
(SELECT MAX(p) FROM (SELECT Rate1 AS p UNION ALL SELECT Rate2 UNION ALL SELECT Rate3 UNION ALL SELECT Rate4 UNION ALL SELECT Rate5) AS d) AS MaxRate
FROM @x

-- The normalized way
DECLARE @NewDesign TABLE (MyID INT, MyWeek INT, MyRate INT)

INSERT @NewDesign
SELECT 1, 1, 1 UNION ALL
SELECT 1, 2, 2 UNION ALL
SELECT 1, 3, 3 UNION ALL
SELECT 1, 4, 4 UNION ALL
SELECT 1, 5, 5 UNION ALL
SELECT 5, 1, 5 UNION ALL
SELECT 5, 2, 4 UNION ALL
SELECT 5, 3, 3 UNION ALL
SELECT 5, 4, 2 UNION ALL
SELECT 5, 5, 1 UNION ALL
SELECT 4, 1, 4 UNION ALL
SELECT 4, 2, 3 UNION ALL
SELECT 4, 3, 5 UNION ALL
SELECT 4, 4, 1 UNION ALL
SELECT 4, 5, 2

SELECT MyID,
MAX(MyRate) AS MaxRate
FROM @NewDesign
GROUP BY MyID
ORDER BY MyID[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 16:28:03

This is how you can normalize your present table

-- The denormalized way
declare @x table (col int identity(1, 1), rate1 int, rate2 int, rate3 int, rate4 int, rate5 int)

insert	@x
select	1, 2, 3, 4, 5 union all
select	5, 4, 3, 2, 1 union all
select	4, 3, 5, 1, 2

select * from @x

-- Normalize the data
SELECT	Col, 1 AS MyWeek, Rate1 AS MyRate FROM @x UNION ALL
SELECT	Col, 2, Rate2 FROM @x UNION ALL
SELECT	Col, 3, Rate3 FROM @x UNION ALL
SELECT	Col, 4, Rate4 FROM @x UNION ALL
SELECT	Col, 5, Rate5 FROM @x
ORDER BY Col, MyWeek

E 12°55'05.25"
N 56°04'39.16"

pootle_flump

1064 Posts

Posted - 2007-08-22 : 17:58:54

First normal form:
http://www.tonymarston.net/php-mysql/database-design.html
http://r937.com/relational.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources