locked
sql script how to get bigger value from list. RRS feed

  • Question

  • User1248258412 posted

    I've a table name User_detail, 1 user_id can have many level assign.

    how can I set the level sequence in SQL witout setup table, and everytime when i want to display user level; it will always check from the sequence and display the higher value. my sequest is {1, 2, 3A, 3B, 5, AA, AB, 1A, 1B}

    Example: UserA has level 1, 3A, AA, 1A So, when I display, I want to display 1A only for UserA

    Thursday, July 4, 2013 9:31 PM

All replies

  • User1508394307 posted

    Try select top 1 with custom ordering

    Sql server

    select top 1 levelassign
    from user_detail
    where user_id=XXX
    ORDER BY CASE WHEN levelassign = '1A' THEN 0
                  WHEN levelassign = 'AA' THEN 1
                  WHEN levelassign = '3A' THEN 2
                  WHEN levelassign = '1'  THEN 3
                  ELSE levelassign
             END

    Oracle

    ...
    order by case when :levelassign = '1A' then 1 end, 
    case when :levelassign = 'AA' then 2 end

    where in order by clause you can specify your ordering for all levels.

    Another way is to add a new table where you can specify that ordering, e.g. 

    LevelOrder

    Level             Order
    ------------------------
    1A                 0
    1B                 1
    ... 

    and then join that table 

    select top 1 d.levelassign
    from user_detail d, levelorder o
    where d.user_id=XXX
    d.levelassign=o.level
    ORDER BY o.order
    Friday, July 5, 2013 1:41 AM
  • User1248258412 posted

    Hi Smirnov, using Oracle for my script and i try as below but not working; I try to make my own sequence as below and may I know how can I make it when I select max value, it will give me the data 'AA' then if I select min value, it will give me data '3A'

    select max(levelassign) from user_detail
    order by case when levelassign = '2' then 6 end,
    case when levelassign = 'AA' then 9 end, 
     
    case when levelassign = '3A' then 2 end,
    case when levelassign = '1A' then 5 end;

    Sunday, July 7, 2013 9:13 PM
  • User1248258412 posted

    Anyone have idea how to do that in oracle?I try to make my own sequence as below and may I know how can I make it when I select max value, it will give me the data 'AA' then if I select min value, it will give me data '3A'

    select max(levelassign) from user_detail order by case when levelassign = '2' then 6 end, case when levelassign = 'AA' then 9 end,   case when levelassign = '3A' then 2 end, case when levelassign = '1A' then 5 end;

    Monday, July 8, 2013 8:50 PM