Order of cursor walk

I have table Stock which represents products in stock.
When new order came (table orders) I must reserve items in stock for that
order (table reservations).
The way I work is, that I first find all appropriate stock for my order,
walk through it and reserve the quantity until I reach
order quantity:
declare @.stockQ decimal(15,5),@.orderQ decimal(15,5),@.orderID int,@.stockID
int,@.orderIDold int,@.work bit,@.quantity decimal(15,5)
set @.orderIDold=0
declare cCur cursor local for
SELECT o.orderID,s.stockID,o.quantity,s.quantity from orders o LEFT JOIN
stock s
ON o.productID=s.productID
AND (o.quality is null OR o.quality=s.quality)
AND (o.producer is null OR o.producer=s.producer)
open cCur
fetch next from cCur into @.orderID,@.stockID,@.orderQ,@.stockQ
while @.@.fetch_status=0
begin
set @.stockQ=@.stockQ-(SELECT isnull(sum(quantity),0) from reservations
where stockID=@.stockID)
if isnull(@.stockQ,0)>0
begin
if @.orderID<>@.orderIDold
begin
SET @.work=0
SET @.orderIDold=@.orderID
set @.quantity=@.orderQ
end
if @.work=0--(if @.work=1 then all quantity for this order was
already reserved)
begin
if @.stockQ>=@.quantity
begin
INSERT INTO reservations
SELECT @.orderID,@.stockID,@.quantity
set @.work=1
end
else
begin
INSERT INTO reservations
SELECT @.orderID,@.stockID,@.stockQ
set @.quantity=@.quantity-@.stockQ
end
end
end
fetch next from cCur into @.orderID,@.stockID,@.orderQ,@.stockQ
end
close cCur
deallocate ccur
GO
It works.
The problem is, when I have in table orders more products with the same ID
and different other parameters.
With sample data you can see (just copy the script below and run my query)
that reservation that this query create is
ORDERID STOCKID quantity
---
1 1 100
Instead I reserve both orders I reserve only first.
So, I should include somehow the right order of select statement.
The reservation should be:
ORDERID STOCKID quantity
---
1 2 100
2 1 100
How can I solve that? Any idea?
This is simple example I use to explain the situation.The real example has
many parameters, not only quality and producer, and many
different products with different quantity.
So, the real combination shoul reserve the maximum possible orders.
The sample data with tables:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[stock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[stock]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[orders]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[orders]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[reservations]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[reservations]
GO
CREATE TABLE [dbo].[stock] (
[stockID] [int] NOT NULL ,
[productID] [int] NOT NULL ,
[quality] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NOT NULL ,
[producer] [varchar] (10) COLLATE SQL_Slovenian_CP1250_CI_AS NULL ,
[quantity] [decimal](15, 5) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[orders] (
[orderID] [int] NOT NULL ,
[productID] [int] NULL ,
[quality] [char] (1) COLLATE SQL_Slovenian_CP1250_CI_AS NULL ,
[producer] [varchar] (10) COLLATE SQL_Slovenian_CP1250_CI_AS NULL ,
[quantity] [decimal](15, 5) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[reservations] (
[orderID] [int] NOT NULL ,
[stockID] [int] NOT NULL ,
[quantity] [decimal](15, 5) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[stock] ADD
CONSTRAINT [PK_products] PRIMARY KEY CLUSTERED
(
[stockID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[orders] ADD
CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED
(
[orderID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[reservations] ADD
CONSTRAINT [PK_reservations] PRIMARY KEY CLUSTERED
(
[orderID],
[stockID]
) ON [PRIMARY]
GO
INSERT INTO orders(orderID,productID,producer,quanti
ty) VALUES (1,1,'2',100)
INSERT INTO orders(orderID,productID,quality,quantit
y) VALUES (2,1,'A',100)
INSERT INTO stock(stockID,productID,quality,producer
,quantity) VALUES
(1,1,'A','2',100)
INSERT INTO stock(stockID,productID,quality,producer
,quantity) VALUES
(2,1,'B','2',100)
regards,SCursors don't have a predictable order unless you specify ORDER BY in
the cursor declaration.
You can do this possibly more efficiently without a cursor. Google for
FIFO and stock inventory in this group to find some examples.
David Portas
SQL Server MVP
--|||well, I mean the order of SELECT statement which cursor use.
I don't know how to find the right order. There is a lot of combinations.
I thought, the result of this simple example I posted should give me the
right guidline in real example.
I already look for fifo in google but haven't find the similar example.
Can you help me?
thanks ,S
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128676869.547922.139950@.g44g2000cwa.googlegroups.com...
> Cursors don't have a predictable order unless you specify ORDER BY in
> the cursor declaration.
> You can do this possibly more efficiently without a cursor. Google for
> FIFO and stock inventory in this group to find some examples.
> --
> David Portas
> SQL Server MVP
> --
>|||Take a look at this thread:
http://groups.google.co.uk/group/mi...b653f3ad3fcaa5e
David Portas
SQL Server MVP
--

Order of Articles Replicated

Is there any way to specify the order of the articles replicated? What is happending is my merge replication is erroring because a table is being replicated that contains a computed column based on a user-defined function, but the user-defined fuction hasn't been replicated yet.

But I'm not sure replicating the user-defined functions first would solve the problem because they would probably blow up because the tables upon which they are based haven't been replicated yet.

So, how has anyone else solved this?

Thanks...

Scott

You can specify a script to run before or after the snapshot is applied. Create a script that checks to see if your function exists and create it if it does not. Then set that to run before the snapshot.

Go to the properties of your publication and select Subscription Options see section Run additional scripts.

I am not sure you can specify the order of the deployment but this would solve your probem.

Martin

|||Another option is to use sp_addscriptexec for in-flight scenarios.|||But to answer your original question, you can specify article ordering of tables but not other types of objects like views, functions, procs, etc. We're looking to fix this.

Order of Articles Replicated

Is there any way to specify the order of the articles replicated? What is happending is my merge replication is erroring because a table is being replicated that contains a computed column based on a user-defined function, but the user-defined fuction hasn't been replicated yet.

But I'm not sure replicating the user-defined functions first would solve the problem because they would probably blow up because the tables upon which they are based haven't been replicated yet.

So, how has anyone else solved this?

Thanks...

Scott

You can specify a script to run before or after the snapshot is applied. Create a script that checks to see if your function exists and create it if it does not. Then set that to run before the snapshot.

Go to the properties of your publication and select Subscription Options see section Run additional scripts.

I am not sure you can specify the order of the deployment but this would solve your probem.

Martin

|||Another option is to use sp_addscriptexec for in-flight scenarios.|||But to answer your original question, you can specify article ordering of tables but not other types of objects like views, functions, procs, etc. We're looking to fix this.sql

Order in the middle of a table

Hi,
I publish a table on a website. At the end of each row I want to show an up-
and downbutton. The buttons haves an id as parameter. Inside my table I have
a vieworder column, this is the way I sort the table.
What I want is that when you press on the up- or downbutton that record
moves up or down. This means an update of that record and the record above
or below. I want to know what is the best way how to realize this.
Ideas? Are there (build-in) funtions for this?
Thank!You could easily write a stored proc to do this. One of the many
wonderful things about the UPDATE statement in the SQL language is that
it is an all-at-once operation. You can increment the vieworder for a
row and either increment or decrement its neighbour as appropriate all
at the same time. Try this batch:
use tempdb
go
-- A little setting up...
create table #tmp
(
vieworder int not null,
blah char(1) primary key clustered
)
insert into #tmp (vieworder, blah) values (1, 'A')
insert into #tmp (vieworder, blah) values (2, 'B')
insert into #tmp (vieworder, blah) values (3, 'C')
insert into #tmp (vieworder, blah) values (4, 'D')
insert into #tmp (vieworder, blah) values (5, 'E')
insert into #tmp (vieworder, blah) values (6, 'F')
insert into #tmp (vieworder, blah) values (7, 'G')
insert into #tmp (vieworder, blah) values (8, 'H')
insert into #tmp (vieworder, blah) values (9, 'I')
insert into #tmp (vieworder, blah) values (10, 'J')
-- --
-- the row to shuffle
declare @.id int
set @.id = 10
-- The original result set
select * from #tmp order by vieworder
-- Shuffle up
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder + 1)
when (vieworder = @.id + 1) then (vieworder - 1)
end
where (vieworder = @.id or vieworder = @.id + 1) -- Only
shuffle the 2 rows in question
and exists (select * from #tmp where vieworder = @.id + 1) -- Just
to make sure you don't shuffle a row "off the end"
-- Let's see it after the shuffle up
select * from #tmp order by vieworder
-- Shuffle down
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder - 1)
when (vieworder = @.id - 1) then (vieworder + 1)
end
where (vieworder = @.id or vieworder = @.id - 1) -- Only
shuffle the 2 rows in question
and exists (select * from #tmp where vieworder = @.id - 1) -- Just
to make sure you don't shuffle a row "off the end"
-- Let's see it after the shuffle down
select * from #tmp order by vieworder
-- Tidy up
drop table #tmp
Embed that kind of logic inside a stored procedure and then you just
call the stored proc with the appropriate parameters (a direction to
shuffle and a baseline row to shuffle along with its neighbour) from
your webpage using ADO (with a postback to reload the same webpage I guess).
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Arjen wrote:

>Hi,
>I publish a table on a website. At the end of each row I want to show an up
-
>and downbutton. The buttons haves an id as parameter. Inside my table I hav
e
>a vieworder column, this is the way I sort the table.
>What I want is that when you press on the up- or downbutton that record
>moves up or down. This means an update of that record and the record above
>or below. I want to know what is the best way how to realize this.
>Ideas? Are there (build-in) funtions for this?
>Thank!
>
>|||Okay, thanks!
I will try this.
Arjen
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> schreef in bericht news:e
yGZJViqFHA.564@.TK2MSFTNGP10.phx.gbl...
You could easily write a stored proc to do this. One of the many wonderful
things about the UPDATE statement in the SQL language is that it is an all-a
t-once operation. You can increment the vieworder for a row and either incr
ement or decrement its neighbour as appropriate all at the same time. Try t
his batch:
use tempdb
go
-- A little setting up...
create table #tmp
(
vieworder int not null,
blah char(1) primary key clustered
)
insert into #tmp (vieworder, blah) values (1, 'A')
insert into #tmp (vieworder, blah) values (2, 'B')
insert into #tmp (vieworder, blah) values (3, 'C')
insert into #tmp (vieworder, blah) values (4, 'D')
insert into #tmp (vieworder, blah) values (5, 'E')
insert into #tmp (vieworder, blah) values (6, 'F')
insert into #tmp (vieworder, blah) values (7, 'G')
insert into #tmp (vieworder, blah) values (8, 'H')
insert into #tmp (vieworder, blah) values (9, 'I')
insert into #tmp (vieworder, blah) values (10, 'J')
-- --
-- the row to shuffle
declare @.id int
set @.id = 10
-- The original result set
select * from #tmp order by vieworder
-- Shuffle up
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder + 1)
when (vieworder = @.id + 1) then (vieworder - 1)
end
where (vieworder = @.id or vieworder = @.id + 1) -- Only shuffle t
he 2 rows in question
and exists (select * from #tmp where vieworder = @.id + 1) -- Just to make s
ure you don't shuffle a row "off the end"
-- Let's see it after the shuffle up
select * from #tmp order by vieworder
-- Shuffle down
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder - 1)
when (vieworder = @.id - 1) then (vieworder + 1)
end
where (vieworder = @.id or vieworder = @.id - 1) -- Only shuffle t
he 2 rows in question
and exists (select * from #tmp where vieworder = @.id - 1) -- Just to make s
ure you don't shuffle a row "off the end"
-- Let's see it after the shuffle down
select * from #tmp order by vieworder
-- Tidy up
drop table #tmp
Embed that kind of logic inside a stored procedure and then you just call th
e stored proc with the appropriate parameters (a direction to shuffle and a
baseline row to shuffle along with its neighbour) from your webpage using AD
O (with a postback to reload the same webpage I guess).
Hope this helps.
mike hodgson
blog: http://sqlnerd.blogspot.com
Arjen wrote:
Hi,
I publish a table on a website. At the end of each row I want to show an up-
and downbutton. The buttons haves an id as parameter. Inside my table I have
a vieworder column, this is the way I sort the table.
What I want is that when you press on the up- or downbutton that record
moves up or down. This means an update of that record and the record above
or below. I want to know what is the best way how to realize this.
Ideas? Are there (build-in) funtions for this?
Thank!

Order by with select into

I have a Microsoft SQL Server 7.0.

I wrote a sql command that creates a temporary table with a ORDER BY
clause.

When a execute a SELECT on this temporary table sometimes the result is
ok, but sometimes is not ordered. I didnt see anything like that. Any
clue?

Is there any kind of limits with temporary tables ? Because the command
that creates the temporary table is working and the rsults is always
ordered. But when I create a table with it, sometimes the table is not
ordered.

Paulo

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Paulo Andre Ortega Ribeiro" <anonymous@.devdex.com> wrote in message
news:3f0b0ac2$0$202$75868355@.news.frii.net...
> I have a Microsoft SQL Server 7.0.
> I wrote a sql command that creates a temporary table with a ORDER BY
> clause.
> When a execute a SELECT on this temporary table sometimes the result is
> ok, but sometimes is not ordered. I didnt see anything like that. Any
> clue?
> Is there any kind of limits with temporary tables ? Because the command
> that creates the temporary table is working and the rsults is always
> ordered. But when I create a table with it, sometimes the table is not
> ordered.
> Paulo

Rows in tables (temporary or permanent) never have an order, even if you
used ORDER BY when you did the INSERT, and even if there's a clustered index
on the table. The only way to be sure you get ordered data is to use ORDER
BY when you SELECT it.

Some tables, especially with clustered indexes, may look like the data is
ordered, but you can't assume it will always work. You could think of it
like this - when you SELECT from the table, you create a result set, and
ORDER BY only works on the result set, not on the table.

Is there some specific reason that you want to order data in the table,
instead of using ORDER BY in your queries? If there is, then maybe you could
give some more details about what you are trying to do, and someone might be
able to suggest a different solution.

Simon|||Paulo,

Tables do not have an order. In other words, tables by definition are
logically an unordered set of rows. Using ORDER BY in a INSERT...SELECT or
SELECT...INTO does not mean that the data in the table is 'ordered'. The
order of rows which you see when you do a SELECT without an ORDER BY clause
is a undefined/arbitrary order chosen by the optimizer based on the physical
characteristics, indexes, access paths, complexity of joins if any, other
statistical information etc. & many undocumented factors. Hence you should
not rely on the 'order' of rows in a table which you see on the screen.

To repeat, rows in a table do not have a logical order. The only guaranteed
way of retrieving rows in a specific order is to use an ORDER BY clause in
your SELECT statement.

--
- Anith
( Please reply to newsgroups only )sql

Order by with Insert into?

I'm selecting records from a table and inserting them into another existing table

Use DSRBQ000
INSERT INTO dbo.db_table_information
Select Table_Name, Column_Name + ' ' + Upper(data_type) +
CASE WHEN data_type IN('binary','char','nchar','nvarchar','varbinary', 'varchar') THEN '('
+ Cast(character_maximum_length AS varchar(10))+')'
WHEN data_type IN('decimal','numeric') THEN '(' + Cast(numeric_precision as varchar(3)) + ','
+ Cast(numeric_scale as varchar(3))+ ')'
Else ''
End +
CASE WHEN columnproperty(object_id(table_name),column_name,' IsIdentity')= 1 THEN ' IDENTITY' +
'(' + Cast(ident_seed(table_name) AS varchar(10)) + ',' + Cast(ident_incr(table_name) AS varchar(10)) + ')'
Else ''
End +
CASE WHEN is_nullable = 'YES' THEN ' NULL'
ELSE ''
END 'Column_Definition', ordinal_position
from information_schema.columns
where table_name IN(select distinct table_name from information_schema.tables where table_type = 'BASE TABLE')
and table_name NOT IN('dtproperties','dbo.db_table_information')
order by table_name, ordinal_position

I'm trying to first order by table_name and then ordinal_position. However, when viewing the table that it data is getting inserted into, I notice that even though it is ordered by table_name, sometimes a row is out of order according to ordinal position.

Is there a valid reason for this? Are you not allow to select the order from which a recordset gets inserted into a table? If thats the case, how can I update the db_table_information and save it so it is in table_name, ordinal_position order?Inserting a sorted record set is usally a wast of time as there is most likely an index in place. When you issue a select your result set will be based on the clustered index or first non-clustered index created for the table.

If you always want db_table_information to be in table_name, ordinal_position order then create an index on those attributes.|||figured out why this occured.. i defined ordinal position as char instead of a number.|||DOH! If only these computers would do as we want rather than do as we ask the world would be a better place!

ORDER BY using @variables

Hi
I realise you can't declare a column name as a @.variable:
SELECT name, address FROM table WHERE name = 'smith' ORDER BY @.column
....but other than using dynamic SQL, is there a better way i can ORDER a query using various columns?
thankssomething like this perhaps?order
by case @.flag
when 1 then column1
when 2 then column2
when 3 then column3
else null end|||Ordering logic belongs at the presentation layer, not the database layer.|||WHAT??!!!!

please explain, oh database guru

you're suggesting that we ditch the ORDER BY clause altogether?

something about that idea just doesn't sit too well with me...|||I'm gonna report this thread|||Thanks for that r937, works spot on!

Just have another question, for some reason i get the following error when trying to add the case statement to a UNION query:

ORDER BY items must appear in the select list if the statement contains a UNION operator.

Now i know that you can define a single ORDER BY clause in a union query and that it should appear after the last SELECT which it is. I've tried manually putting the ORDER BY clause into the query and it works fine, but it just doesnt like the case statement for some reason? Is there a way round it?

cheers|||Just note two conditions on performing UNIONS :-

The number and the order of the columns must be the same in all queries.
The data types must be compatible.|||Is there a way round it?there might be, but i can't really help because i can't see the query from here|||sorry, should have put it in - this is a simlified version of my MSSQL query - it still brings up the same error:

SELECT business_name, address1
FROM VENUE
WHERE (business_name LIKE '%' + @.v_name + '%')
UNION
SELECT business_name, address1
FROM AHOTELS
WHERE business_name Like '%' + @.v_name + '%'
ORDER BY CASE @.sortBy
WHEN 1 THEN business_name
WHEN 2 THEN postcode
WHEN 3 THEN town
ELSE NULL END|||WHAT??!!!!

please explain, oh database guru
Ordering is normally a presentation issue. Databases should not be concerned with the manner in which data is presented.
you're suggesting that we ditch the ORDER BY clause altogether?
Of course not. Would you suggest that we ditch cursors just because they are often misused?|||it still brings up the same error:which is... ?|||Ordering is normally a presentation issue. Databases should not be concerned with the manner in which data is presented.next time i see you post a SELECT statement with an ORDER BY clause, you are gonna get hit with a big can of whoopass from me, then|||This is the error:

ORDER BY items must appear in the select list if the statement contains a UNION operator.|||well, that's pretty clear, isn't it ;)

SELECT business_name, address1, postcode, town
FROM VENUE
WHERE (business_name LIKE '%' + @.v_name + '%')
UNION
SELECT business_name, address1, postcode, town
FROM AHOTELS
WHERE business_name Like '%' + @.v_name + '%'
ORDER BY CASE @.sortBy
WHEN 1 THEN business_name
WHEN 2 THEN postcode
WHEN 3 THEN town
ELSE NULL END|||but I'm sure they don't want it in the result set|||but I'm sure they don't want it in the result set
so what's your suggestion in that case?

are you going to join the blindman parade and suggest that ordering should be done in the front end application?

if you don't include postcode and town in the result set, and then pass that result set to the front end app, then how are you gonna sort by postcode or town??

come on, brett, you're a smart guy, i'd like to see your solution|||hi, sorry, the code i pasted was a shortened version and the original did include postcode and town (apologies, will not shorten code in the future!)

I'm using the column names in the first SELECT, is this right?

Also, as i've mentioned, when i use a single ORDER BY, eg:

...
ORDER BY business_name

this works, but if i try:

...
ORDER BY CASE @.sortBy
WHEN 1 THEN business_name
ELSE NULL END

This surely should do the same thing (assuming @.sortBy is 1) but does not work and comes up with that error!! (btw i'm using MS SQL server managemnt studio, and the error comes up when i try to execute(save) the stored proc!)|||Nobody open a can of whoopass on me please :)


ORDER BY CASE
WHEN @.sortBy = 1 THEN business_name
ELSE NULL END


EDIT - misread the SQL - sorry no real change made|||When heating a can of whoopass, should I mix it with 1 can of water or 1 can of milk?

Also, will one can of whoopas be enough to feed everybody in my parade? Or will you open up a family-size can of whoopass for me?

You won't find ORDER BY in my select statements unless specifically requested by the developers and then it would be against my advice. But anyway, I consider the ordering of data in result sets to be at most a minor transgression.|||Here is the essence of the problem, then?

create table test1
(col1 int, col2 varchar(30))

create table test2
(col3 int, col4 varchar(30))

insert into test1 values (1, 'hello')
insert into test1 values (3, 'aloha')
insert into test2 values (2, 'bye')
insert into test2 values (4, 'auf wiedersehen')

select col1, col2 from test1
union
select col3, col4 from test2
order by col1

select col1, col2 from test1
union
select col3, col4 from test2
order by case when 1 = 1 then col1
when 1 = 2 then col2 end -- OK. Dummy cases, but it generates an error.

drop table test1
drop table test2

Now, that is a conundrum. This will take some thought...|||just found a blog here (http://www.sqlblogs.com/top/ng/group~22/~117449~__order-by-and-UNION/index.aspx)which refers to a ANSI SQL-92 standard (??) that you can't use Expressions in an ORDER clause when used with a UNION!! Does this sound right??

If so, are there any other methods which i can try which do the same as a UNION?

ta|||yup MCrowley, that is the problem in essence!!|||This is ugly, but it gets the job done. Performance-wise it is probably the same thing:

select * from
(select col1, col2 from test1
union
select col3 , col4 from test2) a
order by case when 1 = 1 then col1
when 1 = 2 then col2 end

EDIT: Removed confusing extra characters.|||New problem:

select * from
(select col1, col2 from test1
union
select col3 , col4 from test2) a
order by case when 1 = 2 then col1
when 1 = 1 then col2 end

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'hello' to a column of data type int.

Are all of your datatypes in the order by the same, or at least similar datatypes?|||yup, they are all varchar apart from @.sortBy which is an int. Like i said before, the UNION works fine on its own and with a single ORDER BY clause, its only when the case statement gets thrown into it that is has an error!|||Then you should be all set with the solution in post 23. the problem only comes up when you have an int and a varchar as a result of the case statement.|||Ok thanks MCrowley, will try it!

ORDER BY specific values

Hi Everyone,
I was wondering if there is anyway to ORDER BY in a query by certain values first. I have a table with Projects and subprojects and sub-subprojects and I always want to display the parent project first. Is there anyway that I can do that.
Thanks for all your help.Could you give us a little inside information about your data-structure, eventually accompanied with some sample data and the way you would like to have it ?sql

ORDER BY question: splitting string into 2 orders?

I have a column named "LIST" in a table with strings like the following:

151231-1002-02-1001
151231-1001-02-1001
151231-1002-02-1002
151231-1003-02-1001
etc...

What I'd like to do is include an ORDER BY statement that splits the
string, so that the order would be by the second set of four numbers
(i.e. between the first and second - marks), followed by the third set
of two numbers, and then by the last set of four numbers.

How would I do something like this?

--
Sugapablo - russpghREMOVE@.stargate.net
http://www.sugapablo.com | ICQ: 902845If this is a fixed width column with fixed formats, you can use substring to
parse the value like:

SELECT *
FROM tbl
ORDER BY SUBSTRING(col, 8, 4),
SUBSTRING(col, 13, 2),
RIGHT(col, 4) ;

If these are variable length formatted, then you have more work to do:

SELECT *
FROM tbl
ORDER BY SUBSTRING(col,
CHARINDEX('-', col) + 1,
CHARINDEX('-', col,
CHARINDEX('-', col) + 1) -
CHARINDEX('-', col) - 1),
REVERSE(SUBSTRING(REVERSE(col),
CHARINDEX('-', REVERSE(col)) + 1,
CHARINDEX('-', REVERSE(col),
CHARINDEX('-', REVERSE(col)) + 1) -
CHARINDEX('-', REVERSE(col)) - 1)),
REVERSE(SUBSTRING(REVERSE(col), 1,
CHARINDEX('-', REVERSE(col)) - 1)) ;

Another trick is to use PARSENAME function. Note that the return expression
for PARSENAME function is unicode though. See SQL Server Books Online for
more details.

SELECT *
FROM tbl
ORDER BY PARSENAME(REPLACE(col, '-', '-'), 3),
PARSENAME(REPLACE(col, '-', '-'), 2),
PARSENAME(REPLACE(col, '-', '-'), 1) ;

If each of these portions are of business significance, why are you
representing them as a single column? If consolidation is needed for certain
specific requirements, you can use a view for such representation.

--
- Anith
( Please reply to newsgroups only )

ORDER BY Question

I have a table which contains a sNAME field - a persons name of "firstame space
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
...or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
Brian
Brian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local ...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>
|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query[vbcol=seagreen]
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local ...
any
>
|||Jerry,
It worked and it was quick too...thanks again
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local ...
>

Order By Question

Hi,
I have a table with a field i sort on which has values like: p100-01,
p101-02, p999-05, p1000-03. On my order by shows these values like this:
p100-01
p1000-03
p101-02
p999-05
Is there a way around this or since its alpha numeric I am stuck with this?
ThanksYou can create a column that pads the strings out the way you want them
and do an ORDER BY it.
CASE WHEN foo_nbr LIKE 'p[0-9][0-9][0-9]-[0-9][0-9]'
THEN SUBSTRING (foo-nbr, 1,4) + '0' + SUBSTRING (foo-nbr,
5,7)
ELSE foo_nbr END AS sort_col|||if your pattern is always as illustrated (i.e., single
char+number+dash+number) then try
order by convert(decimal(30,5), replace(substring(value,2,9), '-', '.'))
you'll want to adjust the decimal precision and scale based on how many
numbers you'll have before and after the dash, if it's constant or at
least has a specified range.
also, this will not be a fast order, except on a small result set
Kyle wrote:
> Hi,
> I have a table with a field i sort on which has values like: p100-01,
> p101-02, p999-05, p1000-03. On my order by shows these values like this:
> p100-01
> p1000-03
> p101-02
> p999-05
> Is there a way around this or since its alpha numeric I am stuck with this
?
> Thanks
>|||... ["value" in this represents the column name] ...
Trey Walpole wrote:
> if your pattern is always as illustrated (i.e., single
> char+number+dash+number) then try
> order by convert(decimal(30,5), replace(substring(value,2,9), '-', '.'))
> you'll want to adjust the decimal precision and scale based on how many
> numbers you'll have before and after the dash, if it's constant or at
> least has a specified range.
> also, this will not be a fast order, except on a small result set
> Kyle wrote:
>|||On Fri, 9 Sep 2005 13:41:47 -0600, Kyle wrote:

>Hi,
>I have a table with a field i sort on which has values like: p100-01,
>p101-02, p999-05, p1000-03. On my order by shows these values like this:
>p100-01
>p1000-03
>p101-02
>p999-05
>Is there a way around this or since its alpha numeric I am stuck with this?
>Thanks
>
Hi Kyle,
This requirement indicates that the value is not atomic, but that the
numeric parts have different meaning. Instead of using a kludge to fix
the sorting, it's probably better to split the various parts of this
data into seperate columns, and concatenate them when selecting the
data.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I developed a query to do natural alphanumeric sorting. This query breaks
the alphanumeric string up into chunks of char's and number's, then orders b
y
the chunks. It only works for the first 4 chunks. I haven't had the time,
but I am sure that you could build a trigger to populate a,b,c and d columns
and sort by that.
Archer
select TOP 100 fieldid as 'sortcolumn',
LEFT(fieldid, PATINDEX('%[0-9]%', fieldid) - 1) as 'a',
CASE
WHEN right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ) not
like '%[a-z,-]%'
then CAST(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1
) AS INT)
else
cast (
left (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
case
when patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) = 0
then patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 ))
else patindex('%[a-z, -]%', right (fieldid, len(fieldid) -
PATINDEX('%[0-9]%', fieldid) + 1 )) -1
end
)
as int)
end
as 'b',
LEFT (
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
,
CASE
WHEN PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ))
- patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) = 0
THEN
LEN( right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ))
- patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
)
ELSE PATINDEX('%[0-9]%',
right
(
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ))
- patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) -1
END
)
as 'c',
CASE
WHEN right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) not like '%[a-z,-]%'
then CAST(right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ) AS INT)
else
cast (
left (
right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ),
case
when patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) = 0
then patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 ))
else patindex('%[a-z, -]%', right (
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
, len(
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) - PATINDEX('%[0-9]%',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
)
) + 1 )) -1
end
)
as int)
end
as 'd',
right (
right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 ),
len(right (fieldid, len(fieldid) - PATINDEX('%[0-9]%', fieldid) + 1 )) -
patindex('%[a-z, -]%', right (fieldid, len(fieldid) - PATINDEX('%[0-9]%',
fieldid) + 1 )) + 1
) as 'cTEST'
from tblsamplesTEST
order by a,b,c,d
"Kyle" wrote:

