CODEWITHSUNDEEP
mysqlsql
Suma Mutalikdesai
Suma Mutalikdesai

Reputation: 75

Avoiding a nested subquery for the single table

I have a table with following data. I have some sales Id and it may / may not be revised. If it is revised then it will have revision number. If SalesId is revised twice then it will have revision number 2.

SalesId and Revision are w.r.t to each other and there is no primary key or foreign key

SalesId -100 100 100 101 102 102 103 104 104


Revision -0   1   2  0    0   1   0   0   1

My requirement is – if user gives one revision number say ‘n’ , I need to get all the records which are having revisions<=’n’.

For example: 

1.I want to know SalesId's which are not at all revised , means ‘n=0’. expected result : 101, 103

2.I want to know SalesIds which are revised only once, means ‘n=1’. expected result : 102, 104

so I have written a nested query --

select Sales1.Sales_Id,Sales1.Rev
from (select Sales_Id, Max(Revision) as "Rev" from Sales Group By Sales_Id )as Sales1 where Rev=0

select Sales1.Sales_Id,Sales1.Rev
from (select Sales_Id, Max(Revision) as "Rev" from Sales Group By Sales_Id )as Sales1 where Rev=1

I am getting the correct answer.

Using Nested Query lowers the performance, I want this query to be simplified without using a nested Query.

Please suggest me something.

Upvotes: 2

Views: 167

Answers (1)

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

In "one query", you can use an HAVING clause :

select sales_id, max(revision)
from Sales
group by sales_id
having max(revision) = 0;

select sales_id, max(revision)
from Sales
group by sales_id
having max(revision) = 1;

I added your queries in SqlFiddle, you may look at execution plan to see the difference.

SqlFiddle

Upvotes: 2

Related Questions