0

I have a database table called 'dbTable' with 7 columns which are : name, score1, score2, score3, score4, score5, 2nd_lowest. Each student has a record with all score columns filled. 

eg//
If james had 'score1' =  40, 'score2' = 70, 'score3' = 36, 'score4' = 60, 'score5' =  50.

How do I write an sql query to select 40 as the second smallest score value and update it into 2nd lowest column.
so that the remaining records can fill '2nd_lowest' as below:

| name | score1 | score2 |score3 | score4 |score5 | 2nd_lowest |
-------------------------------------------------------------------------------------
| jimy    |   40.0  |   70.0   |  36.0   |   60.0   |   50.0  |   40.0          |
| kane   |   20.0  |   90.0   |  72.0   |   10.0   |   30.0  |   20.0          |
| mimy  |   50.0  |   80.0   |  76.0   |   30.0   |   50.0  |   50.0          |

am using an sqlite3 dbms
all help will be well appreciated. thank you

jarlh
  • 42,561
  • 8
  • 45
  • 63
Rodney Nart
  • 121
  • 11
  • 1
    So to understand the question, are you saying you want to show scores for all students, but with their scores always in order left to right, say? or just for that one student? – Toby Nov 20 '17 at 14:16
  • for all the students @Toby.. each student has score records for each 'score column'. so i want a query that can select only the second lowest score among all the score columns and return the value into the '2nd_lowest' column of that student. it should happen for all students please. – Rodney Nart Nov 20 '17 at 14:22
  • 1
    This doesn't seem like a good design, to achieve it you are going to need a trigger. But since the data that you need is a derivative you don't really have to store it – Antho Christen Nov 20 '17 at 14:26
  • I agree that your approach seems flawed, and could cause much more hassle later. I don't understand why you would store the same value twice in each row, just to show which is the 2nd lowest? Why not just query the db and look for the 2nd lowest? However, to answer the actual question, you could do it through a trigger, as anchreg has said. Or you could try a CASE statement, or maybe some other method. It would be easier to do it in code, than SQL. Try looking here https://stackoverflow.com/questions/368351/whats-the-best-way-to-select-the-minimum-value-from-several-columns would that work? – Toby Nov 20 '17 at 14:33
  • select min(score1,score2,score3,score4,score5) is able to find the lowest value for the student among all the five columns, and do same for all the students in the table...i just hope there's a way to find the second lowest @anchreg – Rodney Nart Nov 20 '17 at 14:35
  • Can you have two (or more) equal scores for the same student? – PM 77-1 Nov 20 '17 at 14:40
  • @Rodney - there is a way to find the second lowest - CASE. See the link I posted. Unfortunately there isn't a secondlowest method in sql. – Toby Nov 20 '17 at 14:47

3 Answers3

1

Hmm. I would rethink your DB design if at all possible. But if you can't then this should get you going in the correct direction. Yes, it is horrible, but then the data table is a tricky one too!

select name,min(s1,s2,s3,s4,s5) as second_least
from
(
 select dt.name,
 case when score1=min(score1,score2,score3,score4,score5) then max(score1,score2,score3,score4,score5) else score1 end as s1,
 case when score2=min(score1,score2,score3,score4,score5) then max(score1,score2,score3,score4,score5) else score2 end as s2,
 case when score3=min(score1,score2,score3,score4,score5) then max(score1,score2,score3,score4,score5) else score3 end as s3,
 case when score4=min(score1,score2,score3,score4,score5) then max(score1,score2,score3,score4,score5) else score4 end as s4,
 case when score5=min(score1,score2,score3,score4,score5) then max(score1,score2,score3,score4,score5) else score5 end as s5
 from  dbTable dt
) t;
Tom Mac
  • 9,693
  • 3
  • 25
  • 35
  • ohh @Toby i read the CASE statment link you provided...am yet to try it out. i appreciate all the help. thank you. – Rodney Nart Nov 20 '17 at 15:10
  • 'Java.sql.SQLException: only a single result allowed for a SELECT that is part of an expression' is the exception error i get @Tom Mac – Rodney Nart Nov 20 '17 at 15:42