> Hi,
> I have a table with a field i sort on which has values like: p100-01,
> p101-02, p999-05, p1000-03. On my order by shows these values like this:
> p100-01
> p1000-03
> p101-02
> p999-05
> Is there a way around this or since its alpha numeric I am stuck with this
?
> Thanks
>
>|||Kyle wrote:
> I have a table with a field i sort on which has values like: p100-01,
> p101-02, p999-05, p1000-03. On my order by shows these values like this:
> p100-01
> p1000-03
> p101-02
> p999-05
> Is there a way around this or since its alpha numeric I am stuck with this?[/color
]
If only the first number has a variable length, but the second number is
always two digits:
order by len(i), i
Dieter

ORDER BY Question

I have a table which contains a sNAME field - a persons name of "firstame space
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
..or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
BrianBrian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
> >I have a table which contains a sNAME field - a persons name of "firstame
> >space
> > lastname".
> >
> > I have a query that delivers a recordset using "ORDER BY sName"
> >
> > Now, a client has asked to see the data sorted by "lastname". Is there
any
> > method using an SQL expression to deliver that recordset sorted by
> > lastname?
> >
> > ..or do I have to re-organize my data fields and data?
> >
> > I was hoping for a quick 'sql function' solution instead of changing the
> > database.
> >
> > Brian
> >
>|||Jerry,
It worked and it was quick too...thanks again
--
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN>)+ 1,
> DATALENGTH(<COLUMN>) - CHARINDEX(' ',<COLUMN>)) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>>I have a table which contains a sNAME field - a persons name of "firstame
>>space
>> lastname".
>> I have a query that delivers a recordset using "ORDER BY sName"
>> Now, a client has asked to see the data sorted by "lastname". Is there
>> any
>> method using an SQL expression to deliver that recordset sorted by
>> lastname?
>> ..or do I have to re-organize my data fields and data?
>> I was hoping for a quick 'sql function' solution instead of changing the
>> database.
>> Brian
>

