LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 06-01-2005, 12:02 PM   #1
Ephracis
Senior Member
 
Registered: Sep 2004
Location: Sweden
Distribution: Ubuntu, Debian
Posts: 1,109

Rep: Reputation: 49
sql: highest value, comparing fields


I want an sql query that shows me the field with the highest value. I have a table like this:

uhost varchar(50)
nick varchar(30)
val1 int
val2 int
val3 int
...
val8 int

Now, I need a query that shows the nick, the total of all values and the highest of the different fields. I have already fetched the total by adding them all (val1 + val2 + val3), maybe there is a better way?

Regards.
 
Old 06-01-2005, 01:21 PM   #2
rokka
Member
 
Registered: Feb 2004
Location: .se
Distribution: Ubuntu, debian
Posts: 124

Rep: Reputation: 15
What database are you using?
Try this:
select nick, max(val1, val2, val3...) as maxvalue, (val1+val2val3...) as sumvalues
from...

I must point out that when you have to face this kind of problem it is usually a result of poor database design. Keeping the valX-values in a separate table would probably be better. But them again - I have no idea what these values represent so who am I to judge!

Edit: Hej föresten :-)

Last edited by rokka; 06-01-2005 at 01:22 PM.
 
Old 06-01-2005, 03:39 PM   #3
Ephracis
Senior Member
 
Registered: Sep 2004
Location: Sweden
Distribution: Ubuntu, Debian
Posts: 1,109

Original Poster
Rep: Reputation: 49
No, max seems to only be designed to take one field into it and return the post with the highest value of that field, not what I am looking for.
 
Old 06-01-2005, 03:48 PM   #4
david_ross
Moderator
 
Registered: Mar 2003
Location: Scotland
Distribution: Slackware, RedHat, Debian
Posts: 12,047

Rep: Reputation: 64
I think GREATEST should work:
GREATEST(val1,val2,val3) as greatest
 
Old 06-01-2005, 03:50 PM   #5
Tinkster
Moderator
 
Registered: Apr 2002
Location: in a fallen world
Distribution: slackware by choice, others too :} ... android.
Posts: 22,974
Blog Entries: 11

Rep: Reputation: 879Reputation: 879Reputation: 879Reputation: 879Reputation: 879Reputation: 879Reputation: 879
I agree with rokka - that's a design-flaw.
The only way around this will be programmatical,
you will need to script a solution or build the
table(s) differently.

[edit]
GREATEST is not in the SQL standards, and the only
database I know of to use it is oracle ;}
[/edit]



Cheers,
Tink

Last edited by Tinkster; 06-01-2005 at 03:52 PM.
 
Old 06-01-2005, 04:25 PM   #6
david_ross
Moderator
 
Registered: Mar 2003
Location: Scotland
Distribution: Slackware, RedHat, Debian
Posts: 12,047

Rep: Reputation: 64
Quote:
Originally posted by Tinkster
the only database I know of to use it is oracle ;}
And MySQL it would seem
http://dev.mysql.com/doc/mysql/en/co...operators.html

I didn't realise it wasn't part of the standards though so it may not be of any use.
 
Old 06-01-2005, 05:28 PM   #7
Ephracis
Senior Member
 
Registered: Sep 2004
Location: Sweden
Distribution: Ubuntu, Debian
Posts: 1,109

Original Poster
Rep: Reputation: 49
Quote:
Originally posted by rokka
What database are you using?
Try this:
select nick, max(val1, val2, val3...) as maxvalue, (val1+val2val3...) as sumvalues
from...

I must point out that when you have to face this kind of problem it is usually a result of poor database design. Keeping the valX-values in a separate table would probably be better. But them again - I have no idea what these values represent so who am I to judge!

Edit: Hej föresten :-)
I am using MySQL.

I am aware of that the design is kind of poor but I have been trying to think of any other way to sort things out but every other solution would create other problems. But since the table is not that big really it is not a big cost to have it as it is right now, it is not a table that will grow much either. So I guess it is ok by now.

Hallå, där.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Distros: Highest number of packages ? Youssef_C Linux - Distributions 5 11-02-2005 06:32 PM
Highest Paid Stream In Computers emailssent General 16 06-26-2005 09:06 PM
Setting the highest display resolution artemis Fedora 3 06-16-2004 02:32 AM
top 10 highest frequency x2000koh Programming 4 05-19-2003 09:37 AM
SQL query, comparing tables ngomong Programming 3 07-07-2002 07:44 PM


All times are GMT -5. The time now is 01:57 PM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration