Complex order by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complex order by

Author

Topic

shaharru
Yak Posting Veteran

72 Posts

Posted - 2008-11-10 : 12:30:31

I’m trying to do a kind of complex sorting , I can’t seem to get it right.
I tried using “UNION” to select the table twice and sort each half in a different way but “UNION” does not support order by.

I have the following table:


ProductId	ProductValue	AddDate	       IsValid
1	           100.0	10/11/2008	True
2	           101.4	11/11/2008	True
3	           102.1	02/11/2008	False
4	           123.5	10/11/2008	False
5                  177.5	01/11/2008	True
6	           144.3	08/11/2008	False
7	           199.3	07/11/2008	False
8	           201.1	06/11/2008	False

I want to sort the table :
All rows with IsValid = true , should be sorted by ProductValue
All rows with IsValid = false , should be sorted by AddDate

Resulting Table should be :

ProductId	ProductValue	AddDate 	IsValid
5	           177.5	01/11/2008	True
2	           101.4	11/11/2008	True
1	           100.0	10/11/2008	True
4	           123.5	10/11/2008	False
6	           144.3	08/11/2008	False
7	           199.3	07/11/2008	False
8	           201.1	06/11/2008	False
3	           102.1	02/11/2008	False

thank you for your help guys!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-10 : 12:32:51

You should use two queries to achieve the two different sorts and then union them together so that you have one query to run and one result set.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shaharru
Yak Posting Veteran

72 Posts

Posted - 2008-11-10 : 12:44:25

thx for the replay.

I tried using a union , but im getting and sql error that i cannot use a union with a order by.
"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

here is my sql


SELECT t2.ProductId, t2.ProductValue, t2.AddDate , t2.IsValid
FROM (
    SELECT t0.ProductId, t0.ProductValue, t0.AddDate , t0.IsValid
    FROM Products AS t0
    WHERE ([t0].[IsValid] = 1)
    order by t0.ProductValue
    UNION ALL
  SELECT t1.ProductId, t1.ProductValue, t1.AddDate , t1.IsValid
    FROM Products AS t1
    WHERE ([t1].[IsValid] = 0)
    order by t1.AddDate
    ) AS [t2]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-10 : 12:48:31

You can use TOP 100 PERCENT to avoid that error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shaharru
Yak Posting Veteran

72 Posts

Posted - 2008-11-10 : 12:52:59

thx tkizer! ,it works !

i still have a question :)

1. is this the most efficient way to perform this? i have this sql executed every 2 secs.
2. my website is based on linq , any idea on how to write this in linq?

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-11-10 : 13:09:51

How about something like

SELECT ProductId, ProductValue, AddDate, IsValid
FROM Products
ORDER BY IsValid DESC,
    CASE WHEN IsValid = 'True' THEN Productvalue
    ELSE AddDate END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-10 : 13:16:22

That should work too, but it may not be as efficient as the two query/union approach due to indexes. I'd compare the execution plans. The first option should allow you to use different indexes for each whereas the second option would only allow you to use one index.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shaharru
Yak Posting Veteran

72 Posts

Posted - 2008-11-10 : 14:11:24

thank you!

any clue on writing in in LINQ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-10 : 14:21:10

I have zero experience with LINQ.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

shaharru
Yak Posting Veteran

72 Posts

Posted - 2008-11-10 : 14:23:06

:)

Its just the linq doesn't support generating a "TOP 100 PERCENT" query

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-10 : 14:38:41

ordering in a view or subquery is irrelevant and you can't be certain it will be honored. so don't do it.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

shaharru
Yak Posting Veteran

72 Posts

Posted - 2008-11-10 : 14:43:42

so what will i do ?
how do i write this in linq?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-10 : 14:49:41

may i suggest you don't write this in linq and stop using it altogether, since it is basically a dead end technology.

what you do is put results of each unioed query into a #temp table view and select from there.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

shaharru
Yak Posting Veteran

72 Posts

Posted - 2008-11-10 : 14:58:59

i cant do that , my all website - over 100 classes is based on linq.

any way i can do this with linq?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-10 : 15:04:48

you can try with this query:


SELECT t2.ProductId, t2.ProductValue, t2.AddDate , t2.IsValid
FROM
(
    SELECT t0.ProductId, t0.ProductValue, t0.AddDate , t0.IsValid
    FROM Products AS t0
    WHERE ([t0].[IsValid] = 1)
		UNION ALL
  	SELECT t1.ProductId, t1.ProductValue, t1.AddDate , t1.IsValid
    FROM Products AS t1
    WHERE ([t1].[IsValid] = 0)
) AS [t2]
ORDER BY IsValid DESC, ProductValue, AddDate

but how to do that in linq... beats me...

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

shaharru
Yak Posting Veteran

72 Posts

Posted - 2008-11-10 : 15:13:34

thx spirit1.

:(

this query doesn't give the correct result.
the sort of IsValid = 0 , should be based on AddDate and not based on ProductValue.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-10 : 15:31:50

what you could do is read each query with linq and combine them into the same collection.
that would work, no?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

shaharru
Yak Posting Veteran

72 Posts

Posted - 2008-11-10 : 15:44:16

yes that would work .
BUT that will cause the combine part of the query to run on the code and not in the sql ,which is much much slower .
i run this query every 2 secs... performance is a real issue.

whats really driving me crazy is that i have now a T-SQL code that is working and im trying to write LINQ query that will generate a T-SQL.
LINQ is suppose to make life easier huh :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-11-10 : 15:48:05

of course it is

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

shaharru
Yak Posting Veteran

72 Posts

Posted - 2008-11-11 : 08:35:20

anybody has some other idea?

shaharru
Yak Posting Veteran

72 Posts

Posted - 2008-11-11 : 09:27:45

i just check the following sql query:


SELECT t2.ProductId, t2.ProductValue, t2.AddDate , t2.IsValid
FROM (
    SELECT TOP 100 PERCENT t0.ProductId, t0.ProductValue, t0.AddDate , t0.IsValid
    FROM Products AS t0
    WHERE ([t0].[IsValid] = 1)
    order by t0.ProductValue
    UNION ALL
  SELECT TOP 100 PERCENT t1.ProductId, t1.ProductValue, t1.AddDate , t1.IsValid
    FROM Products AS t1
    WHERE ([t1].[IsValid] = 0)
    order by t1.AddDate
    ) AS [t2]

i found out that it doesn't really work , if i put the "TOP 100 PERCENT" it cancels any of the "order by" commands.
it only works if i use "TOP 100" without the PERCENT which is not really good :(

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources