Nested SQL Query

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello friends,
I have table called with columns

tbl_actiontable (jobno,actionstatus,cycle)

where cycle can take value 1,2,3..
and actionstatus 55,56,33

My query is I'm trying to find out
1. Max cycle
2. Find if for that cycle, if there is actionstatus=33
3. if 2 condition is false, m finding if there is actionstatus = 55 or 56

How can i carry out this task in one sql query

I tried following, I was able to get 1 and 3 condition

SQL

select 1 as color from tbl_action_history where   actionstatus=56  And jobno=92397   And
cycle in (select MAX(cycle) from tbl_action_history where jobno=92397)

I'm not able to check second condition and go for 3 condition

Thanks in advance

Posted 29-Dec-10 1:24am

dhage.prashant01

Updated 29-Dec-10 2:04am

Not Active

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Hiren solanki · Answer 1 · 2010-12-29T02:56:00

Solution 1

SELECT 1 
FROM tbl_action_history
WHERE EXISTS(SELCT MAX(cycle) FROM tbl_action_history WHERE jobno=92397 AND (actionstatus = 33 OR actionstatus = 55 OR actionstatus=56))

This might help you.

Posted 29-Dec-10 2:56am

Hiren solanki

Gordon Kushner · Answer 2 · 2010-12-29T03:13:00

I'm not 100% sure what you're looking for, but this code will first check for conditions 1 & 2 and then condition 3.
Then it selects the top 1 based on priority.

SQL

declare @actiontable TABLE (jobno int,
                            actionstatus int,
                            cycle int)

insert into @actiontable
values(5, 33, 14)
insert into @actiontable
values(5, 45, 20)
insert into @actiontable
values(5, 55, 17)
insert into @actiontable
values(5, 56, 25)

select top 1 *
from
((select 1 as priority, jobno, actionstatus, cycle
    from @actiontable
    where jobno = 5
    and cycle = (select MAX(cycle)
            from @actiontable
            where jobno = 5)
    and actionstatus = 33)

union all

(select top 1 2, jobno, actionstatus, cycle
    from @actiontable
    where jobno = 5
    and actionstatus in (55, 56))) as x
order by priority