Home » SQL & PL/SQL » SQL & PL/SQL » Sql Query with Mutiple columns
Sql Query with Mutiple columns [message #572801] Mon, 17 December 2012 14:41 Go to next message
sandhoter
Messages: 1
Registered: December 2012
Location: chicago
Junior Member
Table-Name

ID Status description Tracking ID
1 Strat Frog 1
2 Start Dog 2
3 Process Frog 1
4 Completed Dog 2
5 Start Rabbit 3
6 Error Frog 1
7 Stop Rabbit 3
8 Start Elephant 4
9 process Elephant 4
10 Start Human 5
11 Stop Human 5
12 Start Butterfly 6
13 completed Butterfly 6
14 start lion 7
15 error lion 8
16 complted lion 8
17 start tiger 9
18 error tiger 9


select * from Table-Name where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss')
And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')And not description in (Select * from Table-Name where Status like ('%Complete%' or Status like '%stop%') and description in (Select description from Table-Name where Status Like '%start%'));

Result should be " Frog and Elephant and tiger"

Start of every record(descrpition --status is Start)
End of every record ( status is stop or done or completed)
status process is in btwn (their will be mulitple records with name s//y to process...ie. process 1 ...process 2...process 3 )


Note:

tracking IDs may change up on error

Thank you ,

It helps me a lot ...Thank you
  • Attachment: Document.txt
    (Size: 0.64KB, Downloaded 44 times)

[Updated on: Mon, 17 December 2012 14:46]

Report message to a moderator

Re: Sql Query with Mutiple columns [message #572802 is a reply to message #572801] Mon, 17 December 2012 14:43 Go to previous messageGo to next message
BlackSwan
Messages: 22911
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Sql Query with Mutiple columns [message #572809 is a reply to message #572802] Mon, 17 December 2012 15:53 Go to previous message
Littlefoot
Messages: 19699
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no "datetime" column in your example. "Description" can't be in (or not) a "select * from table_name" (unless that table contains a single column, but it appears that it does not; you'll get "too many values" error).

Anyway: here's one option:
SQL> select * from test order by id;

        ID STATUS     DESCRIPTIO TRACKING_ID
---------- ---------- ---------- -----------
         1 start      frog                 1
         2 start      dog                  2
         3 process    frog                 1
         4 completed  dog                  2
         5 start      rabbit               3
         6 error      frog                 1
         7 stop       rabbit               3
         8 start      elephant             4
         9 process    elephant             4
        10 start      human                5
        11 stop       human                5
        12 start      butterfly            6
        13 completed  butterfly            6
        14 start      lion                 7
        15 error      lion                 8
        16 completed  lion                 8
        17 start      tiger                9
        18 error      tiger                9

18 rows selected.

SQL> with max_tid as
  2    (select description, max(id) mid
  3     from test
  4     group by description
  5    )
  6  select t.description
  7  from test t, max_tid m
  8  where t.description = m.description
  9    and t.id = m.mid
 10    and t.status not in ('completed', 'stop', 'done')
 11  order by 1;

DESCRIPTIO
----------
elephant
frog
tiger

SQL>
Previous Topic: sql query to display the repeated column values as null
Next Topic: how to execute dynamic sql in procedure with special characters?
Goto Forum:
  


Current Time: Sat Oct 25 15:47:20 CDT 2014

Total time taken to generate the page: 0.03752 seconds