1

This assumes you can use some Java:

What about storing all the scores for a person from db into an array list and then iterating through it to find the second lowest value then an insert for the output of that value to 2nd_lowest column for that person?

Something like this to find the second lowest from array, you can adapt to use values from DB of course:

double[] elements = {40.0  70.0  36.0, 60.0, 50.0};
    double smallest = Integer.MAX_VALUE;
    double secondSmallest = Integer.MAX_VALUE;
    for (double i = 0; i < elements.length; i++) {
    if (elements[i] < smallest) {
            secondSmallest = smallest;
            smallest = elements[i];
        } else if (elements[i] < secondSmallest) {
            secondSmallest = elements[i];
        }

    }

Hopefully gives a small bit of help

Cowboy Farnz
  • 329
  • 2
  • 11
0
with num(x) as(
  values(1),(2),(3),(4),(5)
),
new as(
 select name, case x when 1 then score1  when 2 then score2  when 3 then score3
                     when 4 then score4  else score5 end val
   from test, num
  where val!=min(score1,score2,score3,score4,score5)
)
update test set "2nd_lowest"=
   (select min(val) from new where new.name=test.name)

Demo on sqlfiddle.com

For the older version of SQlite3 that does not support CTE:

update test set "2nd_lowest"=(
  select min(val)
    from (
      select name, case x when 1 then score1 when 2 then score2
                     when 3 then score3 when 4 then score4
                     else score5 end val
        from test T, (select 1 as x union all values(2),(3),(4),(5)) X
       where val!=min(score1,score2,score3,score4,score5)
         and T.name=test.name
    ) 
)
Mike
  • 1,985
  • 1
  • 8
  • 14
  • thanks @Mike but sqlite3 dbms is not accepting the query statment because its starting with "with".. i undrstand its an actual mysql query statement. is there a way to write the statement to be accepted in sqlite 3 dbms – Rodney Nart Nov 20 '17 at 21:21
  • @RodneyNart Updated, try test it – Mike Nov 20 '17 at 21:39
  • thanks for your efforts @Mike ..i still get exceptions around "from test T,"which is in the query..the exception is 'Java.sql.SQLException: near “test”:syntax error'.. i try working out that area but still get exceptions..can you kindly please help me out.. thank you very much. – Rodney Nart Nov 21 '17 at 00:54
  • @RodneyNart Rename "test" to you table. What is your version of Sqlite ? – Mike Nov 21 '17 at 06:31
  • yes i did that.. am using sqlite manager.. and the db extension is sqlite3. so my database name is db.sqlite3 @Mike. my table name is jh2. so the exception error is 'Java.sql.SQLException: near “jh2”:syntax error' bro. – Rodney Nart Nov 21 '17 at 08:35
  • @RodneyNart Please tell the exact version number. The version consists of 3 numbers. I am currently testing the requests on version 3.10.2 – Mike Nov 21 '17 at 08:41
  • I have version 4.6.1... but i also have another which is not yet registered.. so its version is not stated..but its quite an old manager. used 3 years now but it supports sqlite3 and its very good. i dont know which one i have to use for your answer,bro – Rodney Nart Nov 21 '17 at 09:10
  • @RodneyNart First number in version must be 3 (for sqlite3). what returns `select sqlite_version();` ? – Mike Nov 21 '17 at 09:15
  • 3.6.14.2 is the version @Mike – Rodney Nart Nov 21 '17 at 09:25
  • @RodneyNart I can not find the old version for testing :( Try to test the parts of the query yourself. For example, whether the comma is supported instead of "JOIN", Is "values" supported, etc ... – Mike Nov 21 '17 at 10:04
  • but does it work on version 3.10.2. if it does, will find it and download. i can send you the setup of the sqlite Manager i have if its not a bother to you too @Mike. thanks – Rodney Nart Nov 21 '17 at 10:53