Defining sort order using parameter fields.

Hi!

This is my first post at this forum.

I have a parameterfield wich the users can use to enter sortorder to the report. I have then created a formula with this code in it.

if {?sortid} = "1" then ({DMD_VIEW_ERRAND.er_control_date} AND {DMD_VIEW_ERRAND.er_id})
else
if {?sortid} = "2" then ({DMD_VIEW_ERRAND.er_activity_desc} AND {DMD_ERRAND.ER_HANDLERDATA} AND {DMD_VIEW_ERRAND.er_id})


This formula is then added into the record sorting expert.

The problem is there is an error in the formula. I want to order by multiple columns. Is it possible?
If it is, what's wrong with my code?

I'm using crystal XI
Best regards
HeleniusI've solved it.

Here's the code

if {?sortid} = "1" then (CStr({DMD_VIEW_ERRAND.er_control_date_fmt}) + "AND" + {DMD_VIEW_ERRAND.er_id})
else
if {?sortid} = "2" then ({DMD_VIEW_ERRAND.er_activity_desc} + "AND" + {DMD_ERRAND.ER_HANDLERDATA} + "AND" + CStr({DMD_VIEW_ERRAND.er_control_date_fmt}) + "AND" + {DMD_VIEW_ERRAND.er_id})

I had a little convertion problem...

/Helenius|||Noooo, it doesn't seem to be solved after all. The report only sort based by the first column. Any suggestions??

Default sort order when using SELECT with no ORDER BY clause

For SQL Server 2000 and 7.0:

What is the default sort order of a dataset when you perform a SELECT * FROM [tablename] without using the ORDER BY clause?

