| Sql Query with Mutiple columns [message #572801] |
Mon, 17 December 2012 14:41  |
 |
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 18 times)
[Updated on: Mon, 17 December 2012 14:46] Report message to a moderator |
|
|
|
|
|
| Re: Sql Query with Mutiple columns [message #572809 is a reply to message #572802] |
Mon, 17 December 2012 15:53  |
 |
Littlefoot
Messages: 16975
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> |
|
|
|
] 
Members
Search
Help
Register
Login
Home