ORDER BY Question

I have a table which contains a sNAME field - a persons name of "firstame sp
ace
lastname".
I have a query that delivers a recordset using "ORDER BY sName"
Now, a client has asked to see the data sorted by "lastname". Is there any
method using an SQL expression to deliver that recordset sorted by lastname?
..or do I have to re-organize my data fields and data?
I was hoping for a quick 'sql function' solution instead of changing the
database.
BrianBrian,
You could use functions to determine the lastname and sort by that but that
technique would probably preclude the use of NC indexes to resolve the query
and may negatively impact performance. Something like (or some variation
of):
SELECT <COLUMN LIST.
FROM <TABLE>
ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
HTH
Jerry
"Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>I have a table which contains a sNAME field - a persons name of "firstame
>space
> lastname".
> I have a query that delivers a recordset using "ORDER BY sName"
> Now, a client has asked to see the data sorted by "lastname". Is there any
> method using an SQL expression to deliver that recordset sorted by
> lastname?
> ..or do I have to re-organize my data fields and data?
> I was hoping for a quick 'sql function' solution instead of changing the
> database.
> Brian
>|||Jerry,
Thanks...I think that will do for now. The table only has a max of 400 rows
in it.
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
that
> technique would probably preclude the use of NC indexes to resolve the
query
> and may negatively impact performance. Something like (or some variation
> of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
> DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
any[vbcol=seagreen]
>|||Jerry,
It worked and it was quick too...thanks again
Brian
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u155VK20FHA.3000@.TK2MSFTNGP12.phx.gbl...
> Brian,
> You could use functions to determine the lastname and sort by that but
> that technique would probably preclude the use of NC indexes to resolve
> the query and may negatively impact performance. Something like (or some
> variation of):
> SELECT <COLUMN LIST.
> FROM <TABLE>
> ORDER BY SUBSTRING(<COLUMN>, CHARINDEX(' ',<COLUMN> )+ 1,
> DATALENGTH(<COLUMN> ) - CHARINDEX(' ',<COLUMN> )) DESC
> HTH
> Jerry
>
> "Brian Staff" <brianstaff AT [NoSpam]cox DOT net> wrote in message
> news:VA.000002fd.102e316a@.bstaffw2k.jda.corp.local...
>sql

Order by query

I have a Comment Table where a comment can have a reply, if the comment is replied to I want the reply to appear under the comment.

Based on the Fields CommentID and Parent ID the parentID is the Comment and the Comment with a ParentID set too that comment is the answer.

How do I build this Query?ORDER BY COALESCE(ParentID,CommentID),CommentID|||Cool it worked took a while, had too set the ParentId to Null

Select * From Comment
ORDER BY COALESCE(iParentID,iCommentID),iCommentID

Thanks alot|||Were they from blanks to Nulls?
If so you could use

ORDER
BY Coalesce(NullIf(iParentID,''),iCommentID),iComment ID)|||eeewwww!!