I've been told it is ordered by the clustered index (my testing does not bear this out) and I've been told that it is completely unpredictable (closer to what I'm seeing). I believe the latter to be true but cannot find this outlined anywhere in Microsoft's BOL, TechNet, KnowledgeBase, MSDN, etc.

Any pointers on where to find the definitive answer is appreciated.

TIA. RickIf there is clustered index - sorting by clustered index, else sorting by inserting records.

BOL says:

SQL Server 7.0 tables use one of two methods to organize their data pages:

Clustered tables are tables that have a clustered index.
The data rows are stored in order based on the clustered index key. The data pages are linked in a doubly-linked list. The index is implemented as a B-tree index structure that supports fast retrieval of the rows based on their clustered index key values.

Heaps are tables that have no clustered index.
The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. The data pages are not linked in a linked list.

SQL Server also supports up to 249 nonclustered indexes on each table. The nonclustered indexes have a B-tree index structure similar to the one in clustered indexes. The difference is that nonclustered indexes have no effect on the order of the data rows. Clustered tables keep their data rows in order based on the clustered index key. The collection of data pages for a heap is not affected if nonclustered indexes are defined for the table. The data pages remain in a heap unless a clustered index is defined.|||Hi ,

If there is no ORDER BY clause in the Query , then the data will be in the same order as inserted.

If you mention ORDER BY Column name , then the dafault sort order will be ASCending if SQL Server is installed by default collation.

Ex. Select * from authors order by authorname

FYI:
--
Use the locale identified by Setup, and then choose the desired binary, case, or other options.

For the release of SQL Server 2000, when Setup detects that the computer is running the U.S. English locale, Setup automatically selects the SQL collation: Dictionary order, case-insensitive, for use with 1252 character set.

To select the equivalent Windows collation, select Collation designator, choose the Latin1_General collation designator, do not select case-sensitive, and select accent-sensitive.

For more information , see BOL - Collation|||"If there is no ORDER BY clause in the Query , then the data will be in the same order as inserted"

if you're lucky

records can get physically stored not in insertion sequence (e.g. a row happens to be too big for the current physical database page, but the next one inserted after that isn't)

i believe records are returned in physical sequence, all things being equal

but what if some of the rows are already in cache, and the remaining rows need to be fetched from disk? it's a safe bet that the ones in cache are pumped out first

i too have heard that the sort order is unpredictable, but i too have been unable to find this on microsoft's site

a "definitive" answer will be found, of course, only on microsoft's site

rudy
http://rudy.ca/|||Hi,

I dont agree with "R937". Sorry to say that.

To his Question" but what if some of the rows are already in cache, and the remaining rows need to be fetched from disk? it's a safe bet that the ones in cache are pumped out first"

This is absolutely wrong. Thats not the concept of caching or Query Optimization.

For Example, if I excute the query as below,

Use pubs
go
/* This will return rows where job_id > 5 */
Select * from jobs where job_id > 5
go

Select * from jobs
go
AS per your statement, this has to return the jobid>5 from cache first and then job_id<4 from disk.

Thats not true.

Concept:
----
As per SQL Server, both the query are entirely different.

First it will just look in cache whether the same SQL Query is available in cache not the data. If the SQL Query is not avaiable , then it will be excuted for optimization and return the rows from disk.

Hope you agree with me.

Any constructive criticism will be appreciated.

FYI: Concepts on Query Optimization will explain well.

Have Fun :)
Varad

Default Sort column....

My query looks like this......
SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
How the rest of the data will be displayed? How exactly SQL Server
determines the sorting order columns for rest of the columns?
I am using SQL2K.
Thanks,
Smith
The results will be ordered by COL2 only. If the values are not unique, the
ordering of the remaining data is undefined. You need to specify additional
columns in your ORDER BY clause if you want other data returned in a
particular sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> My query looks like this......
> SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> How the rest of the data will be displayed? How exactly SQL Server
> determines the sorting order columns for rest of the columns?
> I am using SQL2K.
> Thanks,
> Smith
>
|||Dan,
Thanks for the reply.
1. Is it documented anywhere that remaining sort order is undefined?
2. Is it consistent while displaying the data when the order is undefined?
Thanks,
Smith
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> The results will be ordered by COL2 only. If the values are not unique,
the
> ordering of the remaining data is undefined. You need to specify
additional
> columns in your ORDER BY clause if you want other data returned in a
> particular sequence.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Smith John" <JohnSmith56@.hotmail.com> wrote in message
> news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
>
|||> 1. Is it documented anywhere that remaining sort order is undefined?
Not as far as I know. As a rule, undefined behavior is seldom documented.
It is risky to rely on undefined/undocumented behavior because this can
change without notice between versions or service packs and break your code.

> 2. Is it consistent while displaying the data when the order is undefined?
No. Once your ordering criteria is satisfied, the order of the remaining
data depends on the details of the query plan. This may vary depending on
the indexes used, number of processors, concurrent scans, etc. SQL Server
certainly doesn't add the unnecessary overhead of sequencing data without an
explicit ORDER BY unless it is needed for internal query optimization
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> the
> additional
>
|||"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
Yes. In theory: Sets have no order
In SQL 2000 selects return no specific order unless you use an Order BY.
Part of this is because it may be faster for the DB to return it in an order
different from what you want. (for example SOMETIMES you can expect it to
return in the order of the clustered index, but that's simply because it can
read it off the disk faster that way.)

> 2. Is it consistent while displaying the data when the order is undefined?
No. Or rather, it's not guaranteed to be. Now, in my experience, usually
it is, but don't count on it.

> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> the
> additional
>
|||With an SQL query, you specify the resultset. It is up to the RDBMS to
generate the correct result, and it can obtain this result any which way
it likes, just as long as the result is correct.
Everything you don't specify (such as the ordering of two rows with the
same value in column COL2) is by definition unspecified. This is a
property of SQL.
What SQL-Server will do is create a query plan that ensures the ordering
on COL2. The order for duplicates COL2-rows will just be in whatever
order the rows happen to be at that point. Since (in most cases) there
are many ways to achieve the same result, you cannot rely on any
particular order that you did not specify.
Gert-Jan
Smith John wrote:[vbcol=seagreen]
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> the
> additional

Default Sort column....

