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
--
Friday, March 30, 2012
Order of conditions in a query
I have a query with many (approximately, 30) conditions, such as:
select ....... from table1 join table2 on ( (table1.field1 = table2.field1 OR table1.filed1 IS NULL) AND (table1.field2 = table2.field2 OR table1.filed2 IS NULL) )
My question is:
In C++ or C#, when I write a condition like this, say, in an IF or WHILE, I know that I would be better off specifying the IS NULL (well, == null, to be precise) first, and use | instead of ||. In that case, the first condition (equality to null) is checked first, it's fast, and if it's not satisfied, the control flow goes to the next statement.
The question is, is there the same rule in T-SQL?
I mean, if I put the "... IS NULL" first, and then "OR ... = ...", will the query run faster than if I write it the other way around (that is, "... = ... OR ... IS NULL")?
This is very important to me, because most of those fields are VARCHAR, and due to some business rules, I can't change them to numerics etc, which would be compared much faster than text. So, even if I use full text search, I still need to find a way to optimize the query for performance...
By the way, I know that I can put those conditions in the WHERE clause, but as far as I know it won't make much of a difference for performance. So, my question is primarily about the order of conditions, in which SQL Server constructs its query plan.
[Edited:] In other words, what runs faster: comparing varchar to null or comparing varchars? And does it make a difference if I switch their places in my sql script?
We are using SQL Server 2000 SP4, Standard Edition. [Dev edition on the dev machine.]
Could someone kindly advise me on this, please?
Thank you ever so much.
The way you have that written, can't you do a right join and omit the IS NULL condition?|||The order the WHERE clause is processed is "undefined" and "not guaranteed". So don't try to write something that is based on process order.IS NULL is very fast compared to varchar equals. But the optimizer is going to "pick" how to best process the query and may not do it the same way every time.|||
The way you have that written, can't you do a right join and omit the IS NULL condition?
I'd love to, but it's an "either-or" from a business logic, and I'm not returning a set of rows, - this query is actually a part of the matching logic (a Notify function) for a Notification Services application. I'm sorry, I should have mentioned that.
Thank you.
sqlOrder of columns using drillthrough
I need to implement drillthrough options in a cube using MSAS 2000.
But required a given order of columns for showing these columns and I
don't know how can I set an order of columns.
It looks like I can only select and deselect column outputting but
there is no possibility to set an order.
Thanks for help,
AndriyOn Jul 9, 3:40 pm, andr...@.rambler.ru wrote:
> I need to implement drillthrough options in a cube using MSAS 2000.
> But required a given order of columns for showing these columns and I
> don't know how can I set an order of columns.
> It looks like I can only select and deselect column outputting but
> there is no possibility to set an order.
> Thanks for help,
> Andriy
The only way I know is to edit the MDX directly - IDE editor doesn't
allow to set the order.
Marco
But required a given order of columns for showing these columns and I
don't know how can I set an order of columns.
It looks like I can only select and deselect column outputting but
there is no possibility to set an order.
Thanks for help,
AndriyOn Jul 9, 3:40 pm, andr...@.rambler.ru wrote:
> I need to implement drillthrough options in a cube using MSAS 2000.
> But required a given order of columns for showing these columns and I
> don't know how can I set an order of columns.
> It looks like I can only select and deselect column outputting but
> there is no possibility to set an order.
> Thanks for help,
> Andriy
The only way I know is to edit the MDX directly - IDE editor doesn't
allow to set the order.
Marco
Order of columns using drillthrough
I need to implement drillthrough options in a cube using MSAS 2000.
But required a given order of columns for showing these columns and I
don't know how can I set an order of columns.
It looks like I can only select and deselect column outputting but
there is no possibility to set an order.
Thanks for help,
Andriy
On Jul 9, 3:40 pm, andr...@.rambler.ru wrote:
> I need to implement drillthrough options in a cube using MSAS 2000.
> But required a given order of columns for showing these columns and I
> don't know how can I set an order of columns.
> It looks like I can only select and deselect column outputting but
> there is no possibility to set an order.
> Thanks for help,
> Andriy
The only way I know is to edit the MDX directly - IDE editor doesn't
allow to set the order.
Marco
But required a given order of columns for showing these columns and I
don't know how can I set an order of columns.
It looks like I can only select and deselect column outputting but
there is no possibility to set an order.
Thanks for help,
Andriy
On Jul 9, 3:40 pm, andr...@.rambler.ru wrote:
> I need to implement drillthrough options in a cube using MSAS 2000.
> But required a given order of columns for showing these columns and I
> don't know how can I set an order of columns.
> It looks like I can only select and deselect column outputting but
> there is no possibility to set an order.
> Thanks for help,
> Andriy
The only way I know is to edit the MDX directly - IDE editor doesn't
allow to set the order.
Marco
order of columns in composite index
I'm know that for composite indexes, the recommendation is always to specify
the most selective column first, but can someone please explain why this
makes such a huge difference in perfomance' I have a table where column A
only has 1 unique value and column B is basically unique among all rows. A
query that has A and B in the WHERE clause takes ALOT longer if my composite
index was created with (A, B) instead of (B, A). Thanks for any help.
BobBy the way, I did verify that in both cases, my index is being used, since I
know that index statistics are gathered based on the first column.
"Bob Gabor" <rjg@.mindspring.com> wrote in message
news:Yclkf.8556$N45.2454@.newsread1.news.atl.earthlink.net...
> I'm know that for composite indexes, the recommendation is always to
> specify the most selective column first, but can someone please explain
> why this makes such a huge difference in perfomance' I have a table
> where column A only has 1 unique value and column B is basically unique
> among all rows. A query that has A and B in the WHERE clause takes ALOT
> longer if my composite index was created with (A, B) instead of (B, A).
> Thanks for any help.
> Bob
>|||It depends on the query. Rules like the one you quote are just
general guidelines. For example, if one of the columns is used
in a range or LIKE comparison, it may be best to index that column
first regardless of selectivity. Depending on the query and data,
the two-column statistics may be more or less accurate predictors
of row count for the index in one order than in the other, also.
If you look at the query plans in more detail, you may be able
to see whether the faster solution is resulting in a better plan that
the other ordering can't allow, or if the faster solution is a result
of better row count estimates.
Steve Kass
Drew University
Bob Gabor wrote:
>I'm know that for composite indexes, the recommendation is always to specif
y
>the most selective column first, but can someone please explain why this
>makes such a huge difference in perfomance' I have a table where column A
>only has 1 unique value and column B is basically unique among all rows. A
>query that has A and B in the WHERE clause takes ALOT longer if my composit
e
>index was created with (A, B) instead of (B, A). Thanks for any help.
>Bob
>
>|||>> is always to specify
the most selective column first, but can someone please explain why
this
makes such a huge difference in perfomance' <<
there is only one hard and fast rule in our trade:
there are no hard and fast rules in database programming.
;)
For instance, if you frequently join on some column, putting it first
frequently speeds up joins.|||It does depend on many things, but I can give you some insight into why this
might be a problem (though I can not say it is necessarily a problem for
your application).
If you have a 2-column index with a non-selective leading column and a very
selective secondary column, it can cause additional I/O when compared to a
query run over an index with the columns defined in the opposite order
(selective column first). If the query does a s
on the first column and
then later columns, this could be less efficient.
SELECT col1, col2 FROM Table WHERE col1=4 and col3 > 5;
I will point out that it can vary from database engine to database engine.
It can vary on the predicates being used. It can vary based on the mix of
queries and the hardware. In short, it really does depend. However, it is
generally good to index selective fields since the cost of searching and the
cost of maintaining these indexes in updates is less than non-selective
columns.
Another reason to potentially pick a more selective leading index column,
all other factors being equal, is that SQL Server builds histograms on the
leading column. If it is very unselective, this can make the process of
cardinality estimation more difficult for the optimizer. This could cause
errors that lead to less than optimal plans being picked in some cases.
I hope that this gives you some insights into the internals to understand
why it might matter.
Thanks,
Conor Cunningham
SQL Server Query Optimization Development Lead
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1133653242.800468.130470@.g47g2000cwa.googlegroups.com...
> the most selective column first, but can someone please explain why
> this
> makes such a huge difference in perfomance' <<
> there is only one hard and fast rule in our trade:
> there are no hard and fast rules in database programming.
> ;)
> For instance, if you frequently join on some column, putting it first
> frequently speeds up joins.
>
the most selective column first, but can someone please explain why this
makes such a huge difference in perfomance' I have a table where column A
only has 1 unique value and column B is basically unique among all rows. A
query that has A and B in the WHERE clause takes ALOT longer if my composite
index was created with (A, B) instead of (B, A). Thanks for any help.
BobBy the way, I did verify that in both cases, my index is being used, since I
know that index statistics are gathered based on the first column.
"Bob Gabor" <rjg@.mindspring.com> wrote in message
news:Yclkf.8556$N45.2454@.newsread1.news.atl.earthlink.net...
> I'm know that for composite indexes, the recommendation is always to
> specify the most selective column first, but can someone please explain
> why this makes such a huge difference in perfomance' I have a table
> where column A only has 1 unique value and column B is basically unique
> among all rows. A query that has A and B in the WHERE clause takes ALOT
> longer if my composite index was created with (A, B) instead of (B, A).
> Thanks for any help.
> Bob
>|||It depends on the query. Rules like the one you quote are just
general guidelines. For example, if one of the columns is used
in a range or LIKE comparison, it may be best to index that column
first regardless of selectivity. Depending on the query and data,
the two-column statistics may be more or less accurate predictors
of row count for the index in one order than in the other, also.
If you look at the query plans in more detail, you may be able
to see whether the faster solution is resulting in a better plan that
the other ordering can't allow, or if the faster solution is a result
of better row count estimates.
Steve Kass
Drew University
Bob Gabor wrote:
>I'm know that for composite indexes, the recommendation is always to specif
y
>the most selective column first, but can someone please explain why this
>makes such a huge difference in perfomance' I have a table where column A
>only has 1 unique value and column B is basically unique among all rows. A
>query that has A and B in the WHERE clause takes ALOT longer if my composit
e
>index was created with (A, B) instead of (B, A). Thanks for any help.
>Bob
>
>|||>> is always to specify
the most selective column first, but can someone please explain why
this
makes such a huge difference in perfomance' <<
there is only one hard and fast rule in our trade:
there are no hard and fast rules in database programming.
;)
For instance, if you frequently join on some column, putting it first
frequently speeds up joins.|||It does depend on many things, but I can give you some insight into why this
might be a problem (though I can not say it is necessarily a problem for
your application).
If you have a 2-column index with a non-selective leading column and a very
selective secondary column, it can cause additional I/O when compared to a
query run over an index with the columns defined in the opposite order
(selective column first). If the query does a s
on the first column andthen later columns, this could be less efficient.
SELECT col1, col2 FROM Table WHERE col1=4 and col3 > 5;
I will point out that it can vary from database engine to database engine.
It can vary on the predicates being used. It can vary based on the mix of
queries and the hardware. In short, it really does depend. However, it is
generally good to index selective fields since the cost of searching and the
cost of maintaining these indexes in updates is less than non-selective
columns.
Another reason to potentially pick a more selective leading index column,
all other factors being equal, is that SQL Server builds histograms on the
leading column. If it is very unselective, this can make the process of
cardinality estimation more difficult for the optimizer. This could cause
errors that lead to less than optimal plans being picked in some cases.
I hope that this gives you some insights into the internals to understand
why it might matter.
Thanks,
Conor Cunningham
SQL Server Query Optimization Development Lead
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1133653242.800468.130470@.g47g2000cwa.googlegroups.com...
> the most selective column first, but can someone please explain why
> this
> makes such a huge difference in perfomance' <<
> there is only one hard and fast rule in our trade:
> there are no hard and fast rules in database programming.
> ;)
> For instance, if you frequently join on some column, putting it first
> frequently speeds up joins.
>
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.sqlOrder Issue
have a character field that represents a date in the format YYYYMM.
This data is broken down in three month blocks,
12 Dec
11 Nov
10 Oct
by this year and the corresponding 3 month block from last year.
So the data will look like this
200512
200511
200510
200412
200411
200410
I have a report that requires this order, but my result set is already being
put into another order and I want to know if I can convert the current YYYYM
M
format to a valid date.
Can this be done and how?
Thank you~~I'm not sure I follow you. If you have:
ORDER BY thecolumn DESC
don't you get the desired order? Also, why don't you store these as a smalld
atetime column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anthony W DiGrigoli" <AnthonyWDiGrigoli@.discussions.microsoft.com> wrote in
message
news:6368A787-C7D5-4DBA-B0DE-4E7D2B6473D3@.microsoft.com...
> have a character field that represents a date in the format YYYYMM.
> This data is broken down in three month blocks,
> 12 Dec
> 11 Nov
> 10 Oct
> by this year and the corresponding 3 month block from last year.
> So the data will look like this
> 200512
> 200511
> 200510
> 200412
> 200411
> 200410
> I have a report that requires this order, but my result set is already bei
ng
> put into another order and I want to know if I can convert the current YYY
YMM
> format to a valid date.
> Can this be done and how?
> Thank you~~|||You can use multiple expressions in the "order by" clause.
Example:
select c1, ..., cn
from table1
order by
cast(left(c1, 4) as int) desc, -- here you order by year
cast(right(c1, 2) as int) desc -- here by month
AMB
"Anthony W DiGrigoli" wrote:
> have a character field that represents a date in the format YYYYMM.
> This data is broken down in three month blocks,
> 12 Dec
> 11 Nov
> 10 Oct
> by this year and the corresponding 3 month block from last year.
> So the data will look like this
> 200512
> 200511
> 200510
> 200412
> 200411
> 200410
> I have a report that requires this order, but my result set is already bei
ng
> put into another order and I want to know if I can convert the current YYY
YMM
> format to a valid date.
> Can this be done and how?
> Thank you~~
This data is broken down in three month blocks,
12 Dec
11 Nov
10 Oct
by this year and the corresponding 3 month block from last year.
So the data will look like this
200512
200511
200510
200412
200411
200410
I have a report that requires this order, but my result set is already being
put into another order and I want to know if I can convert the current YYYYM
M
format to a valid date.
Can this be done and how?
Thank you~~I'm not sure I follow you. If you have:
ORDER BY thecolumn DESC
don't you get the desired order? Also, why don't you store these as a smalld
atetime column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anthony W DiGrigoli" <AnthonyWDiGrigoli@.discussions.microsoft.com> wrote in
message
news:6368A787-C7D5-4DBA-B0DE-4E7D2B6473D3@.microsoft.com...
> have a character field that represents a date in the format YYYYMM.
> This data is broken down in three month blocks,
> 12 Dec
> 11 Nov
> 10 Oct
> by this year and the corresponding 3 month block from last year.
> So the data will look like this
> 200512
> 200511
> 200510
> 200412
> 200411
> 200410
> I have a report that requires this order, but my result set is already bei
ng
> put into another order and I want to know if I can convert the current YYY
YMM
> format to a valid date.
> Can this be done and how?
> Thank you~~|||You can use multiple expressions in the "order by" clause.
Example:
select c1, ..., cn
from table1
order by
cast(left(c1, 4) as int) desc, -- here you order by year
cast(right(c1, 2) as int) desc -- here by month
AMB
"Anthony W DiGrigoli" wrote:
> have a character field that represents a date in the format YYYYMM.
> This data is broken down in three month blocks,
> 12 Dec
> 11 Nov
> 10 Oct
> by this year and the corresponding 3 month block from last year.
> So the data will look like this
> 200512
> 200511
> 200510
> 200412
> 200411
> 200410
> I have a report that requires this order, but my result set is already bei
ng
> put into another order and I want to know if I can convert the current YYY
YMM
> format to a valid date.
> Can this be done and how?
> Thank you~~
Order in which datasets executed
Can one specify the prder in which datasets are executed in the report?
Thanks
Sanjeev
Order in which datasets executedI dont know why you want order of execution, because SSRS runs all the query
and takes it to a Intermediate format, which is ready for rendering in any
format. So all the substitution parameter happens before creating this format.
Amarnath
"Sanjeev Rampersad" wrote:
> Can one specify the prder in which datasets are executed in the report?
> Thanks
> Sanjeev
> Order in which datasets executed
>
>
Thanks
Sanjeev
Order in which datasets executedI dont know why you want order of execution, because SSRS runs all the query
and takes it to a Intermediate format, which is ready for rendering in any
format. So all the substitution parameter happens before creating this format.
Amarnath
"Sanjeev Rampersad" wrote:
> Can one specify the prder in which datasets are executed in the report?
> Thanks
> Sanjeev
> Order in which datasets executed
>
>
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!
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 in Parent Package Configurations
Hi all,
I am pretty new to SSIS and i found some, (to me) unexpected behaviour. Maybe you guys can help me out understanding it.
I am currently building a multi package ETL solution, that uses parent-package configurations to "distribute" variable values from the "root" package to the lower level and "leaf" packages. Each package contains some 8 parent-package variables.
When i ran the entire solution (i.e. the whole tree) I found that some packages aren't getting the correct values for some of their variables. It seems that if a parent-package configuration that wasn't configured correctly (misspelled parent variable name in this case) blocked the other parent-package configurations in the same package from evaluating. When i moved the correct parent-package configuration to execute prior to the incorrect configuration, it ran just ok.
So apperently parent-package configurations are evaluated one by one, and if one of them fails, the "later" configurations aren't evaluated any more.
Is this a feature? or a bug?
Why don't i get a warning in the error list? Should i maybe configure my BIDS in a different fashion?
Hope someone can help me out.
Cheers,
Tom Kronenburg
Tom,
I am not aware of that behaivor using parent-package variables. What I know though is that parent package based configurations are allways resolved in the last place no matter how 'high' they are in the package configuration wizard; which may yield unexpected results if any other configuration depends on a parent-package variable one.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=264502
|||Rafael,
Thanks, but that was not really what i had problems with. I have only parent-package variables, and the order in which they are presented in the "Package configuration organizer" does present some problems.
E.g. My first parent package configuration has a reference to a variable with a different datatype then expected. (i.e. the configuration CustNo expects an int, and gets a string)
The second parent package configuration is correct.
The third parent package configuration expects a variable with a different name (e.g. it expects to get a variable CustNam and the parent only provides CustName
The fourth is correct again.
When running this package, it will fail 1 and 3, evaluate 2 correctly and never evaluate (is that the correct term?) 4.
In the progress tab i will see warnings for the failure of 1 and 3, and a notice that 2 is evaluated correctly, but i will never see any mention of 4.
I guess it's a bug, but it just might be intended to work that way and somewhere the properties of my bids are not set correctly.
Tom
|||I just reproduced the issue you described. It looks like any parent package variable that comes after an invalid one (when the warning message is: Configuration from a parent variable "xxx" did not occur because there was no parent variable. Error Code: 0xC0010001) is just ignored.
I would suggest to open a bug in the SQL Server connect site http://connect.microsoft.com/SQLServer/Feedback ; if you do so, place a link here so others can validate and vote.
[Microsoft follow-up]
|||The bug is reported, vote through https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=276366
|||Tom,
I have unmarked this thread as answered so it can show up in the Microsoft follow up report.
BTW, thanks for openning the bug in the connect site, I have casted my vote
|||This is a bug and we are aware of it. The bug will be fixed in the next release.
If you need a hotfix for the problem, please contact CSS
|||Greetings,
This is a known issue; the fix for the bug has been implemented and should be available in an upcoming release of SQL Server.
-David
sqlOrder headers and lines
Hi,
I am trying to design a data mart that will server as a data source for an Analysis cube. I would like to know the best practice for handling order headers and order lines. Do I need 2 fact tables in the data mart, and does there need to be a foreign key relationship between them.
The header table will contain information like whether a delivery was made on time, which depot delivered the order, how many times the delivery was attempted, total distribution charge, whereas the line table will contain product related information with quantities ordered / delivered, and the cost per line
Thanks
Depends on the analysis that needs to be supported, both by the data mart and the cube. For example, the FactInternetSales and FactResellerSales tables in the sample Adventure Works DW database are at the line level. But from your description, it sounde like there are relevant measures at both the header and line item level, so 2 fact tables (with 2 mesure groups) may be preferable. The detail table would then have a foreign key for the parent header table.
In terms of dimensions, those which directly relate to the detail measure group can be configured with a "many-many" relation to the header measure group (using the detail measure group and header fact dimension as intermediates). And those dimensions which directly relate to the header measure group can be configured as "referenced" (via the header fact dimension) for the detail measure group.
This article argues for a single detail-level fact table, but the AS 2005 modelling features may mitigate some of these issues:
Managing Your Parents
Be mindful of reporting needs when designing parent and child fact tables
By Ralph Kimball
...Order Group in specified order
Hi all,
We have recently migrated from Crystal Reports to SSRS. We are converting
all our company reports from one format to another. Some of our reports have
groupings in a specified order. When you sort order in Crystal you can select
"Specified Order" and then pick the names of the groups and place them in a
specified order to appear on the report, as opposed to ascending alphabetical
order for example.
How can I specify "Specified Order" when creating a Reporting Services report?
Thanks
JHI have never used Crystal Reports. But I think I understand what you
are asking. I had a similar issue where I had to explicity specify the
order of matrix columns. Neither RS nor my SQL SELECT ORDER BY did the
trick because I could do only Ascending/Descending or ASC/DESC
respectively. I would like to believe RS has some way to control
explicit order, but I sure cannot find it.
I solved my problem by doing the following
1) returned a dummy INT column from my SELECT statement. Made sure rows
in the dataset returned appropriate value for this column based on the
column data.
2) Changed the Column group in my matrix to Sort Ascending on THIS INT
column instead of using a data field.
Kludgy perhaps, but at the moment I could care less.
HTH.
Best.
JH wrote:
> Hi all,
> We have recently migrated from Crystal Reports to SSRS. We are converting
> all our company reports from one format to another. Some of our reports have
> groupings in a specified order. When you sort order in Crystal you can select
> "Specified Order" and then pick the names of the groups and place them in a
> specified order to appear on the report, as opposed to ascending alphabetical
> order for example.
> How can I specify "Specified Order" when creating a Reporting Services report?
> Thanks
> JH
We have recently migrated from Crystal Reports to SSRS. We are converting
all our company reports from one format to another. Some of our reports have
groupings in a specified order. When you sort order in Crystal you can select
"Specified Order" and then pick the names of the groups and place them in a
specified order to appear on the report, as opposed to ascending alphabetical
order for example.
How can I specify "Specified Order" when creating a Reporting Services report?
Thanks
JHI have never used Crystal Reports. But I think I understand what you
are asking. I had a similar issue where I had to explicity specify the
order of matrix columns. Neither RS nor my SQL SELECT ORDER BY did the
trick because I could do only Ascending/Descending or ASC/DESC
respectively. I would like to believe RS has some way to control
explicit order, but I sure cannot find it.
I solved my problem by doing the following
1) returned a dummy INT column from my SELECT statement. Made sure rows
in the dataset returned appropriate value for this column based on the
column data.
2) Changed the Column group in my matrix to Sort Ascending on THIS INT
column instead of using a data field.
Kludgy perhaps, but at the moment I could care less.
HTH.
Best.
JH wrote:
> Hi all,
> We have recently migrated from Crystal Reports to SSRS. We are converting
> all our company reports from one format to another. Some of our reports have
> groupings in a specified order. When you sort order in Crystal you can select
> "Specified Order" and then pick the names of the groups and place them in a
> specified order to appear on the report, as opposed to ascending alphabetical
> order for example.
> How can I specify "Specified Order" when creating a Reporting Services report?
> Thanks
> JH
Order for Defragmenting
If you have a database and the disk it is on to defragment which order
should they be defragmented in?I'd do the disk first. SQL Server doesn't know whether the disk is fragmented or not, so the end
result would be the same. But if you defrag the disk first, then SQL Server's defragmentation might
be a bit quicker (since its database files now are ... defragmented).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:cc8d6907-20cd-44e9-8b5f-f717879a7787@.v67g2000hse.googlegroups.com...
> If you have a database and the disk it is on to defragment which order
> should they be defragmented in?|||I agree with Tibor's suggestion. I would also recommend shutting down SQL
Server prior to the disk defrag operation.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:B3CE7A63-E1FE-447E-AA2A-882EDCA0DEFB@.microsoft.com...
> I'd do the disk first. SQL Server doesn't know whether the disk is
> fragmented or not, so the end result would be the same. But if you defrag
> the disk first, then SQL Server's defragmentation might be a bit quicker
> (since its database files now are ... defragmented).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Robin9876" <robin9876@.hotmail.com> wrote in message
> news:cc8d6907-20cd-44e9-8b5f-f717879a7787@.v67g2000hse.googlegroups.com...
>> If you have a database and the disk it is on to defragment which order
>> should they be defragmented in?
>sql
should they be defragmented in?I'd do the disk first. SQL Server doesn't know whether the disk is fragmented or not, so the end
result would be the same. But if you defrag the disk first, then SQL Server's defragmentation might
be a bit quicker (since its database files now are ... defragmented).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:cc8d6907-20cd-44e9-8b5f-f717879a7787@.v67g2000hse.googlegroups.com...
> If you have a database and the disk it is on to defragment which order
> should they be defragmented in?|||I agree with Tibor's suggestion. I would also recommend shutting down SQL
Server prior to the disk defrag operation.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:B3CE7A63-E1FE-447E-AA2A-882EDCA0DEFB@.microsoft.com...
> I'd do the disk first. SQL Server doesn't know whether the disk is
> fragmented or not, so the end result would be the same. But if you defrag
> the disk first, then SQL Server's defragmentation might be a bit quicker
> (since its database files now are ... defragmented).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Robin9876" <robin9876@.hotmail.com> wrote in message
> news:cc8d6907-20cd-44e9-8b5f-f717879a7787@.v67g2000hse.googlegroups.com...
>> If you have a database and the disk it is on to defragment which order
>> should they be defragmented in?
>sql
Order for conditions to be processed
what is order in which conditions are processed for sql query i.e for
select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)Originally posted by kiranghag
what is order in which conditions are processed for sql query i.e for
select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)
If all the conditions are AND'ed then the order will be left to right, if the first condition is false the entire thing will be false so the remaining conditions would not be processed.
And if the conditions are OR'ed then if the first condition is true then all the conditions are true.
Also adding parenthesis decides which conditions are processesed when.
Regards,
Harshal.|||Originally posted by kiranghag
what is order in which conditions are processed for sql query i.e for
select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)
sql has an internal parser which reorders the conditions depending on clustered/indexes and such (keys for example). Conditions that refer to the c/indexes are processed prior to the non-c/indexes.
Besides that, the order in which the other conditions are processed might get reversed, which ever suit sql best.|||Kaiowas, I think you're talking about the order in which the records are retrieved in the case of SELECT, or UPDATEed/DELETEed respectively.
harshal's statement pretty much summarizes what the optimizer does and how to control it.
select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)Originally posted by kiranghag
what is order in which conditions are processed for sql query i.e for
select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)
If all the conditions are AND'ed then the order will be left to right, if the first condition is false the entire thing will be false so the remaining conditions would not be processed.
And if the conditions are OR'ed then if the first condition is true then all the conditions are true.
Also adding parenthesis decides which conditions are processesed when.
Regards,
Harshal.|||Originally posted by kiranghag
what is order in which conditions are processed for sql query i.e for
select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)
sql has an internal parser which reorders the conditions depending on clustered/indexes and such (keys for example). Conditions that refer to the c/indexes are processed prior to the non-c/indexes.
Besides that, the order in which the other conditions are processed might get reversed, which ever suit sql best.|||Kaiowas, I think you're talking about the order in which the records are retrieved in the case of SELECT, or UPDATEed/DELETEed respectively.
harshal's statement pretty much summarizes what the optimizer does and how to control it.
Order Entry System
An online shopping store with a lot of products... Unfortunately the number
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. ThanksTwo things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning
and such, is to either remove the item from inventory when it is placed in
the cart and set it to "promised" - not fully considering it sold until the
entire purchase is complete
- credit card authorized, etc. This runs the risk of depleting your inventor
y by the number of items that are sitting in abandoned carts on your site -
at least until those carts expire by whatever criteria you use for that.
The other is that the availability shown on the site is just informational t
o the customer and you don't attempt to keep that absolutely sync'd with you
r real unpromised inventory. I've seen sites where they do a final availabil
ity check at purchase time
(which is often on a separate server from the browsing server) and tell you
then that the item is no longer available. I think that is a pretty bad cust
omer experience, but you will need to strike the balance with your other bus
iness needs.
There are other techniques for dealing with available to promise, but they t
end to be more loosely coupled and that doesn't fit well when your inventory
per item is very limited.|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> number
scale[vbcol=seagreen]
not[vbcol=seagreen]
show[vbcol=seagreen]
order[vbcol=seagreen]
> out
> Any
>
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. ThanksTwo things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning
and such, is to either remove the item from inventory when it is placed in
the cart and set it to "promised" - not fully considering it sold until the
entire purchase is complete
- credit card authorized, etc. This runs the risk of depleting your inventor
y by the number of items that are sitting in abandoned carts on your site -
at least until those carts expire by whatever criteria you use for that.
The other is that the availability shown on the site is just informational t
o the customer and you don't attempt to keep that absolutely sync'd with you
r real unpromised inventory. I've seen sites where they do a final availabil
ity check at purchase time
(which is often on a separate server from the browsing server) and tell you
then that the item is no longer available. I think that is a pretty bad cust
omer experience, but you will need to strike the balance with your other bus
iness needs.
There are other techniques for dealing with available to promise, but they t
end to be more loosely coupled and that doesn't fit well when your inventory
per item is very limited.|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> number
scale[vbcol=seagreen]
not[vbcol=seagreen]
show[vbcol=seagreen]
order[vbcol=seagreen]
> out
> Any
>
Order Entry System
An online shopping store with a lot of products... Unfortunately the number
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
Two things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>
|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning and such, is to either remove the item from inventory when it is placed in the cart and set it to "promised" - not fully considering it sold until the entire purchase is complete
- credit card authorized, etc. This runs the risk of depleting your inventory by the number of items that are sitting in abandoned carts on your site - at least until those carts expire by whatever criteria you use for that.
The other is that the availability shown on the site is just informational to the customer and you don't attempt to keep that absolutely sync'd with your real unpromised inventory. I've seen sites where they do a final availability check at purchase time
(which is often on a separate server from the browsing server) and tell you then that the item is no longer available. I think that is a pretty bad customer experience, but you will need to strike the balance with your other business needs.
There are other techniques for dealing with available to promise, but they tend to be more loosely coupled and that doesn't fit well when your inventory per item is very limited.
|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.[vbcol=seagreen]
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> number
scale[vbcol=seagreen]
not[vbcol=seagreen]
show[vbcol=seagreen]
order
> out
> Any
>
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
Two things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>
|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning and such, is to either remove the item from inventory when it is placed in the cart and set it to "promised" - not fully considering it sold until the entire purchase is complete
- credit card authorized, etc. This runs the risk of depleting your inventory by the number of items that are sitting in abandoned carts on your site - at least until those carts expire by whatever criteria you use for that.
The other is that the availability shown on the site is just informational to the customer and you don't attempt to keep that absolutely sync'd with your real unpromised inventory. I've seen sites where they do a final availability check at purchase time
(which is often on a separate server from the browsing server) and tell you then that the item is no longer available. I think that is a pretty bad customer experience, but you will need to strike the balance with your other business needs.
There are other techniques for dealing with available to promise, but they tend to be more loosely coupled and that doesn't fit well when your inventory per item is very limited.
|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.[vbcol=seagreen]
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> number
scale[vbcol=seagreen]
not[vbcol=seagreen]
show[vbcol=seagreen]
order
> out
> Any
>
Order Entry System
An online shopping store with a lot of products... Unfortunately the number
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. ThanksTwo things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning and such, is to either remove the item from inventory when it is placed in the cart and set it to "promised" - not fully considering it sold until the entire purchase is complete - credit card authorized, etc. This runs the risk of depleting your inventory by the number of items that are sitting in abandoned carts on your site - at least until those carts expire by whatever criteria you use for that
The other is that the availability shown on the site is just informational to the customer and you don't attempt to keep that absolutely sync'd with your real unpromised inventory. I've seen sites where they do a final availability check at purchase time (which is often on a separate server from the browsing server) and tell you then that the item is no longer available. I think that is a pretty bad customer experience, but you will need to strike the balance with your other business needs
There are other techniques for dealing with available to promise, but they tend to be more loosely coupled and that doesn't fit well when your inventory per item is very limited.|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> > An online shopping store with a lot of products... Unfortunately the
> number
> > of products to be sold are limited.. kind of in an inventory...
> >
> > I could have millions of customers logging on someday ... How can I
scale
> > this for concurrency and also ensure that data is consistent and i do
not
> > oversell the products that are not in the inventory..thats assuming i
show
> > all the customers all the products available and everyone decides to
order
> > it which i would not be able to control. I can imagine a product catalog
> > going thru some sort of a replication architecture for concurrency/scale
> out
> > purposes as data is static. How does one do it for an inventory system..
> Any
> > suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
> >
> >
>
of products to be sold are limited.. kind of in an inventory...
I could have millions of customers logging on someday ... How can I scale
this for concurrency and also ensure that data is consistent and i do not
oversell the products that are not in the inventory..thats assuming i show
all the customers all the products available and everyone decides to order
it which i would not be able to control. I can imagine a product catalog
going thru some sort of a replication architecture for concurrency/scale out
purposes as data is static. How does one do it for an inventory system.. Any
suggestions/articles are highly appreciated. Using SQL 2000.. ThanksTwo things: good database design and plenty of good hardware. Assuming you
want to sell the product on a first-ordered first-served basis your product
table should have a InventoryCount column. This could be displayed to let
each customer know how many there are at the time they first view the item.
When they actually purchase the item the count should be checked again and
if the count is greater than or equal to the qty ordered, it should be
reduced by the number ordered and the order should be sent to shipping as
part of the same transaction. The key is to make each transaction very
efficient and have enough hardware resources to deal with the demand.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> An online shopping store with a lot of products... Unfortunately the
number
> of products to be sold are limited.. kind of in an inventory...
> I could have millions of customers logging on someday ... How can I scale
> this for concurrency and also ensure that data is consistent and i do not
> oversell the products that are not in the inventory..thats assuming i show
> all the customers all the products available and everyone decides to order
> it which i would not be able to control. I can imagine a product catalog
> going thru some sort of a replication architecture for concurrency/scale
out
> purposes as data is static. How does one do it for an inventory system..
Any
> suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
>|||This is kind of a classic "available to promise" scenario.
One approach to solving these kinds of problems, beyond basic transactioning and such, is to either remove the item from inventory when it is placed in the cart and set it to "promised" - not fully considering it sold until the entire purchase is complete - credit card authorized, etc. This runs the risk of depleting your inventory by the number of items that are sitting in abandoned carts on your site - at least until those carts expire by whatever criteria you use for that
The other is that the availability shown on the site is just informational to the customer and you don't attempt to keep that absolutely sync'd with your real unpromised inventory. I've seen sites where they do a final availability check at purchase time (which is often on a separate server from the browsing server) and tell you then that the item is no longer available. I think that is a pretty bad customer experience, but you will need to strike the balance with your other business needs
There are other techniques for dealing with available to promise, but they tend to be more loosely coupled and that doesn't fit well when your inventory per item is very limited.|||So say we have the hardware, what technology do we implement to deal with
the demand ? I guess when it checks the count again and if its less then the
qty ordered, then it needs to send a message to the client right ?
How do you make each transaction very efficient ? Say if i have 2 customers
that want to buy ItemA that has 4 counts in its inventory . Both these
customers see 4 and want to buy all 4 and place an order. How does this get
handled ?
Also, are you saying that we need to have just one server that hosts this
inventory table to all the million customers that might be hitting it. ?
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23W6LH5VLEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Two things: good database design and plenty of good hardware. Assuming
you
> want to sell the product on a first-ordered first-served basis your
product
> table should have a InventoryCount column. This could be displayed to let
> each customer know how many there are at the time they first view the
item.
> When they actually purchase the item the count should be checked again and
> if the count is greater than or equal to the qty ordered, it should be
> reduced by the number ordered and the order should be sent to shipping as
> part of the same transaction. The key is to make each transaction very
> efficient and have enough hardware resources to deal with the demand.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e4G56$ULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> > An online shopping store with a lot of products... Unfortunately the
> number
> > of products to be sold are limited.. kind of in an inventory...
> >
> > I could have millions of customers logging on someday ... How can I
scale
> > this for concurrency and also ensure that data is consistent and i do
not
> > oversell the products that are not in the inventory..thats assuming i
show
> > all the customers all the products available and everyone decides to
order
> > it which i would not be able to control. I can imagine a product catalog
> > going thru some sort of a replication architecture for concurrency/scale
> out
> > purposes as data is static. How does one do it for an inventory system..
> Any
> > suggestions/articles are highly appreciated. Using SQL 2000.. Thanks
> >
> >
>
Order Date prompt descending doesn't work
Hi,
In the report model I'm defining attribute date as ValueSelection: dropdown and SortDirection: Descending.
In the report builder I define the field as a prompt.
When I execute the report the values in the prompt date are order ascending and not descending.
This occur all over my model, can't order date field as ascending.
Any idea?
Thanks,
Assaf
In the report builder: There is a button "Sort and Group". Did you set the "Sort by" property of the date? I think that has to be set in order to sort right.|||Hi,
Thanks for your reply.
The "Sort by" sort the records on the report.
My problem is that the sort in the prompt itself is always stay ascesnding although in the .NET I set it to descending
Any Idea?
Assaf
sqlorder data
Hi all,
someone could help me?
I need to order some records by data; the problem is the definition of
that data field: it' is't a datetime format, but nvarchar.
Thanks a lot
wa
The obvious question is why it isn't datetime...
That aside, you can convert the data in your ORDER BY clause to datetime (or a character
representation which sorts correctly), but we need to see what format you have for your date values
first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141633726.439178.133970@.i40g2000cwc.googlegr oups.com...
> Hi all,
> someone could help me?
> I need to order some records by data; the problem is the definition of
> that data field: it' is't a datetime format, but nvarchar.
> Thanks a lot
> wa
>
|||the format I have is nvarchar:
24/02/06 18.09.14
Could you post me an example pls?
Thank you in advance
wa
|||Hi
CREATE TABLE #Test
(
dt VARCHAR(20)
)
INSERT INTO #Test VALUES ('24/02/06 18.09.14')
INSERT INTO #Test VALUES ('22/02/06 17.15.14')
INSERT INTO #Test VALUES ('21/02/06 22.10.14')
INSERT INTO #Test VALUES ('28/02/06 04.09.14')
INSERT INTO #Test VALUES ('05/02/06 04.09.14')
SELECT * FROM #Test ORDER BY dt
--See the output
SELECT CAST('20'+SUBSTRING(dt,5,2)+
SUBSTRING(dt,3,2)+SUBSTRING(dt,1,2)+' '+SUBSTRING(dt,8,8)AS DATETIME) AS
newdt FROM
(
SELECT REPLACE(REPLACE(dt,'.',':'),'/','') AS dt FROM #Test
) as Der ORDER BY newdt
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141634987.423331.180520@.e56g2000cwe.googlegr oups.com...
> the format I have is nvarchar:
> 24/02/06 18.09.14
> Could you post me an example pls?
> Thank you in advance
> wa
>
|||Thanks a lots!
I'm new on sql language.
someone could help me?
I need to order some records by data; the problem is the definition of
that data field: it' is't a datetime format, but nvarchar.
Thanks a lot
wa
The obvious question is why it isn't datetime...
That aside, you can convert the data in your ORDER BY clause to datetime (or a character
representation which sorts correctly), but we need to see what format you have for your date values
first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141633726.439178.133970@.i40g2000cwc.googlegr oups.com...
> Hi all,
> someone could help me?
> I need to order some records by data; the problem is the definition of
> that data field: it' is't a datetime format, but nvarchar.
> Thanks a lot
> wa
>
|||the format I have is nvarchar:
24/02/06 18.09.14
Could you post me an example pls?
Thank you in advance
wa
|||Hi
CREATE TABLE #Test
(
dt VARCHAR(20)
)
INSERT INTO #Test VALUES ('24/02/06 18.09.14')
INSERT INTO #Test VALUES ('22/02/06 17.15.14')
INSERT INTO #Test VALUES ('21/02/06 22.10.14')
INSERT INTO #Test VALUES ('28/02/06 04.09.14')
INSERT INTO #Test VALUES ('05/02/06 04.09.14')
SELECT * FROM #Test ORDER BY dt
--See the output
SELECT CAST('20'+SUBSTRING(dt,5,2)+
SUBSTRING(dt,3,2)+SUBSTRING(dt,1,2)+' '+SUBSTRING(dt,8,8)AS DATETIME) AS
newdt FROM
(
SELECT REPLACE(REPLACE(dt,'.',':'),'/','') AS dt FROM #Test
) as Der ORDER BY newdt
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141634987.423331.180520@.e56g2000cwe.googlegr oups.com...
> the format I have is nvarchar:
> 24/02/06 18.09.14
> Could you post me an example pls?
> Thank you in advance
> wa
>
|||Thanks a lots!
I'm new on sql language.
order data
Hi all,
someone could help me?
I need to order some records by data; the problem is the definition of
that data field: it' is't a datetime format, but nvarchar.
Thanks a lot
waThe obvious question is why it isn't datetime...
That aside, you can convert the data in your ORDER BY clause to datetime (or a character
representation which sorts correctly), but we need to see what format you have for your date values
first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141633726.439178.133970@.i40g2000cwc.googlegroups.com...
> Hi all,
> someone could help me?
> I need to order some records by data; the problem is the definition of
> that data field: it' is't a datetime format, but nvarchar.
> Thanks a lot
> wa
>|||the format I have is nvarchar:
24/02/06 18.09.14
Could you post me an example pls?
Thank you in advance
wa|||Hi
CREATE TABLE #Test
(
dt VARCHAR(20)
)
INSERT INTO #Test VALUES ('24/02/06 18.09.14')
INSERT INTO #Test VALUES ('22/02/06 17.15.14')
INSERT INTO #Test VALUES ('21/02/06 22.10.14')
INSERT INTO #Test VALUES ('28/02/06 04.09.14')
INSERT INTO #Test VALUES ('05/02/06 04.09.14')
SELECT * FROM #Test ORDER BY dt
--See the output
SELECT CAST('20'+SUBSTRING(dt,5,2)+
SUBSTRING(dt,3,2)+SUBSTRING(dt,1,2)+' '+SUBSTRING(dt,8,8)AS DATETIME) AS
newdt FROM
(
SELECT REPLACE(REPLACE(dt,'.',':'),'/','') AS dt FROM #Test
) as Der ORDER BY newdt
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141634987.423331.180520@.e56g2000cwe.googlegroups.com...
> the format I have is nvarchar:
> 24/02/06 18.09.14
> Could you post me an example pls?
> Thank you in advance
> wa
>|||Thanks a lots!
I'm new on sql language.
someone could help me?
I need to order some records by data; the problem is the definition of
that data field: it' is't a datetime format, but nvarchar.
Thanks a lot
waThe obvious question is why it isn't datetime...
That aside, you can convert the data in your ORDER BY clause to datetime (or a character
representation which sorts correctly), but we need to see what format you have for your date values
first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141633726.439178.133970@.i40g2000cwc.googlegroups.com...
> Hi all,
> someone could help me?
> I need to order some records by data; the problem is the definition of
> that data field: it' is't a datetime format, but nvarchar.
> Thanks a lot
> wa
>|||the format I have is nvarchar:
24/02/06 18.09.14
Could you post me an example pls?
Thank you in advance
wa|||Hi
CREATE TABLE #Test
(
dt VARCHAR(20)
)
INSERT INTO #Test VALUES ('24/02/06 18.09.14')
INSERT INTO #Test VALUES ('22/02/06 17.15.14')
INSERT INTO #Test VALUES ('21/02/06 22.10.14')
INSERT INTO #Test VALUES ('28/02/06 04.09.14')
INSERT INTO #Test VALUES ('05/02/06 04.09.14')
SELECT * FROM #Test ORDER BY dt
--See the output
SELECT CAST('20'+SUBSTRING(dt,5,2)+
SUBSTRING(dt,3,2)+SUBSTRING(dt,1,2)+' '+SUBSTRING(dt,8,8)AS DATETIME) AS
newdt FROM
(
SELECT REPLACE(REPLACE(dt,'.',':'),'/','') AS dt FROM #Test
) as Der ORDER BY newdt
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141634987.423331.180520@.e56g2000cwe.googlegroups.com...
> the format I have is nvarchar:
> 24/02/06 18.09.14
> Could you post me an example pls?
> Thank you in advance
> wa
>|||Thanks a lots!
I'm new on sql language.
order data
Hi all,
someone could help me?
I need to order some records by data; the problem is the definition of
that data field: it' is't a datetime format, but nvarchar.
Thanks a lot
waThe obvious question is why it isn't datetime...
That aside, you can convert the data in your ORDER BY clause to datetime (or
a character
representation which sorts correctly), but we need to see what format you ha
ve for your date values
first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141633726.439178.133970@.i40g2000cwc.googlegroups.com...
> Hi all,
> someone could help me?
> I need to order some records by data; the problem is the definition of
> that data field: it' is't a datetime format, but nvarchar.
> Thanks a lot
> wa
>|||the format I have is nvarchar:
24/02/06 18.09.14
Could you post me an example pls?
Thank you in advance
wa|||Hi
CREATE TABLE #Test
(
dt VARCHAR(20)
)
INSERT INTO #Test VALUES ('24/02/06 18.09.14')
INSERT INTO #Test VALUES ('22/02/06 17.15.14')
INSERT INTO #Test VALUES ('21/02/06 22.10.14')
INSERT INTO #Test VALUES ('28/02/06 04.09.14')
INSERT INTO #Test VALUES ('05/02/06 04.09.14')
SELECT * FROM #Test ORDER BY dt
--See the output
SELECT CAST('20'+SUBSTRING(dt,5,2)+
SUBSTRING(dt,3,2)+SUBSTRING(dt,1,2)+' '+SUBSTRING(dt,8,8)AS DATETIME) AS
newdt FROM
(
SELECT REPLACE(REPLACE(dt,'.',':'),'/','') AS dt FROM #Test
) as Der ORDER BY newdt
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141634987.423331.180520@.e56g2000cwe.googlegroups.com...
> the format I have is nvarchar:
> 24/02/06 18.09.14
> Could you post me an example pls?
> Thank you in advance
> wa
>|||Thanks a lots!
I'm new on sql language.
someone could help me?
I need to order some records by data; the problem is the definition of
that data field: it' is't a datetime format, but nvarchar.
Thanks a lot
waThe obvious question is why it isn't datetime...
That aside, you can convert the data in your ORDER BY clause to datetime (or
a character
representation which sorts correctly), but we need to see what format you ha
ve for your date values
first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141633726.439178.133970@.i40g2000cwc.googlegroups.com...
> Hi all,
> someone could help me?
> I need to order some records by data; the problem is the definition of
> that data field: it' is't a datetime format, but nvarchar.
> Thanks a lot
> wa
>|||the format I have is nvarchar:
24/02/06 18.09.14
Could you post me an example pls?
Thank you in advance
wa|||Hi
CREATE TABLE #Test
(
dt VARCHAR(20)
)
INSERT INTO #Test VALUES ('24/02/06 18.09.14')
INSERT INTO #Test VALUES ('22/02/06 17.15.14')
INSERT INTO #Test VALUES ('21/02/06 22.10.14')
INSERT INTO #Test VALUES ('28/02/06 04.09.14')
INSERT INTO #Test VALUES ('05/02/06 04.09.14')
SELECT * FROM #Test ORDER BY dt
--See the output
SELECT CAST('20'+SUBSTRING(dt,5,2)+
SUBSTRING(dt,3,2)+SUBSTRING(dt,1,2)+' '+SUBSTRING(dt,8,8)AS DATETIME) AS
newdt FROM
(
SELECT REPLACE(REPLACE(dt,'.',':'),'/','') AS dt FROM #Test
) as Der ORDER BY newdt
"Wasco" <wasco77@.virgilio.it> wrote in message
news:1141634987.423331.180520@.e56g2000cwe.googlegroups.com...
> the format I have is nvarchar:
> 24/02/06 18.09.14
> Could you post me an example pls?
> Thank you in advance
> wa
>|||Thanks a lots!
I'm new on sql language.
Order converted dates in union query
I have the following as part of a union query:
CONVERT(CHAR(8), r.RRDate, 1) AS [Date]
I also want to order by date but when I do that it doesn't order correctly because of the conversion to char data type (for example, it puts 6/15/05 before 9/22/04 because it only looks at the first number(s) and not the year). If I try to cast it back to smalldatetime in the order by clause it tells me that ORDER BY items must appear in the select list if the statement contains a UNION operator. I get the same message if I try putting just "r.RRDate" in the ORDER BY clause. It's not that big of a deal - I can lose the formatting on the date if I need to to get it to sort correctly, but this query gets used frequently and I'd like to keep the formatting if possible.
Thanks,
Dave
Do you really require UNION operator? If the results of each SELECT statement in the UNION is distinct then use UNION ALL. This will also provide better performance since it doesn't do the duplicate elimination step. And if you use UNION ALL then you can use the column name "r.RRDate" in the ORDER BY clause. If you need to use UNION then only way is to specify the column in the SELECT list also if you want it in the ORDER BY clause. Lastly, is there any reason for your to format the date in the query itself. It is usually unnecessary work to do this on the server-side. It is best to send the date value as is and format on the client. Alternatively, you can use a style which is universal and will preserve sorting for example like the ISO unseparated date format (style 112: YYYYMMDD) or ISO 8601 datetime format (style 126: YYYY-MM-DDThh:mm:ss.nnn). Using language dependent style format is always confusing and can cause errors when you try to use it as is in a different system that has a different language setting for example.Order by?
Hi All,
I have a question in sql...
How can i sort a select statement depending on nvarchar not on Int ??
My select statement is :
" select * from table1 order by st_name asc"
can anyone help me?
thanks a lot
Sql statement "select * from table1 order by st_name" will work.
It doesnot matter whether field is nvarchar or int.
It will work for both.
|||
The database doesn't care what data type it is, just the field name to sort by. Of course, a number stored as nvarchar will sort differently than of type int, so that is a disadvantage if that is your situation.
|||What is the datatype for the fieldst_name?If the datatype is anvarchar, it will order by alpha-numeric, ifASC (ascending)
For example:
- 101 ways
- 20 cars
- 20 boats
- apple
- boy
and of course int datatype is numerical order
sql
Order by, Using a param
How do you allow sorting via one of the params?
I would like to do something like...
declare @.c char(50)
,@.s char(30)
set @.c = 'SomeClient'
set @.s = 'SomeColumn'
select @.s
select *
from MyTable
where rtrim(clientName) = rtrim(@.c)
order by rtrim(@.s)
thanks in advance..
bob.John 3:16 wrote:
> How do you allow sorting via one of the params?
> I would like to do something like...
> declare @.c char(50)
> ,@.s char(30)
> set @.c = 'SomeClient'
> set @.s = 'SomeColumn'
> select @.s
> select *
> from MyTable
> where rtrim(clientName) = rtrim(@.c)
> order by rtrim(@.s)
>
> thanks in advance..
> bob.
>
DECLARE @.ClientName VARCHAR(50)
DECLARE @.ColumnName VARCHAR(30)
DECLARE @.Command VARCHAR(1024)
SELECT @.ClientName = 'SomeClient'
SELECT @.ColumnName = 'SomeColumn'
SELECT @.Command = 'SELECT * FROM MyTable WHERE clientName = @.ClientName
ORDER BY ' + @.ColumnName
EXEC (@.Command)|||http://www.aspfaq.com/2501
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:egtoAL7kGHA.4816@.TK2MSFTNGP05.phx.gbl...
> How do you allow sorting via one of the params?
> I would like to do something like...
> declare @.c char(50)
> ,@.s char(30)
> set @.c = 'SomeClient'
> set @.s = 'SomeColumn'
> select @.s
> select *
> from MyTable
> where rtrim(clientName) = rtrim(@.c)
> order by rtrim(@.s)
>
> thanks in advance..
> bob.
>|||Thanks Tracy...
I really appreciate it.
Bob.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23TIRzN7kGHA.5108@.TK2MSFTNGP02.phx.gbl...
> John 3:16 wrote:
> DECLARE @.ClientName VARCHAR(50)
> DECLARE @.ColumnName VARCHAR(30)
> DECLARE @.Command VARCHAR(1024)
> SELECT @.ClientName = 'SomeClient'
> SELECT @.ColumnName = 'SomeColumn'
> SELECT @.Command = 'SELECT * FROM MyTable WHERE clientName = @.ClientName
> ORDER BY ' + @.ColumnName
> EXEC (@.Command)|||Thanks Aaron...
I checked out the link....
I appreciate the reply and the link.
Bob.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:upqE0N7kGHA.2052@.TK2MSFTNGP04.phx.gbl...
> http://www.aspfaq.com/2501
>
> "John 3:16" <bobmcc@.tricoequipment.com> wrote in message
> news:egtoAL7kGHA.4816@.TK2MSFTNGP05.phx.gbl...
>|||"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:es2VKR7kGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Thanks Tracy...
> I really appreciate it.
> Bob.
If your ORDER BY column name is not hard-wired into your script (highly
likely or you wouldn't need to use Dynamic SQL to generate the SELECT
statement), be very careful that you validate the column name thoroughly or
you can leave yourself open to SQL Injection, as in:
SELECT @.ColumnName = '1; TRUNCATE TABLE SomeImportantTable; --'
I would like to do something like...
declare @.c char(50)
,@.s char(30)
set @.c = 'SomeClient'
set @.s = 'SomeColumn'
select @.s
select *
from MyTable
where rtrim(clientName) = rtrim(@.c)
order by rtrim(@.s)
thanks in advance..
bob.John 3:16 wrote:
> How do you allow sorting via one of the params?
> I would like to do something like...
> declare @.c char(50)
> ,@.s char(30)
> set @.c = 'SomeClient'
> set @.s = 'SomeColumn'
> select @.s
> select *
> from MyTable
> where rtrim(clientName) = rtrim(@.c)
> order by rtrim(@.s)
>
> thanks in advance..
> bob.
>
DECLARE @.ClientName VARCHAR(50)
DECLARE @.ColumnName VARCHAR(30)
DECLARE @.Command VARCHAR(1024)
SELECT @.ClientName = 'SomeClient'
SELECT @.ColumnName = 'SomeColumn'
SELECT @.Command = 'SELECT * FROM MyTable WHERE clientName = @.ClientName
ORDER BY ' + @.ColumnName
EXEC (@.Command)|||http://www.aspfaq.com/2501
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:egtoAL7kGHA.4816@.TK2MSFTNGP05.phx.gbl...
> How do you allow sorting via one of the params?
> I would like to do something like...
> declare @.c char(50)
> ,@.s char(30)
> set @.c = 'SomeClient'
> set @.s = 'SomeColumn'
> select @.s
> select *
> from MyTable
> where rtrim(clientName) = rtrim(@.c)
> order by rtrim(@.s)
>
> thanks in advance..
> bob.
>|||Thanks Tracy...
I really appreciate it.
Bob.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23TIRzN7kGHA.5108@.TK2MSFTNGP02.phx.gbl...
> John 3:16 wrote:
> DECLARE @.ClientName VARCHAR(50)
> DECLARE @.ColumnName VARCHAR(30)
> DECLARE @.Command VARCHAR(1024)
> SELECT @.ClientName = 'SomeClient'
> SELECT @.ColumnName = 'SomeColumn'
> SELECT @.Command = 'SELECT * FROM MyTable WHERE clientName = @.ClientName
> ORDER BY ' + @.ColumnName
> EXEC (@.Command)|||Thanks Aaron...
I checked out the link....
I appreciate the reply and the link.
Bob.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:upqE0N7kGHA.2052@.TK2MSFTNGP04.phx.gbl...
> http://www.aspfaq.com/2501
>
> "John 3:16" <bobmcc@.tricoequipment.com> wrote in message
> news:egtoAL7kGHA.4816@.TK2MSFTNGP05.phx.gbl...
>|||"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:es2VKR7kGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Thanks Tracy...
> I really appreciate it.
> Bob.
If your ORDER BY column name is not hard-wired into your script (highly
likely or you wouldn't need to use Dynamic SQL to generate the SELECT
statement), be very careful that you validate the column name thoroughly or
you can leave yourself open to SQL Injection, as in:
SELECT @.ColumnName = '1; TRUNCATE TABLE SomeImportantTable; --'
ORDER BY, only orders by the first character?
Hi,
I am using a stored procedure that is databound to a dropdownlist. The stored procedure works fine, aside from the fact that the ORDER BY only sorts the list of items using the first character in the cell. E.g. The numbers 1, 20, 100 and 200 would be ordered as follows:
1
100
20
200
clearly i am doing something wrong and i apologise if it is a stupid question, but could anyone please offer me some help?
Thank you,
Shep
ORDER BY a numeric data type as opposed to ORDER BY a character data type.
Run the following code in a test database. Note the absence of indexes.
CREATE TABLE dbo.OrderByInt(number int)
GO
INSERT INTO dbo.OrderByInt(number) VALUES(1)
INSERT INTO dbo.OrderByInt(number) VALUES(10)
INSERT INTO dbo.OrderByInt(number) VALUES(100)
INSERT INTO dbo.OrderByInt(number) VALUES(2)
INSERT INTO dbo.OrderByInt(number) VALUES(20)
INSERT INTO dbo.OrderByInt(number) VALUES(200)
SELECT number FROM dbo.OrderByInt
SELECT number FROM dbo.OrderByInt ORDER BY number
CREATE TABLE dbo.OrderByVarchar(number varchar(3))
GO
INSERT INTO dbo.OrderByVarchar(number) VALUES('1')
INSERT INTO dbo.OrderByVarchar(number) VALUES('10')
INSERT INTO dbo.OrderByVarchar(number) VALUES('100')
INSERT INTO dbo.OrderByVarchar(number) VALUES('2')
INSERT INTO dbo.OrderByVarchar(number) VALUES('20')
INSERT INTO dbo.OrderByVarchar(number) VALUES('200')
SELECT number FROM dbo.OrderByVarchar
SELECT number FROM dbo.OrderByVarchar ORDER BY number
SELECT number FROM dbo.OrderByVarchar ORDER BY CAST(number AS int)
|||
It sounds like you're storing numbers as text (in other words, storing "int" values in a "varchar" column). So, SQL Server is doing a text sort, which does indeed go by the first character.
The best solution is to use a data type that matches the data (use "int" for integer numbers, and nvarchar for text).
An alternative solution is to use a CAST or CONVERT in your order by to convert it to "int" before sorting -- ORDER BY CAST(myColumn AS int) -- but any sort of bulk operation on an expression like that cripples SQL Server's query optimizer and will hurt performance on large tables.
-Ryan / Kardax
Edit: "lkh" beat me by seconds, and provides a nice illustration of the problem and solution.
|||Thank you for your reply lkd, however i am still having trouble:
When I try the above code i get the error message 'TABLE NOT SUPPORTED BY THIS EDITOR'
I am using Visual Web Developer 2005 Express Edition, could this be the problem?
Shep
|||Sorry I haven't fully explained myself (i missed the most important part, my apologise!)
i am working in units of voltage & power, so there for the listing would be as follows:
1kW
10W
100W
20W
200W
so i dont think its an option to convert the values into integers, given that I have other non-numeric characters in there! Is it a lost cause?
Shep
|||I have never used Visual Web Developer but in a quick tour of the web site I don't see anything about creating objects in a database. You may have to use an editor in your database.|||My honest though probably not helpful thought is that this is a database design problem. Can you redesign the table to have a column called Watt of int datatype?
Otherwise you could create a complicated CASE statement and sort on that column but that is not ideal.
|||Well the table is supplying the items for the dropdownlist, based on a certain product type. So just using numbers without the corresponding measurements would not look right in the dropdownlist.|||Can you add an int column that wouldn't appear in the UI to the table that would be used in the SELECT statement that populates the dropdown list?|||
If your are only using the single rightmost character (
as in your examples) as alpha, then the following may work for you.
BOTH strip off the last character, AND convert to an Integer ONLY in the ORDER BY clause.
DECLARE @.MyTestTable table
( MyColumn varchar(5) )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '2A' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '10W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '1A' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '100W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '200W' )
INSERT INTO @.MyTestTable ( MyColumn ) VALUES ( '20W' )
SELECT MyColumn
FROM @.MyTestTable
ORDER BY cast( left( MyColumn, ( len( MyColumn) - 1 )) AS int )
Hi,
thanks to everyone for their help, in the end i have resorted to simply using VB to code in the items depending on the category: e.g.
If Value = 2 Then
ddlPara1.Items.Add(New ListItem("50V", "50V"))
ddlPara1.Items.Add(New ListItem("120V", "120V"))
ddlPara1.Items.Add(New ListItem("150V", "150V"))
ddlPara1.Items.Add(New ListItem("300V", "300V"))
ddlPara1.Items.Add(New ListItem("400V", "400V"))
If Value = 3 Then........
This shouldnt affect the performance of my application should it?
Thanks again,
Shep
ORDER BY, CASE, with multiple columns
I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?
I'm not exactly sure what that'll do to performance. It'd be worth it to see what the execution plan says.
Does anyone know why this is, or what syntax would make this work?
Thanks
Also, i realize that in your order by statement, when you use CASE, all of your columns have to be the same data type.
SELECT ...
ORDER BY (CASE Lower(@.SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)
So in ORDER BY clause above, i am attempting to order by "prodprice" as one of the possibilities. This produces the error:Error converting data type varchar to numeric.
The whole IDEA of a case statement is to avoid opening yourself to injection attacks by Dynamic Execution.
So...how can you use the case statement to order by multiple columns, and to order with different datatypes?|||Not sure it this helps butview post 386101 discusses something close to your question. Maybe the method discussed near the bottom can be adapted.|||Right, so you'd have something like this:
SET @.SortExpression = Lower(@.SortExpression)
SELECT ...
ORDER BY
CASE WHEN @.SortExpression = 'prodname' THEN prodname END,
CASE WHEN @.SortExpression = 'prodname' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodsize END,
CASE WHEN @.SortExpression = 'prodsize' THEN prodname END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodprice END,
CASE WHEN @.SortExpression = 'prodprice' THEN prodname END,
prodcompany,
prodname
I'm not exactly sure what that'll do to performance. It'd be worth it to see what the execution plan says.
Terri|||that alleviated some of my problem. Thanks.
How exactly do i test the excecution plan? or check to see if it's compiling all the way?
Can i use a SQL Trace?|||Check out this article:SQL Server Query Execution Plan Analysis.
Terri
Order by with specific record on top
I have a routine that is displays my record in date order.
I want to be able to put a specific record from that set of rows on top,
with the rest in date order (as it is now, but without the one I moved to
the top.
For example, I have the following select:
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com'
order by datesubmitted desc
This gives me the following:
JobID jobTitle
_______ ________________________________________
__
3734 Desktop Computer Support Technician/22Feb05
3733 Desktop Computer Support Technician/21Feb05
3732 Desktop Computer Support Technician/19Feb05
3731 Desktop Computer Support Technician/17Feb05
3730 Desktop Computer Support Technician/15Feb05
3729 Desktop Computer Support Technician/14Feb05
3728 Desktop Computer Support Technician/12Feb05
What I want to do is take Job ID 3731 and move it to the top like so:
JobID jobTitle
_______ ________________________________________
__
3731 Desktop Computer Support Technician/17Feb05
3734 Desktop Computer Support Technician/22Feb05
3733 Desktop Computer Support Technician/21Feb05
3732 Desktop Computer Support Technician/19Feb05
3730 Desktop Computer Support Technician/15Feb05
3729 Desktop Computer Support Technician/14Feb05
3728 Desktop Computer Support Technician/12Feb05
I tried using a union ( I am sure there is a better way):
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID = 3731
union
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID <> 3731
order by datesubmitted desc
I got an error:
Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a
UNION operator.
I put datesubmitted in the select statment and got the following:
JobID jobTitle
_______ ________________________________________
__
3734 Desktop Computer Support Technician/22Feb05
3733 Desktop Computer Support Technician/21Feb05
3732 Desktop Computer Support Technician/19Feb05
3731 Desktop Computer Support Technician/17Feb05
3730 Desktop Computer Support Technician/15Feb05
3729 Desktop Computer Support Technician/14Feb05
3728 Desktop Computer Support Technician/12Feb05
I assume it did the union and then order by, which negated what I was trying
to do.
How do I do the order by, just on the 2nd select and have it put the 2
together?
Thanks,
Tom"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OEK6BxSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
>I have a routine that is displays my record in date order.
> I want to be able to put a specific record from that set of rows on top,
> with the rest in date order (as it is now, but without the one I moved to
> the top.
> For example, I have the following select:
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com'
> order by datesubmitted desc
> This gives me the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> What I want to do is take Job ID 3731 and move it to the top like so:
> JobID jobTitle
> _______ ________________________________________
__
> 3731 Desktop Computer Support Technician/17Feb05
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
>
> I tried using a union ( I am sure there is a better way):
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID = 3731
> union
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID <> 3731
> order by datesubmitted desc
> I got an error:
> Server: Msg 104, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if the statement contains a
> UNION operator.
> I put datesubmitted in the select statment and got the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> I assume it did the union and then order by, which negated what I was
> trying to do.
> How do I do the order by, just on the 2nd select and have it put the 2
> together?
Select 1 as temp,JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID = 3731
union
Select 2 as temp,JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID <> 3731
order by temp,datesubmitted desc
This seems to work, but was wondering if there was a better way than using 2
selects?
Thanks,
Tom|||>> This seems to work, but was wondering if there was a better way than
Use a CASE expression in your SELECT list & use its alias in the ORDER BY or
use a CASE expression directly in the ORDER BY clause. In your case, you can
have one like:
ORDER BY
CASE WHEN JobID = 3731 THEN 1 ELSE 2 END, datesubmitted DESC ;
Anith|||Try:
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com'
order by case when JobID=3731 then 0 else 1 end asc, datesubmitted desc
-oj
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OEK6BxSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
>I have a routine that is displays my record in date order.
> I want to be able to put a specific record from that set of rows on top,
> with the rest in date order (as it is now, but without the one I moved to
> the top.
> For example, I have the following select:
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com'
> order by datesubmitted desc
> This gives me the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> What I want to do is take Job ID 3731 and move it to the top like so:
> JobID jobTitle
> _______ ________________________________________
__
> 3731 Desktop Computer Support Technician/17Feb05
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
>
> I tried using a union ( I am sure there is a better way):
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID = 3731
> union
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID <> 3731
> order by datesubmitted desc
> I got an error:
> Server: Msg 104, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if the statement contains a
> UNION operator.
> I put datesubmitted in the select statment and got the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> I assume it did the union and then order by, which negated what I was
> trying to do.
> How do I do the order by, just on the 2nd select and have it put the 2
> together?
> Thanks,
> Tom
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ufKXI4SGFHA.208@.TK2MSFTNGP12.phx.gbl...
> Use a CASE expression in your SELECT list & use its alias in the ORDER BY
> or use a CASE expression directly in the ORDER BY clause. In your case,
> you can have one like:
> ORDER BY
> CASE WHEN JobID = 3731 THEN 1 ELSE 2 END, datesubmitted DESC ;
That did it.
Thanks,
Tom|||"oj" <nospam_ojngo@.home.com> wrote in message
news:%232JQi4SGFHA.1932@.TK2MSFTNGP14.phx.gbl...
> Try:
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com'
> order by case when JobID=3731 then 0 else 1 end asc, datesubmitted desc
That did it.
Thanks,
Tom
I want to be able to put a specific record from that set of rows on top,
with the rest in date order (as it is now, but without the one I moved to
the top.
For example, I have the following select:
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com'
order by datesubmitted desc
This gives me the following:
JobID jobTitle
_______ ________________________________________
__
3734 Desktop Computer Support Technician/22Feb05
3733 Desktop Computer Support Technician/21Feb05
3732 Desktop Computer Support Technician/19Feb05
3731 Desktop Computer Support Technician/17Feb05
3730 Desktop Computer Support Technician/15Feb05
3729 Desktop Computer Support Technician/14Feb05
3728 Desktop Computer Support Technician/12Feb05
What I want to do is take Job ID 3731 and move it to the top like so:
JobID jobTitle
_______ ________________________________________
__
3731 Desktop Computer Support Technician/17Feb05
3734 Desktop Computer Support Technician/22Feb05
3733 Desktop Computer Support Technician/21Feb05
3732 Desktop Computer Support Technician/19Feb05
3730 Desktop Computer Support Technician/15Feb05
3729 Desktop Computer Support Technician/14Feb05
3728 Desktop Computer Support Technician/12Feb05
I tried using a union ( I am sure there is a better way):
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID = 3731
union
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID <> 3731
order by datesubmitted desc
I got an error:
Server: Msg 104, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if the statement contains a
UNION operator.
I put datesubmitted in the select statment and got the following:
JobID jobTitle
_______ ________________________________________
__
3734 Desktop Computer Support Technician/22Feb05
3733 Desktop Computer Support Technician/21Feb05
3732 Desktop Computer Support Technician/19Feb05
3731 Desktop Computer Support Technician/17Feb05
3730 Desktop Computer Support Technician/15Feb05
3729 Desktop Computer Support Technician/14Feb05
3728 Desktop Computer Support Technician/12Feb05
I assume it did the union and then order by, which negated what I was trying
to do.
How do I do the order by, just on the 2nd select and have it put the 2
together?
Thanks,
Tom"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OEK6BxSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
>I have a routine that is displays my record in date order.
> I want to be able to put a specific record from that set of rows on top,
> with the rest in date order (as it is now, but without the one I moved to
> the top.
> For example, I have the following select:
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com'
> order by datesubmitted desc
> This gives me the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> What I want to do is take Job ID 3731 and move it to the top like so:
> JobID jobTitle
> _______ ________________________________________
__
> 3731 Desktop Computer Support Technician/17Feb05
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
>
> I tried using a union ( I am sure there is a better way):
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID = 3731
> union
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID <> 3731
> order by datesubmitted desc
> I got an error:
> Server: Msg 104, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if the statement contains a
> UNION operator.
> I put datesubmitted in the select statment and got the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> I assume it did the union and then order by, which negated what I was
> trying to do.
> How do I do the order by, just on the 2nd select and have it put the 2
> together?
Select 1 as temp,JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID = 3731
union
Select 2 as temp,JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com' and
JobID <> 3731
order by temp,datesubmitted desc
This seems to work, but was wondering if there was a better way than using 2
selects?
Thanks,
Tom|||>> This seems to work, but was wondering if there was a better way than
Use a CASE expression in your SELECT list & use its alias in the ORDER BY or
use a CASE expression directly in the ORDER BY clause. In your case, you can
have one like:
ORDER BY
CASE WHEN JobID = 3731 THEN 1 ELSE 2 END, datesubmitted DESC ;
Anith|||Try:
Select JobID,
jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
',''))
from ApplicantResume a
join Position p on (a.PositionID = p.PositionID)
where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
and
Email = 'tscheid@.yahoo.com'
order by case when JobID=3731 then 0 else 1 end asc, datesubmitted desc
-oj
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OEK6BxSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
>I have a routine that is displays my record in date order.
> I want to be able to put a specific record from that set of rows on top,
> with the rest in date order (as it is now, but without the one I moved to
> the top.
> For example, I have the following select:
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com'
> order by datesubmitted desc
> This gives me the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> What I want to do is take Job ID 3731 and move it to the top like so:
> JobID jobTitle
> _______ ________________________________________
__
> 3731 Desktop Computer Support Technician/17Feb05
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
>
> I tried using a union ( I am sure there is a better way):
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID = 3731
> union
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com' and
> JobID <> 3731
> order by datesubmitted desc
> I got an error:
> Server: Msg 104, Level 15, State 1, Line 1
> ORDER BY items must appear in the select list if the statement contains a
> UNION operator.
> I put datesubmitted in the select statment and got the following:
> JobID jobTitle
> _______ ________________________________________
__
> 3734 Desktop Computer Support Technician/22Feb05
> 3733 Desktop Computer Support Technician/21Feb05
> 3732 Desktop Computer Support Technician/19Feb05
> 3731 Desktop Computer Support Technician/17Feb05
> 3730 Desktop Computer Support Technician/15Feb05
> 3729 Desktop Computer Support Technician/14Feb05
> 3728 Desktop Computer Support Technician/12Feb05
> I assume it did the union and then order by, which negated what I was
> trying to do.
> How do I do the order by, just on the 2nd select and have it put the 2
> together?
> Thanks,
> Tom
>|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ufKXI4SGFHA.208@.TK2MSFTNGP12.phx.gbl...
> Use a CASE expression in your SELECT list & use its alias in the ORDER BY
> or use a CASE expression directly in the ORDER BY clause. In your case,
> you can have one like:
> ORDER BY
> CASE WHEN JobID = 3731 THEN 1 ELSE 2 END, datesubmitted DESC ;
That did it.
Thanks,
Tom|||"oj" <nospam_ojngo@.home.com> wrote in message
news:%232JQi4SGFHA.1932@.TK2MSFTNGP14.phx.gbl...
> Try:
> Select JobID,
> jobTitle = (jobTitle + '/' + replace(convert(varchar,DateSubmitted,6)
,'
> ',''))
> from ApplicantResume a
> join Position p on (a.PositionID = p.PositionID)
> where a.positionID = 25 and FirstName = 'Tom' and LastName = 'Scheiderich'
> and
> Email = 'tscheid@.yahoo.com'
> order by case when JobID=3731 then 0 else 1 end asc, datesubmitted desc
That did it.
Thanks,
Tom
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 SELECT DISTINCT
Hi,
I am trying to sort a SELECT DISTINCT query using ORDER BY, but get an error
message saying that the ORDER BY needs to be included in the SELECT
statemeny.
Does anyone have a sample on how to sort a SELECT DISTINCT query ?
Niclas"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:ubdCBujQGHA.4900@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am trying to sort a SELECT DISTINCT query using ORDER BY, but get an
> error message saying that the ORDER BY needs to be included in the SELECT
> statemeny.
> Does anyone have a sample on how to sort a SELECT DISTINCT query ?
> Niclas
>
The columns you want to order on have to be included in the SELECT list.
Here's why:
CREATE TABLE tbl (x INTEGER NOT NULL, z INTEGER NOT NULL, PRIMARY KEY
(x,z));
INSERT INTO tbl (x,z) VALUES (100,1);
INSERT INTO tbl (x,z) VALUES (100,2);
INSERT INTO tbl (x,z) VALUES (200,0);
INSERT INTO tbl (x,z) VALUES (200,4);
SELECT DISTINCT x FROM tbl ORDER BY z;
Result:
Server: Msg 145, Level 15, State 1, Line 9
ORDER BY items must appear in the select list if SELECT DISTINCT is
specified.
Can you explain how SQL Server could return X ordered by Z in this example?
Should 100 come first or should 200 come first? There is no single answer so
that's why it has to be disallowed unless Z is in the SELECT list. Example:
SELECT DISTINCT x,z FROM tbl ORDER BY z;
The problem is with your specification rather than with SQL Server. You
haven't given us a clue about what you really want to sort on so here are a
couple of possibilities using the above example data. Notice you'll get two
different orders:
SELECT x
FROM tbl
GROUP BY x
ORDER BY MIN(z);
x
--
200
100
(2 row(s) affected)
SELECT x
FROM tbl
GROUP BY x
ORDER BY MAX(z);
x
--
100
200
(2 row(s) affected)
Hope this helps.
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
--
I am trying to sort a SELECT DISTINCT query using ORDER BY, but get an error
message saying that the ORDER BY needs to be included in the SELECT
statemeny.
Does anyone have a sample on how to sort a SELECT DISTINCT query ?
Niclas"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:ubdCBujQGHA.4900@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am trying to sort a SELECT DISTINCT query using ORDER BY, but get an
> error message saying that the ORDER BY needs to be included in the SELECT
> statemeny.
> Does anyone have a sample on how to sort a SELECT DISTINCT query ?
> Niclas
>
The columns you want to order on have to be included in the SELECT list.
Here's why:
CREATE TABLE tbl (x INTEGER NOT NULL, z INTEGER NOT NULL, PRIMARY KEY
(x,z));
INSERT INTO tbl (x,z) VALUES (100,1);
INSERT INTO tbl (x,z) VALUES (100,2);
INSERT INTO tbl (x,z) VALUES (200,0);
INSERT INTO tbl (x,z) VALUES (200,4);
SELECT DISTINCT x FROM tbl ORDER BY z;
Result:
Server: Msg 145, Level 15, State 1, Line 9
ORDER BY items must appear in the select list if SELECT DISTINCT is
specified.
Can you explain how SQL Server could return X ordered by Z in this example?
Should 100 come first or should 200 come first? There is no single answer so
that's why it has to be disallowed unless Z is in the SELECT list. Example:
SELECT DISTINCT x,z FROM tbl ORDER BY z;
The problem is with your specification rather than with SQL Server. You
haven't given us a clue about what you really want to sort on so here are a
couple of possibilities using the above example data. Notice you'll get two
different orders:
SELECT x
FROM tbl
GROUP BY x
ORDER BY MIN(z);
x
--
200
100
(2 row(s) affected)
SELECT x
FROM tbl
GROUP BY x
ORDER BY MAX(z);
x
--
100
200
(2 row(s) affected)
Hope this helps.
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
--
Order By with Query then bottom border
Reporting Services 2000
I have a SQL query that is properly sorting a list of items I have by using
the order by clause. I created a report using the report wizard and didn't
use any fields to group by because I already have the list in the correct
order. I did use RS to keep each team on it's own page. How can I have a
border or bgcolor change when the owner of a project changes. For example
I'd like a border after Bob and before John.
Sample Data:
Team Owner Project
NY bob Remote Access
NY bob Server Reboot
NY John Network Upgrade
Here is my order by clause:
ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 ENDDid you try to use an expression to set border width?
=IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Reporting Services 2000
> I have a SQL query that is properly sorting a list of items I have by using
> the order by clause. I created a report using the report wizard and didn't
> use any fields to group by because I already have the list in the correct
> order. I did use RS to keep each team on it's own page. How can I have a
> border or bgcolor change when the owner of a project changes. For example
> I'd like a border after Bob and before John.
> Sample Data:
> Team Owner Project
> NY bob Remote Access
> NY bob Server Reboot
> NY John Network Upgrade
> Here is my order by clause:
> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>
>|||I didn't try that. Thank you.
Where can I find a list of all the functions that can be called?
"isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> Did you try to use an expression to set border width?
> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>|||Can I confuse this just a tad more. What if I only want a border the very
first time the owner changes and don't need a border after that?
"Colin" <legendsfan@.spamhotmail.com> wrote in message
news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
>I didn't try that. Thank you.
> Where can I find a list of all the functions that can be called?
> "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
>> Did you try to use an expression to set border width?
>> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
>> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the
>> correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>
>|||For the function list go to SqlServer Books Online, "Using Functions in
Reporting Services" is the name of the topic.
To show the border the first time only, try this
=IIF(Fields!OwnerValue <> Previous(Fields!Owner.Value) AND
Previous(Fields!Owner.Value)= Min(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Can I confuse this just a tad more. What if I only want a border the very
> first time the owner changes and don't need a border after that?
> "Colin" <legendsfan@.spamhotmail.com> wrote in message
> news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
> >I didn't try that. Thank you.
> >
> > Where can I find a list of all the functions that can be called?
> >
> > "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> > news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> >> Did you try to use an expression to set border width?
> >>
> >> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> >>
> >> "Colin" wrote:
> >>
> >> Reporting Services 2000
> >> I have a SQL query that is properly sorting a list of items I have by
> >> using
> >> the order by clause. I created a report using the report wizard and
> >> didn't
> >> use any fields to group by because I already have the list in the
> >> correct
> >> order. I did use RS to keep each team on it's own page. How can I have
> >> a
> >> border or bgcolor change when the owner of a project changes. For
> >> example
> >> I'd like a border after Bob and before John.
> >>
> >> Sample Data:
> >> Team Owner Project
> >> NY bob Remote Access
> >> NY bob Server Reboot
> >> NY John Network Upgrade
> >>
> >> Here is my order by clause:
> >> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
> >>
> >>
> >>
> >>
> >
> >
>
>
I have a SQL query that is properly sorting a list of items I have by using
the order by clause. I created a report using the report wizard and didn't
use any fields to group by because I already have the list in the correct
order. I did use RS to keep each team on it's own page. How can I have a
border or bgcolor change when the owner of a project changes. For example
I'd like a border after Bob and before John.
Sample Data:
Team Owner Project
NY bob Remote Access
NY bob Server Reboot
NY John Network Upgrade
Here is my order by clause:
ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 ENDDid you try to use an expression to set border width?
=IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Reporting Services 2000
> I have a SQL query that is properly sorting a list of items I have by using
> the order by clause. I created a report using the report wizard and didn't
> use any fields to group by because I already have the list in the correct
> order. I did use RS to keep each team on it's own page. How can I have a
> border or bgcolor change when the owner of a project changes. For example
> I'd like a border after Bob and before John.
> Sample Data:
> Team Owner Project
> NY bob Remote Access
> NY bob Server Reboot
> NY John Network Upgrade
> Here is my order by clause:
> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>
>|||I didn't try that. Thank you.
Where can I find a list of all the functions that can be called?
"isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> Did you try to use an expression to set border width?
> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>|||Can I confuse this just a tad more. What if I only want a border the very
first time the owner changes and don't need a border after that?
"Colin" <legendsfan@.spamhotmail.com> wrote in message
news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
>I didn't try that. Thank you.
> Where can I find a list of all the functions that can be called?
> "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
>> Did you try to use an expression to set border width?
>> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
>> "Colin" wrote:
>> Reporting Services 2000
>> I have a SQL query that is properly sorting a list of items I have by
>> using
>> the order by clause. I created a report using the report wizard and
>> didn't
>> use any fields to group by because I already have the list in the
>> correct
>> order. I did use RS to keep each team on it's own page. How can I have
>> a
>> border or bgcolor change when the owner of a project changes. For
>> example
>> I'd like a border after Bob and before John.
>> Sample Data:
>> Team Owner Project
>> NY bob Remote Access
>> NY bob Server Reboot
>> NY John Network Upgrade
>> Here is my order by clause:
>> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
>>
>>
>|||For the function list go to SqlServer Books Online, "Using Functions in
Reporting Services" is the name of the topic.
To show the border the first time only, try this
=IIF(Fields!OwnerValue <> Previous(Fields!Owner.Value) AND
Previous(Fields!Owner.Value)= Min(Fields!Owner.Value),1pt,0pt)
"Colin" wrote:
> Can I confuse this just a tad more. What if I only want a border the very
> first time the owner changes and don't need a border after that?
> "Colin" <legendsfan@.spamhotmail.com> wrote in message
> news:O1DEBwKwGHA.4460@.TK2MSFTNGP04.phx.gbl...
> >I didn't try that. Thank you.
> >
> > Where can I find a list of all the functions that can be called?
> >
> > "isaak" <isaak.peretsman at usa.dupont.com (no spam)> wrote in message
> > news:E28C729B-AE19-4ADF-9983-233B666CE5D1@.microsoft.com...
> >> Did you try to use an expression to set border width?
> >>
> >> =IIF(Fields!OwnerValue = Previous(Fields!Owner.Value),1pt,0pt)
> >>
> >> "Colin" wrote:
> >>
> >> Reporting Services 2000
> >> I have a SQL query that is properly sorting a list of items I have by
> >> using
> >> the order by clause. I created a report using the report wizard and
> >> didn't
> >> use any fields to group by because I already have the list in the
> >> correct
> >> order. I did use RS to keep each team on it's own page. How can I have
> >> a
> >> border or bgcolor change when the owner of a project changes. For
> >> example
> >> I'd like a border after Bob and before John.
> >>
> >> Sample Data:
> >> Team Owner Project
> >> NY bob Remote Access
> >> NY bob Server Reboot
> >> NY John Network Upgrade
> >>
> >> Here is my order by clause:
> >> ORDER BY Team, CASE WHEN Owner = Team THEN 1 ELSE 2 END
> >>
> >>
> >>
> >>
> >
> >
>
>
Subscribe to:
Posts (Atom)