CODEWITHSUNDEEP
sqlsql-server
Kiran
Kiran

Reputation: 449

ORDER BY with different WHERE condition

Here is illustrated scenario for my problem.

SELECT field
FROM myTable
WHERE field1 = 'abc' AND filed2 = 'xyz' AND field3 = 'pqr'
ORDER BY field4 ASC

This query give me result as:

result1
result2
result3

Now if I run following query, then

SELECT field
FROM myTable
WHERE field1 = 'xxx' AND field2 = 'xyz'
ORDER BY field4 ASC

result2
result3
result1

So, basically both query give same results but with different order. But I want result from first query with second query's ORDER BY. See both have different condition on field1. Everything else is same.

Upvotes: 0

Views: 94

Answers (2)

sepupic
sepupic

Reputation: 8687

There is nothing wrong with this.

2 query return different rows (the first with field1 = 'abc', the second with 'xxx'), so why should these 2 result sets correlate?

Here is an example:

declare @myTable table (field varchar(100), 
                        field1 varchar(100), 
                        field2 varchar(100), 
                        field3 varchar(100), 
                        field4 int);

insert into @myTable (field, field1, field2, field3, field4)
values  ('result1', 'abc', 'xyz', 'pqr',1),
('result2', 'abc', 'xyz', 'pqr',2),
('result3', 'abc', 'xyz', 'pqr',3);

insert into @myTable (field, field1, field2, field4)
values
('result2', 'xxx', 'xyz',1),
('result3', 'xxx', 'xyz',2),
('result1', 'xxx', 'xyz',3);


SELECT field
FROM @myTable
WHERE field1 = 'abc' AND field2 = 'xyz' AND field3 = 'pqr'
ORDER BY field4 ASC

--result1
--result2
--result3

SELECT field
FROM @myTable
WHERE field1 = 'xxx' AND field2 = 'xyz'
ORDER BY field4 ASC

--result2
--result3
--result1

Upvotes: 3

Tree Frog
Tree Frog

Reputation: 666

You could add a sub query of the 2nd statement into the 1st like below and order on that.

SELECT t1.field, 
(SELECT top 1 t2.field4
    FROM myTable t2
    WHERE t2.field1 = 'xxx' AND t2.field2 = 'xyz'
    AND t2.field = t1.field) as orderField
FROM myTable t1
WHERE t1.field1 = 'abc' AND t1.filed2 = 'xyz' AND t1.field3 = 'pqr'
ORDER BY orderField ASC

Upvotes: 0

Related Questions