Maximum Row Value - Offset and Match equivalent? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Maximum Row Value - Offset and Match equivalent?

Author

Topic

joelwills
Starting Member

4 Posts

Posted - 2005-04-05 : 08:51:23

Hi,

I'm trying to do the equivalent of an Excel Offset and Match in SQL Server. For example, I have a table below:

Person A B C D E Highest
John 0 0 0 1 0 D
Dave 0 0 0 0 0 A
Jack 0 0 2 0 3 E
Jill 0 0 0 0 3 E

and wish to bring back the column name with the highest value for each person in the final column "Highest". Or at least the highest value in the row in the last column! Any ideas as it's proving difficult? I know there are issues where there are two values the same, ideally I'd like to get the group names of the top three scores.

Thanks in advance.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-05 : 09:16:53

SQL Server and relational datbases in general are completely different from MS Excel. Try not to even really compare the two, to be honest.

In SQL Server, data such as this should be stored in multiple rows, not columns in a table similiar to this:

Person, Type, Value
John, 'A', 0
John, 'B', 0
John, 'C', 0
...
Jack, 'C', 2

except with more descriptive column names of course. Then, in SQL, you'd say:

select A.Name, A.Type, A.Value
From YOurTable A
Where Value = (Select Max(Value) from YourTable B on A.Name = B.Name)

or something along those lines. It's a whole 'nother ball game with a relational database compared to spreadsheets and it really takes a different perspective to move from one to the other.

- Jeff

joelwills
Starting Member

4 Posts

Posted - 2005-04-05 : 11:40:19

Thanks for your reply Jeff. Unfortunately I have a few million rows already and over 20 columns so transforming it into what you have suggested is a little tricky.

Thanks anyway.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-05 : 11:52:49

I strongly recommend reading this:

http://www.datamodel.org/NormalizationRules.html

Hopefully you can find the time to fix the database before it gets too out of control. The benefits of a well designed database are too many to mention, but most importantly speed, flexibility, data integrity, easier data manipulation, and faster and much shorter SQL for returning data and calculations.

It's funny, though, it seems no one ever has time to learn new techniques and best practices for doing things and to fix things up, but they always have time to write work-arounds and work weekends and nights struggling to get their existing designs to work with band-aids and writing 5 times as much code as they need to.

(not you, personally, of course -- just a general statement ...)

- Jeff

joelwills
Starting Member

4 Posts

Posted - 2005-04-05 : 12:06:00

Thanks for that - don't worry it wasn't my database I wouldn't have built it like that!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-05 : 12:11:28

Your only option, it seems, is to write a LOOOOOONG case statment like this:


Select 
     Name,
     CASE WHEN A > B and A > C and A > D and A > E then 'A'
     WHEN B > C and B > D and B > E then 'B'
     WHEN C > D and C > E then 'C'
     WHEN D > E then 'D'
     ELSE 'E' END as Highest
From
     YourTable

to get the highest value of all rows for the last column, you can use the query I provided in my first post, slightly modified:


select 
  A.Name, A.ColumnE
From 
  YOurTable A
Where 
  ColumnE = (Select Max(ColumnE) from YourTable B on A.Name = B.Name)

something like that....

- Jeff

joelwills
Starting Member

4 Posts

Posted - 2005-04-05 : 12:33:05

In the end I went for:
select person, A as Num, A as Segment
into new_table
from old_table
go
insert into new_table
select Person, B, B from old_table

and so on, it has done it well. Thanks for all the advice

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-05 : 12:39:23

that's a good idea, it will normalize your data ... good call !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources