June 5, 2015 at 11:53 am
I've got two tables that I'm trying to order the data returned in a view. As I understand it, starting with SQL 2005 doing a SELECT TOP 100 PERCENT with an ORDER BY <some field> no longer works. It certainly doesn't work in my experiments.
So today I had the idea of using a UNION instead. So I've got something like this:
SELECT t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField = 0
UNION
SELECT t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField IS NULL
UNION
SELECT t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField = 1
This almost worked, but it returned all of the records where BitField == 1 first. Not what I wanted. So I thought maybe if I did this:
SELECT t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField = 1
UNION
SELECT t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField IS NULL
UNION
SELECT t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField = 0
that it would work, but that didn't work either. The resultset was still had records with BitField == 1 first.
So this makes me wonder, is there some sort of precedent order in which the UNION operator works?
Kindest Regards, Rod Connect with me on LinkedIn.
June 5, 2015 at 12:00 pm
Rod at work (6/5/2015)
I've got two tables that I'm trying to order the data returned in a view. As I understand it, starting with SQL 2005 doing a SELECT TOP 100 PERCENT with an ORDER BY <some field> no longer works. It certainly doesn't work in my experiments.So today I had the idea of using a UNION instead. So I've got something like this:
SELECT t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField = 0
UNION
SELECT t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField IS NULL
UNION
SELECT t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField = 1
This almost worked, but it returned all of the records where BitField == 1 first. Not what I wanted. So I thought maybe if I did this:
SELECT t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField = 1
UNION
SELECT t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField IS NULL
UNION
SELECT t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField = 0
that it would work, but that didn't work either. The resultset was still had records with BitField == 1 first.
So this makes me wonder, is there some sort of precedent order in which the UNION operator works?
What are you trying to accomplish? Yes, you can't put the ORDER BY in the view. If you want the data from the view to be ordered you put the ORDER BY on the query selecting from the view.
June 5, 2015 at 12:06 pm
Is this what you had in mind?
SELECT X.SomeColumn, X.AnotherColumn, X.SomeThirdColumn
FROM
(SELECT 1 as OrderingCol, t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField = 0
UNION
SELECT 2 as OrderingCol, t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField IS NULL
UNION
SELECT 3 as OrderingCol, t1.SomeColumn, t1.AnotherColumn, t2.SomeThirdColumn
FROM
Table1 t1 INNER JOIN Table2 t2
ON t1.ID = t2.fkID
WHERE t1.BitField = 1) X
ORDER BY X.OrderingCol
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 5, 2015 at 1:30 pm
Lynn, what I'm trying to do is group the records by BitField, whether they're ordered or not. What I was hoping the UNION would do is return all of the records with BitField == 0, then those records where BitField is NULL, and finally those where BitField == 1.
What I'm trying to do is help a knowledge working whose working on a MS Access application that uses this SQL view as the source for a combo box. I'm not an Access developer, so I'm going at it from the database side. As I understand it, the combo box in Access is just given a table (or view in this case) and you can't do other things with it, like sort the records in the order you want them. At least that's my understanding.
Kindest Regards, Rod Connect with me on LinkedIn.
June 5, 2015 at 2:33 pm
Rod at work (6/5/2015)
Lynn, what I'm trying to do is group the records by BitField, whether they're ordered or not. What I was hoping the UNION would do is return all of the records with BitField == 0, then those records where BitField is NULL, and finally those where BitField == 1.What I'm trying to do is help a knowledge working whose working on a MS Access application that uses this SQL view as the source for a combo box. I'm not an Access developer, so I'm going at it from the database side. As I understand it, the combo box in Access is just given a table (or view in this case) and you can't do other things with it, like sort the records in the order you want them. At least that's my understanding.
The only way to ensure the order of the data is to have the ORDER BY on the outer query.
June 5, 2015 at 3:10 pm
You can definitely sort the results in a combo box. In the Row Source property of the combo box, just add the appropriate ORDER BY clause to the query.
As others have already pointed out, that is the only way to guarantee the desired order of results anyway. Fortunately, Access does not prevent this.
I know a lot of people relied on the TOP 100 PERCENT until it started getting optimized out, and then people started doing TOP 99.99 PERCENT and TOP 2147483647 as additional cheats. Even those aren't guarantees, though. If you use ORDER BY in the outermost query, then the order is guaranteed.
I'd go the guaranteed route 🙂
Cheers!
June 5, 2015 at 7:08 pm
Put a case statement in the ORDER BY clause of your query:
ORDER BY CASE WHEN BitField = 0 THEN 1
WHEN BitField IS NULL THEN 2
WHEN BitField = 1 THEN 3
ELSE 4
End
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 7, 2015 at 7:49 am
Jacob Wilkins (6/5/2015)
You can definitely sort the results in a combo box. In the Row Source property of the combo box, just add the appropriate ORDER BY clause to the query.As others have already pointed out, that is the only way to guarantee the desired order of results anyway. Fortunately, Access does not prevent this.
I know a lot of people relied on the TOP 100 PERCENT until it started getting optimized out, and then people started doing TOP 99.99 PERCENT and TOP 2147483647 as additional cheats. Even those aren't guarantees, though. If you use ORDER BY in the outermost query, then the order is guaranteed.
I'd go the guaranteed route 🙂
Cheers!
I had used the ORDER BY clause with the TOP 100 PERCENT, but that didn't work.
Kindest Regards, Rod Connect with me on LinkedIn.
June 8, 2015 at 5:56 am
If you really cannot move the ORDER BY to the outer query that uses the view, then you could use the TOP 2147483647 (or a smaller number if that is way more rows than your dataset is ever likely to contain) syntax within the view, as mentioned by someone above. You are correct that TOP 100 PERCENT does not work in views any more, but TOP N and TOP <anything other than 100> PERCENT do still work, it was TOP 100 PERCENT specifically that Microsoft chose to ignore in views...so far. Whether future versions might ignore ORDER BY with more types of TOP in views is a risk to bear in mind.
June 8, 2015 at 8:23 am
gward 98556 (6/8/2015)
If you really cannot move the ORDER BY to the outer query that uses the view, then you could use the TOP 2147483647 (or a smaller number if that is way more rows than your dataset is ever likely to contain) syntax within the view, as mentioned by someone above. You are correct that TOP 100 PERCENT does not work in views any more, but TOP N and TOP <anything other than 100> PERCENT do still work, it was TOP 100 PERCENT specifically that Microsoft chose to ignore in views...so far. Whether future versions might ignore ORDER BY with more types of TOP in views is a risk to bear in mind.
Realize this is a crutch. The only way to ensure the order of data is for there to be an ORDER BY on the outer query.
June 8, 2015 at 9:27 am
Lynn Pettis (6/8/2015)
Realize this is a crutch. The only way to ensure the order of data is for there to be an ORDER BY on the outer query.
Are there any specific scenarios where ORDER BY currently fails in a view using TOP 2147483647 (assuming that is enough rows for all the results)?
June 8, 2015 at 9:39 am
gward 98556 (6/8/2015)
Lynn Pettis (6/8/2015)
Realize this is a crutch. The only way to ensure the order of data is for there to be an ORDER BY on the outer query.
Are there any specific scenarios where ORDER BY currently fails in a view using TOP 2147483647 (assuming that is enough rows for all the results)?
Not saying it fails but that it can. Again, if you want to ensure the order of the data set returned by a query you need to put the ORDER BY on the outer most query.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply