Row numbering unpredictable

Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/Assuming CustomerName is unique:
select
(select count (*)
from #tmp t1
where t1.CustomerName <= t2.CustomerName) as rownum
, *
from
#tmp t2
order by
t2.CustomerName
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/|||you could create the table first with an ID column, then insert into
it. I suspect (though have no evidence) that the select into #tmp with
an id column created then is having issues with the order by|||(cseemeuk@.googlemail.com) writes:
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
people that it's guaranteed to work. Whether that really is true, I'm not
completely convinced of, but fairly. In any case, SELECT INTO is *not*
guaranteed to work that way, so stay away from it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks - works perfectly. The INTO was the problem - appears to be no
guaranteed order to the IDENTITY(bigint, 1,1)
All sorted
Cheers,
Chris Smith
http://www.cswd.co.uk/|||The order is not guaranteed when you use SELECT INTO.
See
http://support.microsoft.com/defaul...kb;en-us;273586
For a list of paging options see
http://www.aspfaq.com/show.asp?id=2120
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
> Hi,
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
> (in ADO :-P)
> This is SQL 2000 SP3 (pah!)
> Cheers,
> Chris Smith
> http://www.cswd.co.uk/
>|||One would think this type of thing,so common and important,
would have a kb or something written by MS.Are you aware of any
link?If none exists I would ask you to kindly request something in
'writing'.Key points of an enterprise database should not be rattling
around just in someone head! :)
Clarity,clarity and nothing but clarity.
Regards from:
www.rac4sql.net
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A28BF07D031Yazorman@.127.0.0.1...
> (cseemeuk@.googlemail.com) writes:
> Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
> BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
> people that it's guaranteed to work. Whether that really is true, I'm not
> completely convinced of, but fairly. In any case, SELECT INTO is *not*
> guaranteed to work that way, so stay away from it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Steve Dassin wrote:
> One would think this type of thing,so common and important,
> would have a kb or something written by MS.Are you aware of any
> link?If none exists I would ask you to kindly request something in
> 'writing'.Key points of an enterprise database should not be rattling
> around just in someone head! :)
> Clarity,clarity and nothing but clarity.
http://support.microsoft.com/defaul...kb;en-us;273586
Do not assume that article means that all INSERTs will always cause
IDENTITY to be generated in a predetermined order. There are at least
some situations where that doesn't work - whether by design or a bug I
can't say.
Perhaps the safest course is to assume that you cannot control the
IDENTITY sequence with ORDER BY. In my view the wisest and most logical
solution is to use other methods like the ROW_NUMBER function for
example.
I can think of at least two good reasons for not using IDENTITY the way
proposed by the KB. Firstly IDENTITY is normally intended as an
arbitrary surrogate key - using the values in any "meaningful" way is a
compromise you don't need and is something it just isn't designed for.
Secondly, this supposed behaviour of an "ordered" INSERT looks contrary
to the set-based nature of an INSERT statement. Whether or not it works
today, it seems undesirable to assume that it should always work that
way in future. One would hope and expect that the engine could optimise
out any redundant sorting in INSERT...SELECT queries. That seems to be
what happens in some cases today and maybe it will happen more often in
future versions due to improvements in the optimiser. Just some things
to bear in mind.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> Anyone got any ideas on how to solve that other than client side paging <
<
The basic principle of a tiered architecture is that display is done in
the front end adn NEVER in the database. Why are you sing violating
40 years of Software Engineering?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144803667.948395.290650@.i40g2000cwc.googlegroups.com...
<<
> The basic principle of a tiered architecture is that display is done in
> the front end adn NEVER in the database. Why are you sing violating
> 40 years of Software Engineering?
>
Forty years of a life sentence is enough.Time to let the innocent free.
Convicted on trumped up,unsubstantiated and false charges.In other words,
NONSENSE.
The thread:
Monday, April 10, 2006 9:48 PM
microsoft.public.sqlserver.programming
Re: Membership Timeline Spanning
contains a response that further clarifies things:
"Itzik Ben-Gan" writes
>.
>In my previous reply I mentioned the ANSI OVER clause (with an ORDER BY
>option). It is really brilliant, and I wonder if the designers of the
>feature themselves knew how profound it is. I believe this option to be the
>bridge between cursors and sets; sort of the holy grail of SQL. :-)
To quote Bob Dylan:
'I would not feel so alone if everyone where getting stoned':)
Yes I agree with you in principal.The 'real' paradign shift has
little to do with the clr and everything to do with exploding
the perverted myth of the exclusivity of'set based' constructs.
The idea one can legitimately think in terms of rows without being
labelled an sql Jodus has arrived.But calling this windowing a
'profound' kind of insight and bestowing on the designers the aura
of 'brilliance' would be a mistake.It is at best an example of
'better late than never'.Calling this state of affairs profound
would surely overshadow the accountability that the commericial
database world should be held to.The fact that this mindset change
has taken almost 30 years should be seen as appalling.Neo-cons of
the industry had hijacked sense with sql creationism and marketing.
WMD was replaced with client/server and a tiered approach.A theory
was misapplied to a retrival mechanism and unapplied to a design
mechanism.An approach that vendors marketted that allowed them to
hide both their intellectual and creative shortcomings.Their db
failures made for the 'client'.And now the clr in the db has replaced
the client.And of course the dreaded cursor.This demanded regime
change and the field was bankrupted for 30 years.For this we are to
praise Ceasar?I think not.
It is interesting to look at the fanfare that vendors are using
to usher in this new paradign.In their documentation Oracle refers
to their analytic functions in windows as an example of
'data densification'.This phrase is supposed to illustrate the
flip side of the Group By.It was obviously borrowed from the idea
of pacification,right out of the Pentagon.This is the best they could
come up with?Any army of engineers berefit of language and concepts.
Not to be out done,MS in its highly touted BOL offers the next best
thing - absolutely Nothing!No explanations,no history no seqways.
The functions are thrown around like so much spaghetti on a wall.
If you write about concepts someone may quote you.MS needn't worry
now.Least I be accused of favortism,IBM was too busy pleasing its
shareholders to write anything intelligible.
Finally,to your point about MS leaving out a large chunk of analytic
material this was obviously not an oversight but just insurance
that anything done with sql-99 could most definitly be easily ported
to the competition.Less is more.Please!If they weren't sure of
what they were doing they could have at least looked at Oracle
which is probably about 8 years ahead.Or even looked at RAC to see what
you and I are really talking about :)
Interested readers maybe surprised that many of the ideas in sql
analytics can be found in the SAS (Statistical Analysis System) Data
Step...introduced about 20 years ago!Many of the Oracle extensions
(First/Last) can also be found here.MySql allows mixing of variables
and columns in a SELECT.Most of the analytics can be easily simulated
in a single SELECT.And of course little RAC, way ahead of its time:)
Some musing from:
www.rac4sql.net