Books online says this about "Order By":
The ORDER BY clause can include items not appearing in the select list.
Well, that's actually strange and surprising. This is surely not new to
many of you, but I just recently saw this construction:
Use Pubs
Select Top 5 * From Titles Order by Newid()
Which, of course, picks 5 titles randomly from the Titles table. It might
not be efficient for huge tables, but it's good to know that you can do
this. Other than this, I have NEVER seen an Order By that didn't reference
a column from the Select list, and I had no idea that it was possible -- I
must have glossed over that sentence in BOL since it had no more
explanation and it wouldn't have really make sense before I knew about it
(cognitive dissonance, I suppose).
Note: If you're using Windows 2000 or later, the implementation of Newid()
returns random IDs. In earlier operating systems, I think they were
sequential.
You have to read between the lines of that Select statement to figure out
that SQL must append the expression you supply to each row of the table,
then order the result set by that expression. Not that BOL bothers to
explain this, of course.
It's kind of poor that BOL has this one sentence but doesn't go on to
explain what happens when you order by something not in the select list.
Can I order by the values taken from a field in a different table? (Not as
far as I can tell.)
Can you do this:
Select Top 5 * From Titles Order by 'Hello there'
(Yes, you can, but it's not interesting.)
BOL should say "The ORDER BY clause can include items not appearing in the
select list, and when you do that, here's what happens..."
Are there any other cases where this feature of Order By could be useful,
other than with Newid()?
David Walker"DWalker" <none@.none.com> wrote in message
news:%23UutkJ$MFHA.3512@.TK2MSFTNGP15.phx.gbl...
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not
as
> far as I can tell.)
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
You can ORDER BY any valid scalar.
Yes, you can order by a value from another table. Use a correlated
subquery:
ORDER BY
(SELECT SomeColumn
FROM SomeOtherTable
WHERE SomeOtherTable.PK = YourTable.PK)
Or, it's often very useful to ORDER BY a CASE expression... For
instance, maybe you want to return all rows where SomeColumn = 99 first,
then sort the rest by SomeColumn ascending:
ORDER BY
CASE SomeColumn
WHEN 99 THEN 1
ELSE SomeColumn
END
.. There are lots of interesting things you can do.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||To add to Adam's reply, In general, this should occur anytime you want th
e
rows in an output resultset ordered by some value (albiet a direct Column
value or calculated expression) whose value you are not specifically
interested in on the CLient, other than as a mechanism by which to sort the
rows...
I often Order by a datetime expression, which is not output in the Select
clause,
It is frequently required that an Output column based on a datetime be
formatted as a user readable string, say "Tuesday, March 14" While actually
sorting on the internal datetime value of the same column, not on the string
which is output by the SQL .
"DWalker" wrote:
> Books online says this about "Order By":
> The ORDER BY clause can include items not appearing in the select list.
> Well, that's actually strange and surprising. This is surely not new to
> many of you, but I just recently saw this construction:
> Use Pubs
> Select Top 5 * From Titles Order by Newid()
> Which, of course, picks 5 titles randomly from the Titles table. It might
> not be efficient for huge tables, but it's good to know that you can do
> this. Other than this, I have NEVER seen an Order By that didn't referenc
e
> a column from the Select list, and I had no idea that it was possible -- I
> must have glossed over that sentence in BOL since it had no more
> explanation and it wouldn't have really make sense before I knew about it
> (cognitive dissonance, I suppose).
> Note: If you're using Windows 2000 or later, the implementation of Newid()
> returns random IDs. In earlier operating systems, I think they were
> sequential.
> You have to read between the lines of that Select statement to figure out
> that SQL must append the expression you supply to each row of the table,
> then order the result set by that expression. Not that BOL bothers to
> explain this, of course.
> It's kind of poor that BOL has this one sentence but doesn't go on to
> explain what happens when you order by something not in the select list.
> Can I order by the values taken from a field in a different table? (Not a
s
> far as I can tell.)
> Can you do this:
> Select Top 5 * From Titles Order by 'Hello there'
> (Yes, you can, but it's not interesting.)
> BOL should say "The ORDER BY clause can include items not appearing in the
> select list, and when you do that, here's what happens..."
> Are there any other cases where this feature of Order By could be useful,
> other than with Newid()?
>
> David Walker
>|||Thanks, Adam and cbretana. That helps.
David
"examnotes" <cbretana@.areteIndNOSPAM.com> wrote in
news:7B887787-2D11-4886-8CE7-C007F4F93603@.microsoft.com:
> To add to Adam's reply, In general, this should occur anytime you
> want the rows in an output resultset ordered by some value (albiet a
> direct Column value or calculated expression) whose value you are not
> specifically interested in on the CLient, other than as a mechanism by
> which to sort the rows...
> I often Order by a datetime expression, which is not output in the
> Select clause,
> It is frequently required that an Output column based on a datetime be
> formatted as a user readable string, say "Tuesday, March 14" While
> actually sorting on the internal datetime value of the same column,
> not on the string which is output by the SQL .
>
> "DWalker" wrote:
>|||On Tue, 29 Mar 2005 08:08:53 -0800, DWalker wrote:
>Thanks, Adam and cbretana. That helps.
>David
Hi David,
In addition to Adam's and CBretana's reply, I'd like to point out that
the ability to order by items that are not in the select list is a T-SQL
proprietary feature. If portability of your code is important, then it's
best to include the ordering columns in the select list, so that your
query is ANSI-compliant.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> In addition to Adam's and CBretana's reply, I'd like to point out that
> the ability to order by items that are not in the select list is a T-SQL
> proprietary feature. If portability of your code is important, then it's
> best to include the ordering columns in the select list, so that your
> query is ANSI-compliant.
Maybe not ANSI-compliant, but I think "proprietary" is too harsh a term.
This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
PostgreSQL, and others all support this, in addition to SQL Server.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||OK, thanks. I'm sure we won't be moving this out of SQL 2000 except
possibly to SQL 2005, but it's good to know it's not strictly ANSI-
compliant.
David
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:OBsRlIKNFHA.244@.tk2msftngp13.phx.gbl:
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:rsfj41l58bdeg94ppt37vgvud1t4814335@.
4ax.com...
> Maybe not ANSI-compliant, but I think "proprietary" is too harsh a
> term.
> This is fairly widely supported amongst SQL DBMSs -- Oracle, DB2,
> PostgreSQL, and others all support this, in addition to SQL Server.
>
Showing posts with label strange. Show all posts
Showing posts with label strange. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
order by because of distinct
I have a strange situation (I think). Within a view (which explains why I use the top 100% --> to use an order by) I have this query:
select TOP 100 PERCENT *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100
This gives me the following results:
ofd_fk_off_id off_fk_class_id
100 2753
100 2753
100 2071
100 2753
Now I change the query to:
select distinct ofd_fk_off_id, ofd_fk_class_id from
(select TOP 100 PERCENT *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100
order by ofd_sequence ASC ) as tbOffers
This gives me the following results:
ofd_fk_off_id off_fk_class_id
100 2071
100 2753
In the execution plan, it says that a distinct order by is used on off_fk_class_id. My question is: why is this done? I want only a distinct and not an order by. So is there a way to change this (default?) behaviour.First of all
Why are you doing a
Select Top 100 PERCENT
you want all the rows
So a simple select would do
select distinct ofd_fk_off_id, ofd_fk_class_id from
(select *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100
order by ofd_sequence ASC ) as tbOffers
Second
The results are OK
in your second example your doing an ORDER BY
that's why you don't have the rows in the same order|||I use the top because the select statement is within a view and otherwise I can not use the order by.
For the results of the second query: they need to be in the same order as the first one, so first 2753 and then 2071. But because of the "select distinct" in the second query it not only performs a distinct but again an "order by" which I did not specify in my query. So my question is why it performs the "order by" and how I can prevent it.|||it's doing an order by to bring all the duplicates together
to then eliminate them|||You'll have to do a second "top 100 percent / order by"
on the Offers to get the wanted order|||Unfortunately, it's not possible to perform an extra order by, because the only correct order is using the field "ofd_sequence" and if I add it to the second query I get the results of the first one. Can't I use another statement. I tried changing the clustered index, but this didn't work either. Someone with an idea?|||OK
1- What do you have in your tables
2- What result do you want
3- We'll find something that works
Enigma in online !|||1. OK, I have three tables: tbOffers (off_id, price and some other fields) and tbOfferDetails (ofd_id, ofd_fk_off_id, ofd_fk_class_id) which are self explaining I think. Every record from the tbOfferDetails table has a foreign key to the third table tbClassifications (class_id, class_caption and some other fields) and to the table tbOffers. Order details are added and have a mandatory sequence. Within one order, a certain class_id (from tbClassifications) can occur multiple times.
2. What I want: all ofd_fk_class_id values for a certain offer (field ofd_fk_off_id in the order details table), but only one time and in the order of the sequence field "ofd_sequence" from the table tbOfferDetails.|||This must not be it but maybe it will give some ideas
Select Offers.IdOffer, ClassDetails.IdClass
From Offers
Inner Join (Select Distinct Top 100 Percent Details.IdOffer, Details.IdClassification,
From Classifications
Inner Join Details
On Details.IdClass = Classifications.IdClass
Order By Details.Id Asc) ClassDetails
On ClassDetails.IdOffer = Offers.IDOffer
???
Can you give some data examples|||Sorry, does not get the desired result. Below is some data. Maybe this will help.
tbClassifications:
class_id class_caption
2071 'Caption_2071'
2753 'Caption_2753'
tbOffers:
off_id off_date
100 '01/01/2004'
tbOfferDetails
ofd_id ofd_fk_off_id ofd_fk_class_id ofd_sequence ofd_description
1 100 2753 100 'First line'
2 100 2753 200 'Second line'
3 100 2753 300 'Third line'
4 100 2753 400 'Fourth line'
5 100 2071 500 'Fifth line'
6 100 2753 600 'Last line'|||and what result would you like ?|||what I wrote in my first post, so
ofd_fk_off_id ofd_fk_class_id
100 2753
100 2071|||Select Distinct Top 100 PERCENT Details.IdClass,Details.IdOffer,DetailsOrder.MinId
From Details
Inner Join (Select Min(Id) as MinId,Idclass
From Details
Group By IdClass) DetailsOrder
On DetailsOrder.IdClass = Details.IdClass
Order By DetailsOrder.MinId
Where
Classes = tbClassifications
Details = tbOfferDetails
Offers = tbOffers|||Finally, it works. I just changed a little bit from the last post of Karolyn. Because it's a view, a distinct was not possible so made an extra subquery of it. Anyway, thanks a lot Karolyn.
select ofd_fk_off_id, ofd_fk_class_id from
(select distinct top 100 percent tbOfferDetails.ofd_fk_class_id, tbOfferDetails.ofd_fk_off_id, minID
from tbOfferDetails inner join
(select min(ofd_sequence) as minID, ofd_fk_class_id
from tbOfferDetails
group by ofd_fk_class_id) DetailsOrder
on DetailsOrder.ofd_fk_class_id = tbOfferDetails.ofd_fk_class_id
order by DetailsOrder.minID) as Results|||Was fun to do
but as usual I'm slow on the starting blocks
thanks to you too, i've learned on limitations of views
select TOP 100 PERCENT *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100
This gives me the following results:
ofd_fk_off_id off_fk_class_id
100 2753
100 2753
100 2071
100 2753
Now I change the query to:
select distinct ofd_fk_off_id, ofd_fk_class_id from
(select TOP 100 PERCENT *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100
order by ofd_sequence ASC ) as tbOffers
This gives me the following results:
ofd_fk_off_id off_fk_class_id
100 2071
100 2753
In the execution plan, it says that a distinct order by is used on off_fk_class_id. My question is: why is this done? I want only a distinct and not an order by. So is there a way to change this (default?) behaviour.First of all
Why are you doing a
Select Top 100 PERCENT
you want all the rows
So a simple select would do
select distinct ofd_fk_off_id, ofd_fk_class_id from
(select *
from tbOfferDetails
where ofd_id = ofd_parent and ofd_fk_off_id = 100
order by ofd_sequence ASC ) as tbOffers
Second
The results are OK
in your second example your doing an ORDER BY
that's why you don't have the rows in the same order|||I use the top because the select statement is within a view and otherwise I can not use the order by.
For the results of the second query: they need to be in the same order as the first one, so first 2753 and then 2071. But because of the "select distinct" in the second query it not only performs a distinct but again an "order by" which I did not specify in my query. So my question is why it performs the "order by" and how I can prevent it.|||it's doing an order by to bring all the duplicates together
to then eliminate them|||You'll have to do a second "top 100 percent / order by"
on the Offers to get the wanted order|||Unfortunately, it's not possible to perform an extra order by, because the only correct order is using the field "ofd_sequence" and if I add it to the second query I get the results of the first one. Can't I use another statement. I tried changing the clustered index, but this didn't work either. Someone with an idea?|||OK
1- What do you have in your tables
2- What result do you want
3- We'll find something that works
Enigma in online !|||1. OK, I have three tables: tbOffers (off_id, price and some other fields) and tbOfferDetails (ofd_id, ofd_fk_off_id, ofd_fk_class_id) which are self explaining I think. Every record from the tbOfferDetails table has a foreign key to the third table tbClassifications (class_id, class_caption and some other fields) and to the table tbOffers. Order details are added and have a mandatory sequence. Within one order, a certain class_id (from tbClassifications) can occur multiple times.
2. What I want: all ofd_fk_class_id values for a certain offer (field ofd_fk_off_id in the order details table), but only one time and in the order of the sequence field "ofd_sequence" from the table tbOfferDetails.|||This must not be it but maybe it will give some ideas
Select Offers.IdOffer, ClassDetails.IdClass
From Offers
Inner Join (Select Distinct Top 100 Percent Details.IdOffer, Details.IdClassification,
From Classifications
Inner Join Details
On Details.IdClass = Classifications.IdClass
Order By Details.Id Asc) ClassDetails
On ClassDetails.IdOffer = Offers.IDOffer
???
Can you give some data examples|||Sorry, does not get the desired result. Below is some data. Maybe this will help.
tbClassifications:
class_id class_caption
2071 'Caption_2071'
2753 'Caption_2753'
tbOffers:
off_id off_date
100 '01/01/2004'
tbOfferDetails
ofd_id ofd_fk_off_id ofd_fk_class_id ofd_sequence ofd_description
1 100 2753 100 'First line'
2 100 2753 200 'Second line'
3 100 2753 300 'Third line'
4 100 2753 400 'Fourth line'
5 100 2071 500 'Fifth line'
6 100 2753 600 'Last line'|||and what result would you like ?|||what I wrote in my first post, so
ofd_fk_off_id ofd_fk_class_id
100 2753
100 2071|||Select Distinct Top 100 PERCENT Details.IdClass,Details.IdOffer,DetailsOrder.MinId
From Details
Inner Join (Select Min(Id) as MinId,Idclass
From Details
Group By IdClass) DetailsOrder
On DetailsOrder.IdClass = Details.IdClass
Order By DetailsOrder.MinId
Where
Classes = tbClassifications
Details = tbOfferDetails
Offers = tbOffers|||Finally, it works. I just changed a little bit from the last post of Karolyn. Because it's a view, a distinct was not possible so made an extra subquery of it. Anyway, thanks a lot Karolyn.
select ofd_fk_off_id, ofd_fk_class_id from
(select distinct top 100 percent tbOfferDetails.ofd_fk_class_id, tbOfferDetails.ofd_fk_off_id, minID
from tbOfferDetails inner join
(select min(ofd_sequence) as minID, ofd_fk_class_id
from tbOfferDetails
group by ofd_fk_class_id) DetailsOrder
on DetailsOrder.ofd_fk_class_id = tbOfferDetails.ofd_fk_class_id
order by DetailsOrder.minID) as Results|||Was fun to do
but as usual I'm slow on the starting blocks
thanks to you too, i've learned on limitations of views
Monday, March 12, 2012
Oracle report returning strange data
I have a RS report that is connecting to an Oracle Database (8.1.7), the
8.1.7 oracle client is installed on RS Sever, and the result set is returning
strange data. I'm using a simple view that was created in the Oracle
database, that returns a small record set about 100 records. I'm seeing ASCII
values in place of some data, in the Data tab and the Preview tab and the
Published report.
I have tried using both Oracle Drivers and the Microsoft ODBC drivers for
Oracle, and I get the same results.
Some of the data looks just fine but some of the characters get replaced by
ASCII values.
I have installed RS SP1.
Don't know what I am doing wrong...Hi Gary
We are running SQL Reporting Services (SP1) against an Oracle 9i database
without any problems. In our case 9.2.0.4 client is used to talk with
9.2.0.4 database.
I suspect the problem you are having is related to version of Oracle client
you are using. If you have access to a client 9.2.0.4 or higher you may want
to give that a try.
"garyc" wrote:
> I have a RS report that is connecting to an Oracle Database (8.1.7), the
> 8.1.7 oracle client is installed on RS Sever, and the result set is returning
> strange data. I'm using a simple view that was created in the Oracle
> database, that returns a small record set about 100 records. I'm seeing ASCII
> values in place of some data, in the Data tab and the Preview tab and the
> Published report.
> I have tried using both Oracle Drivers and the Microsoft ODBC drivers for
> Oracle, and I get the same results.
> Some of the data looks just fine but some of the characters get replaced by
> ASCII values.
> I have installed RS SP1.
> Don't know what I am doing wrong...
8.1.7 oracle client is installed on RS Sever, and the result set is returning
strange data. I'm using a simple view that was created in the Oracle
database, that returns a small record set about 100 records. I'm seeing ASCII
values in place of some data, in the Data tab and the Preview tab and the
Published report.
I have tried using both Oracle Drivers and the Microsoft ODBC drivers for
Oracle, and I get the same results.
Some of the data looks just fine but some of the characters get replaced by
ASCII values.
I have installed RS SP1.
Don't know what I am doing wrong...Hi Gary
We are running SQL Reporting Services (SP1) against an Oracle 9i database
without any problems. In our case 9.2.0.4 client is used to talk with
9.2.0.4 database.
I suspect the problem you are having is related to version of Oracle client
you are using. If you have access to a client 9.2.0.4 or higher you may want
to give that a try.
"garyc" wrote:
> I have a RS report that is connecting to an Oracle Database (8.1.7), the
> 8.1.7 oracle client is installed on RS Sever, and the result set is returning
> strange data. I'm using a simple view that was created in the Oracle
> database, that returns a small record set about 100 records. I'm seeing ASCII
> values in place of some data, in the Data tab and the Preview tab and the
> Published report.
> I have tried using both Oracle Drivers and the Microsoft ODBC drivers for
> Oracle, and I get the same results.
> Some of the data looks just fine but some of the characters get replaced by
> ASCII values.
> I have installed RS SP1.
> Don't know what I am doing wrong...
Saturday, February 25, 2012
Oracle Error When running Niku Clarity with MS JDBC
Hi All
I get a very strange error when running the JDBC in WAS6.0
This is running the Clarity 7.5.3 Application
Failed to verify database: Io exception:
java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
Any ideas
Not sure that I understand the scenario here. The Microsoft 2005 JDBC driver is a type 4 Sql Server driver. The only databases supported are Sql Server 2000 and Sql Server 2005.
|||After working with the CA Clarity system now for some time, it is apparent that they just did not change the code to reflect another vendor, so the Oracle errors are realy just database errors
Just another case of Oracle coders thinking that their code will never move to MS SQL... 
Oracle Error When running Niku Clarity with MS JDBC
Hi All
I get a very strange error when running the JDBC in WAS6.0
This is running the Clarity 7.5.3 Application
Failed to verify database: Io exception:
java.sql.SQLException: Io exception: The Network Adapter could not establish the connection
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
Any ideas
Not sure that I understand the scenario here. The Microsoft 2005 JDBC driver is a type 4 Sql Server driver. The only databases supported are Sql Server 2000 and Sql Server 2005.
|||After working with the CA Clarity system now for some time, it is apparent that they just did not change the code to reflect another vendor, so the Oracle errors are realy just database errors
Just another case of Oracle coders thinking that their code will never move to MS SQL...
Subscribe to:
Posts (Atom)