Home > Default > Single Select Output

Single Select Output

November 30Hits:0
Advertisement
Hi,
I want to know whether this is possible or I need to do in a procedure.
I got a table eg.
ID COLOR
1 Yellow
1 Blue
1 Red
2 White
2 Red
3 White
Given an Id I want to output the color result on the basis of this priorities.
1. If yellow exists in any row the output is yellow ,
2. if Blue exists in any row the output is blue,
3. If White exists then the output is white
last is red.
so for e.g the output for ID = 1 should be yellow
Is it possible in a single select statement.
thanks,
Luckys

Answers

here we go.
SQL> create table status(id number, colour varchar2(32))
  2  /
Table created.
SQL> insert into status values(1,'Yellow')
  2  /
1 row created.
SQL> insert into status values(1,'Blue')
  2  /
1 row created.
SQL> insert into status values(1,'Red')
  2  /
1 row created.
SQL> insert into status values(2,'White')
  2  /
1 row created.
SQL> insert into status values(2,'Red')
  2  /
1 row created.
SQL> insert into status values(3,'White')
  2  /
1 row created.
SQL> select * from status where id=1;
        ID COLOUR
         1 Yellow
         1 Blue
         1 Red
SQL> select * from
  2  (select colour
  3  , decode(colour,'Yellow', 1
  4                 ,'Blue', 2
  5                 ,'White', 3
  6                 ,'Red', 4, 0) ratings
  7    from status
  8   where id=1
  9   order by ratings)
10  where rownum=1
11  /
COLOUR                              RATINGS
Yellow                                    1
SQL> select * from
  2  (select colour
  3  , decode(colour,'Yellow', 1
  4                 ,'Blue', 2
  5                 ,'White', 3
  6                 ,'Red', 4, 0) ratings
  7    from status
  8   where id=2
  9   order by ratings)
10  where rownum=1
11  /
COLOUR                              RATINGS
White                                     3
SQL> select * from
  2  (select colour
  3  , decode(colour,'Yellow', 1
  4                 ,'Blue', 2
  5                 ,'White', 3
  6                 ,'Red', 4, 0) ratings
  7    from status
  8   where id=3
  9   order by ratings)
10  where rownum=1
11  /
COLOUR                              RATINGS
White                                     3
SQL>

Read other 6 answers

Tags:

Related Articles

  • Single Select OutputNovember 30

    Hi, I want to know whether this is possible or I need to do in a procedure. I got a table eg. ID COLOR 1 Yellow 1 Blue 1 Red 2 White 2 Red 3 White Given an Id I want to output the color result on the basis of this priorities. 1. If yellow exists in a

  • Oracle query - Merging multiple rows into a single row outputOctober 11

    Hi All, I have to have a multiple row output to be converted into a single row output.My current output looks as follows: ID YR INC_CODE OFFN SCHOOLNO 8006 2002 00175 SC03 12 8006 2002 00175 DC06 12 8006 2002 00175 DC03 12 8006 2002 00175 DC02 12 ID,

  • 2 set of conditions to use in a Single Select query in procedureNovember 30

    Good day I have one procedure, want to use one 2 set of conditions in Where clause like where condition1 < value AND condition 2 > value AND condition 3 = value where condition 1 > value AND condition 2 < value AND condition 4 = value How we s

  • Selection outputNovember 30

    Hi I need small issue regarding selection output for the queries Actually i created some reports all are restricted with employee number so when query runs we have to enter employee number to  view the query .Now for end user point of view there chan

  • A single .swf output file?November 30

    I import my captivate simulations into my Authorware programs. This requires a single .SWF output file. I want to be able to use the skin (border) feature and produce only 1 single file. Can this be done? Anyone have a work-around... Thanks, kshasmit

  • Single Select  O/PNovember 30

    Dear All Having a select query where the output looks as below, as you can see when there is no record for a particular date(01-JUn-12,03-JUN-12) the count will be zero. SELECT TRUNC(updated_date) DATE1 , COUNT(1) COUNT FROM Temp_a WHERE ZU_flag='N'

  • Selecting data from Multiple Partitions in a single select stmt.October 11

    Hi all, My Database is very large & my tables are partitioned. My question is: 1) If my data is spread across multiple partitions, is there any way to select data from multiple partitions in a single query? If we dont mention partition name also it w

  • Looking for a best query for multiple IF Else statement in a single selectOctober 11

    Hi I want to run multiple IF Else statements in a single select SQL, each statement is one SQL operating on the same table, what is the best way to write this select SQL query ? If it is PL/SQL, when i get the result from the first IF statement I wil

  • SSRS report - A single selection dropdown list converted to text boxOctober 11

    Hello everyone, We created a single selection dropdown parameter (City parameter) on a report. The data in this parameter is populated using MDX query. Also, it is filtered based on selection of another single selection dropdown list (Country paramet

  • How to get selected record details when single selection is used in a tableOctober 11

    Hi All, Inside a query region I have created a table using region wizard,for this table I have added a singleSelection Item. What I want is , on selection of a particular record i want to update or view that record in a new page , for this I have add

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.658 s.