Home » SQL & PL/SQL » SQL & PL/SQL » Select Query for Column Name of Largest Value
Select Query for Column Name of Largest Value [message #214757] Wed, 17 January 2007 23:06 Go to next message
jameswise
Messages: 8
Registered: August 2006
Junior Member
I have a table with the following fields:
c_machine
c_count01
c_count02
c_count03
c_count04
c_count05

The c_count0x fields are numeric fields. I want to run a query against a specific c_machine record (this is the unique primary key for my table) and return the name of the count column with the largest value. For example, take the following record:

c_machine = 001
c_count01 = 0
c_count02 = 10
c_count03 = 0
c_count04 = 50
c_count05 = 5

So, if I ran the query for machine 001 I would want a returned value of “c_count04” as this is the count field with the largest value for this record.

I’m sure there must be some way to do this with a select query but I haven’t figured out a way to do this yet. Any suggestions would be greatly appreciated!
Re: Select Query for Column Name of Largest Value [message #214762 is a reply to message #214757] Wed, 17 January 2007 23:54 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Use GREATEST.
Re: Select Query for Column Name of Largest Value [message #214773 is a reply to message #214762] Thu, 18 January 2007 00:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
So, extending that advice to a solution, we get:
SELECT 
  c_machine
, DECODE(
    GREATEST(c_count01,c_count02,c_count03,c_count04,c_count05)
  , c_count01, 'C_COUNT01'
  , c_count02, 'C_COUNT02'
  , c_count03, 'C_COUNT03'
  , c_count04, 'C_COUNT04'
  , c_count05, 'C_COUNT05'
  ) AS greatest_col
FROM tab


Ross Leishman
Re: Select Query for Column Name of Largest Value [message #214881 is a reply to message #214773] Thu, 18 January 2007 06:20 Go to previous messageGo to next message
jameswise
Messages: 8
Registered: August 2006
Junior Member
Thank you! This is exactly what I wanted. I tried it out and everything is working great!
Re: Select Query for Column Name of Largest Value [message #214965 is a reply to message #214757] Thu, 18 January 2007 15:37 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
That will work, but if you have 2 (or more) columns with the same value, it will return only the first. Is this what you want?
Re: Select Query for Column Name of Largest Value [message #214983 is a reply to message #214965] Thu, 18 January 2007 18:43 Go to previous message
jameswise
Messages: 8
Registered: August 2006
Junior Member
Bill B wrote on Thu, 18 January 2007 16:37
That will work, but if you have 2 (or more) columns with the same value, it will return only the first. Is this what you want?


In this case, it will be fine if only the first field name is returned in case of two or more of the largest values are the same. Thanks for pointing this out though.
Previous Topic: Generate Report
Next Topic: How can one keep a history of PL/SQL code changes?
Goto Forum:
  


Current Time: Sun Dec 04 13:00:51 CST 2016

Total time taken to generate the page: 0.13171 seconds