My query looks like this......
SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
How the rest of the data will be displayed? How exactly SQL Server
determines the sorting order columns for rest of the columns?
I am using SQL2K.
Thanks,
SmithThe results will be ordered by COL2 only. If the values are not unique, the
ordering of the remaining data is undefined. You need to specify additional
columns in your ORDER BY clause if you want other data returned in a
particular sequence.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> My query looks like this......
> SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> How the rest of the data will be displayed? How exactly SQL Server
> determines the sorting order columns for rest of the columns?
> I am using SQL2K.
> Thanks,
> Smith
>|||Dan,
Thanks for the reply.
1. Is it documented anywhere that remaining sort order is undefined?
2. Is it consistent while displaying the data when the order is undefined?
Thanks,
Smith
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> The results will be ordered by COL2 only. If the values are not unique,
the
> ordering of the remaining data is undefined. You need to specify
additional
> columns in your ORDER BY clause if you want other data returned in a
> particular sequence.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Smith John" <JohnSmith56@.hotmail.com> wrote in message
> news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> > My query looks like this......
> > SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> > How the rest of the data will be displayed? How exactly SQL Server
> > determines the sorting order columns for rest of the columns?
> > I am using SQL2K.
> > Thanks,
> > Smith
> >
> >
>|||> 1. Is it documented anywhere that remaining sort order is undefined?
Not as far as I know. As a rule, undefined behavior is seldom documented.
It is risky to rely on undefined/undocumented behavior because this can
change without notice between versions or service packs and break your code.
> 2. Is it consistent while displaying the data when the order is undefined?
No. Once your ordering criteria is satisfied, the order of the remaining
data depends on the details of the query plan. This may vary depending on
the indexes used, number of processors, concurrent scans, etc. SQL Server
certainly doesn't add the unnecessary overhead of sequencing data without an
explicit ORDER BY unless it is needed for internal query optimization
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
>> The results will be ordered by COL2 only. If the values are not unique,
> the
>> ordering of the remaining data is undefined. You need to specify
> additional
>> columns in your ORDER BY clause if you want other data returned in a
>> particular sequence.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Smith John" <JohnSmith56@.hotmail.com> wrote in message
>> news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
>> > My query looks like this......
>> > SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
>> > How the rest of the data will be displayed? How exactly SQL Server
>> > determines the sorting order columns for rest of the columns?
>> > I am using SQL2K.
>> > Thanks,
>> > Smith
>> >
>> >
>>
>|||"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
Yes. In theory: Sets have no order
In SQL 2000 selects return no specific order unless you use an Order BY.
Part of this is because it may be faster for the DB to return it in an order
different from what you want. (for example SOMETIMES you can expect it to
return in the order of the clustered index, but that's simply because it can
read it off the disk faster that way.)
> 2. Is it consistent while displaying the data when the order is undefined?
No. Or rather, it's not guaranteed to be. Now, in my experience, usually
it is, but don't count on it.
> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > The results will be ordered by COL2 only. If the values are not unique,
> the
> > ordering of the remaining data is undefined. You need to specify
> additional
> > columns in your ORDER BY clause if you want other data returned in a
> > particular sequence.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Smith John" <JohnSmith56@.hotmail.com> wrote in message
> > news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> > > My query looks like this......
> > > SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> > > How the rest of the data will be displayed? How exactly SQL Server
> > > determines the sorting order columns for rest of the columns?
> > > I am using SQL2K.
> > > Thanks,
> > > Smith
> > >
> > >
> >
> >
>|||With an SQL query, you specify the resultset. It is up to the RDBMS to
generate the correct result, and it can obtain this result any which way
it likes, just as long as the result is correct.
Everything you don't specify (such as the ordering of two rows with the
same value in column COL2) is by definition unspecified. This is a
property of SQL.
What SQL-Server will do is create a query plan that ensures the ordering
on COL2. The order for duplicates COL2-rows will just be in whatever
order the rows happen to be at that point. Since (in most cases) there
are many ways to achieve the same result, you cannot rely on any
particular order that you did not specify.
Gert-Jan
Smith John wrote:
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > The results will be ordered by COL2 only. If the values are not unique,
> the
> > ordering of the remaining data is undefined. You need to specify
> additional
> > columns in your ORDER BY clause if you want other data returned in a
> > particular sequence.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Smith John" <JohnSmith56@.hotmail.com> wrote in message
> > news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> > > My query looks like this......
> > > SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> > > How the rest of the data will be displayed? How exactly SQL Server
> > > determines the sorting order columns for rest of the columns?
> > > I am using SQL2K.
> > > Thanks,
> > > Smith
> > >
> > >
> >
> >