SQL Absolute value across columns

Question!

I have a table that looks something like this:

word        big   expensive   smart   fast

dog         9     -10         -20     4
professor   2     4           40      -7
ferrari     7     50          0       48
alaska      10    0           1       0
gnat        -3    0           0       0

The + and - values are associated with the word, so professor is smart and dog is not smart. Alaska is big, as a proportion of the total value associated with its entries, and the opposite is true of gnat.

Is there a good way to get the absolute value of the number farthest from zero, and some token whether absolute value =/= value? Relatedly, how might I calculate whether the results for a given value are proportionately large with respect to the other values? I would write something to format the output to the effect of: "dog: not smart, probably not expensive; professor smart; ferrari: fast, expensive; alaska: big; gnat: probably small." (The formatting is not a question, just an illustration, I am stuck on the underlying queries.)

Also, the rest of the program is python, so if there is any python solution with normal dbapi modules or a more abstract module, any help appreciated.

By : unmounted


Answers

You can just use this is in the Username parameter.

admin

By : Thorsten


Asking the question helped clarify the issue; here is a function that gets more at what I am trying to do. Is there a way to represent some of the stuff in ΒΆ2 above, or a more efficient way to do in SQL or python what I am trying to accomplish in show_distinct?

#!/usr/bin/env python

import sqlite3

conn = sqlite3.connect('so_question.sqlite')
cur = conn.cursor()

cur.execute('create table soquestion (word, big, expensive, smart, fast)')
cur.execute("insert into soquestion values ('dog', 9, -10, -20, 4)")
cur.execute("insert into soquestion values ('professor', 2, 4, 40, -7)")
cur.execute("insert into soquestion values ('ferrari', 7, 50, 0, 48)")
cur.execute("insert into soquestion values ('alaska', 10, 0, 1, 0)")
cur.execute("insert into soquestion values ('gnat', -3, 0, 0, 0)")

cur.execute("select * from soquestion")
all = cur.fetchall()

definition_list = ['word', 'big', 'expensive', 'smart', 'fast']

def show_distinct(db_tuple, def_list=definition_list):
    minimum = min(db_tuple[1:])
    maximum = max(db_tuple[1:])
    if abs(minimum) > maximum:
    	print db_tuple[0], 'is not', def_list[list(db_tuple).index(minimum)]
    elif maximum > abs(minimum):
    	print db_tuple[0], 'is', def_list[list(db_tuple).index(maximum)]
    else:
    	print 'no distinct value'

for item in all:
    show_distinct(item)

Running this gives:

    dog is not smart
    professor is smart
    ferrari is expensive
    alaska is big
    gnat is not big
    >>> 
By : unmounted


abs value fartherest from zero:

select max(abs(mycol)) from mytbl

will be zero if the value is negative:

select n+abs(mycol)
  from zzz
 where abs(mycol)=(select max(abs(mycol)) from mytbl);


This video can help you solving your question :)
By: admin