under which circumstances do you expect iParentID to be equal to an empty string??

i would put the odds of iParentID being a CHAR or VARCHAR at 0.0000001%, i.e. squadoosh (http://en.wikipedia.org/wiki/Pardon_the_Interruption)

:cool:|||Your right is an Integer, I was inserting a Zero into the DB probably shouldn't have been.|||yes, zero is definitely wrong, and is a sign that you were probably not defining the foreign key

a foreign key of 0 would require a primary key of 0 to exist, and while you can theoretically define a pk of 0 (using SET IDENTITY_INSERT), this would mean you'd have a "dummy" row with a pk of 0 to act as the parent of all the real rows that have no parent

now let me pose the question: if you do go to the trouble of defining a "dummy" row with a pk of 0, what's the parentID value for that row?

:cool:|||Your completely right No value is NULL, 0 is a value with No parent

Order by problem

Hi there...

If I have a table with two differnet DateTime variables, and I want to order the hits in date order. The problem is that I want to order them depending of wich of theese two columns are the highest(eg. latest date)

ex:
id | date_1 | date_2
------
1 | 1991 | 2001
2 | 2002 | 1991
3 | 1993 | 1992

should result in(highest first):
id
--
2
1
3

How would I do this?You Could Try

Select Date_1 as UDate FROM table
UNION
Select Date_2 as UDate FROM table
ORDER By UDate DESC|||ORDER BY case when date1 > date2 then date1 else date2 end|||ORDER BY case when date1 > date2 then date1 else date2 end

ORDER BY case when d1 > d2 then d1 else d2 end DESC

Order by problem

Hello,

I have a table with a field called 'time' which is type char.

The data which goes into this field is 1am, 2am, 3am, 4am ,5am, 6am, 7am, 8am ,9am, 10am, 11am, 12noon, 1pm, 2pm, 3pm, 4pm,5pm, 6pm, 7pm, 8pm, 9pm, 10pm, 11pm, 12mid.

As users enter the data the order can go out of sequence, so I want to be able to sort the order to go as above for each day.

Is there a way of doing custom ordering by number, then letter of data in the same field.
or any other suggestions are welcome.

Thanks
GoongSuggestion: Store military time (00:00-23:00) and they will sort fine. Then, create a method in the UI that will display that value in a reasonable format.

Order by problem

Hi there...

If I have a table with two differnet DateTime variables, and I want to order the hits in date order. The problem is that I want to order them depending of wich of theese two columns are the highest(eg. latest date)

ex:
id | date_1 | date_2
------
1 | 1991 | 2001
2 | 2002 | 1991
3 | 1993 | 1992

should result in(highest first):
id
--
2
1
3

How would I do this?If your DBMS has a function GREATEST (or similar) then:

ORDER BY GREATEST (date_1, date_2)

If not, you can do it using CASE:

ORDER BY CASE WHEN date_1 > date_2 THEN date_1 ELSE date_2 END|||tony, you forgot the DESC

http://www.dbforums.com/t999138.html

zcumbag, please do not cross-post|||tony, you forgot the DESC
Thanks, Rudy!|||thanks... works fine...

sorry about the cross-posting. I missread thestartpage... wont happen again... thanks|||OK now I deicovered another problem... I need to SELECT DISTINCT and because of that I can't have the CASE-statement in the ORDER BY-statement...

I need to find another solution...

is it posible to have the datestatement in the SELECT-line?
something like this:

SELECT DISTINCT TOP 10 ArticleId, Heading, WICHEVERISBIGGEST(date1, date2) AS date3 FROM tblARticles
...
ORDER BY date3

I haven't seem to find any WICHEVERISBIGGEST-kindof function...

anyone?|||Well, if your DBMS doesn't have a "WHICHEVERISBIGGEST" function (in Oracle it is called GREATEST), then again CASE will do it:

SELECT DISTINCT TOP 10 ArticleId, Heading, CASE WHEN date1 > date2 THEN date1 ELSE date2 END AS date3|||I need to SELECT DISTINCT and because of that I can't have the CASE-statement in the ORDER BY-statement...

can you show us the query? i don't see why DISTINCT means that you can't put a CASE expression into the ORDER BY

you can also say ORDER BY 4 (where 4 is the 4th column) if that helps|||can you show us the query? i don't see why DISTINCT means that you can't put a CASE expression into the ORDER BY

The reason is Error 145 wich says Order By items must appear in the select list if Select Distinct is specified.

But it works if I put the case statement in the select line.

thankssql

Order By Primary Key

I've got a table with an a primary key of type 'int' (auto incrementing).
Were talking about millions of rows.
The name of this field is TempID.
I always want to sort by the TempID ascending...is that something that I
even need to specify?
When I query it in Query Analyzer it seems to always be sorted by the
primary key ascending.
Although this is what I want, will this ALWAYS be the case, or should I just
throw an order by in there for good measure?
Stored Proc:
--
@.numberOfRows int,
@.startingID int
set rowcount @.numberOfRows
Select * From tblList Where TempID > @.startingID
set rowcount 0
--
Will this always work how I expect or should I add the "Order By TempID"?
I am asking because I don't know if my Order By clause will slow it down.
I'm sure I wouldn't notice if it was .0000000002 ms slower, but this would
be good to know, especially if @.startingID = 4,000,000 or the @.numberOfRows
is very large.
I hope that all made sense.Read the BOL for more information under "Clustered Indexes":
"An index on the columns specified in the ORDER BY or GROUP BY clause
may remove the need for the Database Engine to sort the data, because
the rows are already sorted. This improves query performance."
Didi you create them as Clustered keys ?
HTH, Jens Suessmeyer.|||INeedADip wrote:
> I've got a table with an a primary key of type 'int' (auto
> incrementing). Were talking about millions of rows.
> The name of this field is TempID.
> I always want to sort by the TempID ascending...is that something
> that I even need to specify?
> When I query it in Query Analyzer it seems to always be sorted by the
> primary key ascending.
> Although this is what I want, will this ALWAYS be the case, or should
> I just throw an order by in there for good measure?
> Stored Proc:
> --
> @.numberOfRows int,
> @.startingID int
> set rowcount @.numberOfRows
> Select * From tblList Where TempID > @.startingID
> set rowcount 0
> --
> Will this always work how I expect or should I add the "Order By
> TempID"? I am asking because I don't know if my Order By clause will
> slow it
> down. I'm sure I wouldn't notice if it was .0000000002 ms slower, but
> this would be good to know, especially if @.startingID = 4,000,000 or
> the @.numberOfRows is very large.
> I hope that all made sense.
The only way to guarantee the order of results is with an ORDER BY
clause. If that column is your PK, you should already have a unique
index on the column. If it's clustered, it will help with sorting. Keep
in mind that sort operations are very costly. While the unique,
clustered index on the PK column might eliminate the need for the sort,
it is no guarantee. You still need the ORDER BY clause.
David Gugick
Quest Software
www.quest.com|||You have a ton of basic mistakes and need to get an intro book on SQL.
Columns are not fields. An auto-increment can never be a relational
key by definition because it is not an attribute of the entities
modeled in the table. Tables by definition have no ordering.
You are confusing an implementation with SQL and proper data modeling.
If you want to be sure that you get your output in order, you need an
ORDER BY clause.
Years ago, SQL Server programmers got screwed up becuase they wrote
code that assumed a GROUP BY clause would be done with a sort under the
covers, so they did not write the ORDER clause. It blew up a ton of
programs on the next release.|||Wow...guess I hit a nerve.
I'm sorry, I should have picked my words a little more carefully.
I figured you would get the jist of it....
Why would auto-increment fields not be an attribute of the entities in the
table?
Isn't that one of the easiest ways to get a unique identifier for that set
of data, making it a key attribute?
"can never be a relational key"...I've never heard that argument.|||There are different schools of thought on using auto-increment fields as
keys. Personally, I tend to agree with Celko on this one. Auto-increment
fields have nothing to do with the data in the table, they are an artificial
way of creating uniqueness. A more meaningful key would suit my tastes.
Using meaningful keys allows you to
1. Reuse the column name and data type in all tables, which makes joins more
intuitive.
Job.deptid = department.deptid
is easier and more intuitive than
job.Department = department.id
2. Use a key value that makes some sort of sense when you look at it.
3. Avoid redundancy in your table and insure normalization.
Often when we have an auto-incremeneting key, there is another column or
columns that also uniquely identify the row.
However, there are many in the field (with as much experience as myself or
Celko) that consider auto-increment keys a requirement in their tables.
As for the terminology, do a search on celko and punch cards to see why he
makes such an issue of it. As long as everone knows what you mean by
"field" and "record" (and we do) I wouldn't lose any sleep over it.
"INeedADip" <INeedADip@.gmail.com> wrote in message
news:eF8AOUEIGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Wow...guess I hit a nerve.
> I'm sorry, I should have picked my words a little more carefully.
> I figured you would get the jist of it....
> Why would auto-increment fields not be an attribute of the entities in the
> table?
> Isn't that one of the easiest ways to get a unique identifier for that set
> of data, making it a key attribute?
> "can never be a relational key"...I've never heard that argument.
>|||So you guys are just refering to the name of the column?
I do have incrementing fields in 99% of my tables like AccountID, QueueID,
CampaignID, ect...
If that is what you guys are talking about, I agree....anyways...
Thanks for educating me on the Order By, I was under the impression that if
I used an index on the field, I could save some time by not using the Order
By. And just to note...I'm wasn't asking this question as a general rule,
but in this one case (this specific table) I was trying to save some time
because the table is so large and I will only be getting chunks in ascending
order EVERY TIME...but if it is not guaranteed, then I will add the "Order
By".
- Thanks|||Hi Jim,
I've got to take the bite and put the surrogate key side forward.
If a natural key exists on the table (that isn't in reality a generated
number in itself - for instance deptid would indicate its auto-generated
some how), most keys in the natural world are actually a surrogate of some
form or another.
There are problems duplicating the natural key everywhere, especially if its
a composite key; there is no difference in joining whether you use a
surrogate key or a natural one to join.
INeedADip - do a search on 'surrogate key' and 'identity' for some really
good and extensive discussions on the benefits of the surrogate key
approach.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uNTKWeEIGHA.376@.TK2MSFTNGP12.phx.gbl...
> There are different schools of thought on using auto-increment fields as
> keys. Personally, I tend to agree with Celko on this one. Auto-increment
> fields have nothing to do with the data in the table, they are an
> artificial
> way of creating uniqueness. A more meaningful key would suit my tastes.
> Using meaningful keys allows you to
> 1. Reuse the column name and data type in all tables, which makes joins
> more
> intuitive.
> Job.deptid = department.deptid
> is easier and more intuitive than
> job.Department = department.id
> 2. Use a key value that makes some sort of sense when you look at it.
> 3. Avoid redundancy in your table and insure normalization.
> Often when we have an auto-incremeneting key, there is another column
> or
> columns that also uniquely identify the row.
> However, there are many in the field (with as much experience as myself or
> Celko) that consider auto-increment keys a requirement in their tables.
> As for the terminology, do a search on celko and punch cards to see why he
> makes such an issue of it. As long as everone knows what you mean by
> "field" and "record" (and we do) I wouldn't lose any sleep over it.
>
> "INeedADip" <INeedADip@.gmail.com> wrote in message
> news:eF8AOUEIGHA.2704@.TK2MSFTNGP15.phx.gbl...
>|||Thanks Tony.
I believe there was a fairly in depth discussion about this last w,
although I purge my postings frequently and can't locate it. If I recall
correctly, you were one of the folks involved in the discussion. Would you
mind posting a link to it?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eyo318FIGHA.2912@.tk2msftngp13.phx.gbl...
> Hi Jim,
> I've got to take the bite and put the surrogate key side forward.
> If a natural key exists on the table (that isn't in reality a generated
> number in itself - for instance deptid would indicate its auto-generated
> some how), most keys in the natural world are actually a surrogate of some
> form or another.
> There are problems duplicating the natural key everywhere, especially if
its
> a composite key; there is no difference in joining whether you use a
> surrogate key or a natural one to join.
> INeedADip - do a search on 'surrogate key' and 'identity' for some really
> good and extensive discussions on the benefits of the surrogate key
> approach.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:uNTKWeEIGHA.376@.TK2MSFTNGP12.phx.gbl...
Auto-increment
or
he
>|||I think this is one of them...
6d2a8ccf940fe2" target="_blank">http://groups.google.co.uk/group/co...
6d2a8ccf940fe2
but there are many, search on 'surrogate key' rogerson --celko--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eIiy5BGIGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Thanks Tony.
> I believe there was a fairly in depth discussion about this last w,
> although I purge my postings frequently and can't locate it. If I recall
> correctly, you were one of the folks involved in the discussion. Would
> you
> mind posting a link to it?
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eyo318FIGHA.2912@.tk2msftngp13.phx.gbl...
> its
> Auto-increment
> or
> he
>

Order By Param

Is there a way to have a report parameter for the selection of what field
the user wants a table to be grouped on? For instance, the parameter would
have 3 options, clientid, clientname, and clientcity. I want to be able to
select one of these and have the table group on this selection.
Or any generic ways or ideas on how to accomplish this?
Thanks!!!I am looking for the same solution. I have a client that I have promised 10
reports to and they gave me a few but want sort capability on 5 different
fields. I could say that those are 5 different reports, but there should be
an easy way to list the fields in a drop down parameter and adjust the ORDER
BY based on this. Shouldn't there?
Thanks in advance.
ANDY
"Amon Borland" wrote:
> Is there a way to have a report parameter for the selection of what field
> the user wants a table to be grouped on? For instance, the parameter would
> have 3 options, clientid, clientname, and clientcity. I want to be able to
> select one of these and have the table group on this selection.
> Or any generic ways or ideas on how to accomplish this?
> Thanks!!!
>
>|||can you pass the field to sort on into the report as a parameter and use a
CASE in your query to conditionally sort based on whatever you passed in as
a parameter?
Bill
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:C80B4FC3-FA92-4ADE-BFEA-D6B0867FDE97@.microsoft.com...
> I am looking for the same solution. I have a client that I have promised
10
> reports to and they gave me a few but want sort capability on 5 different
> fields. I could say that those are 5 different reports, but there should
be
> an easy way to list the fields in a drop down parameter and adjust the
ORDER
> BY based on this. Shouldn't there?
> Thanks in advance.
> ANDY
> "Amon Borland" wrote:
> > Is there a way to have a report parameter for the selection of what
field
> > the user wants a table to be grouped on? For instance, the parameter
would
> > have 3 options, clientid, clientname, and clientcity. I want to be able
to
> > select one of these and have the table group on this selection.
> >
> > Or any generic ways or ideas on how to accomplish this?
> >
> > Thanks!!!
> >
> >
> >|||It seems all my replied are this..
Sorting is easy
DECLARE @.sql as varchar(100)
SET @.sql = 'SELECT * FROM blah WHERE blah = blah ORDER BY '
@.paramete
EXEC sp_executesql @.sq
Grouping on the other hand I don't think you can do dynamically

Order by of a table passed as parameters?

Is it possible to parametrise the 'order by' options of a table/group so
that a single report could be ordered by users on run time depending of
their needs?
The alternative is to write/copy the same report once and again and have it
repeated (one instance) for each orderable column.
Regards.Sorry for this post. I just found it on RS' BOL.
Regards.
"David Lightman Robles" <dlightman@.NOSPAMiname.com> escribió en el mensaje
news:eXv4RWQXFHA.3572@.TK2MSFTNGP12.phx.gbl...
> Is it possible to parametrise the 'order by' options of a table/group so
> that a single report could be ordered by users on run time depending of
> their needs?
> The alternative is to write/copy the same report once and again and have
> it repeated (one instance) for each orderable column.
> Regards.
>