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
Showing posts with label rows. Show all posts
Showing posts with label rows. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Order By Primary Key
I've got a table with an a primary key of type 'int' (auto incrementing).
Were talking about millions of rows.
The name of this field is TempID.
I always want to sort by the TempID ascending...is that something that I
even need to specify?
When I query it in Query Analyzer it seems to always be sorted by the
primary key ascending.
Although this is what I want, will this ALWAYS be the case, or should I just
throw an order by in there for good measure?
Stored Proc:
--
@.numberOfRows int,
@.startingID int
set rowcount @.numberOfRows
Select * From tblList Where TempID > @.startingID
set rowcount 0
--
Will this always work how I expect or should I add the "Order By TempID"?
I am asking because I don't know if my Order By clause will slow it down.
I'm sure I wouldn't notice if it was .0000000002 ms slower, but this would
be good to know, especially if @.startingID = 4,000,000 or the @.numberOfRows
is very large.
I hope that all made sense.Read the BOL for more information under "Clustered Indexes":
"An index on the columns specified in the ORDER BY or GROUP BY clause
may remove the need for the Database Engine to sort the data, because
the rows are already sorted. This improves query performance."
Didi you create them as Clustered keys ?
HTH, Jens Suessmeyer.|||INeedADip wrote:
> I've got a table with an a primary key of type 'int' (auto
> incrementing). Were talking about millions of rows.
> The name of this field is TempID.
> I always want to sort by the TempID ascending...is that something
> that I even need to specify?
> When I query it in Query Analyzer it seems to always be sorted by the
> primary key ascending.
> Although this is what I want, will this ALWAYS be the case, or should
> I just throw an order by in there for good measure?
> Stored Proc:
> --
> @.numberOfRows int,
> @.startingID int
> set rowcount @.numberOfRows
> Select * From tblList Where TempID > @.startingID
> set rowcount 0
> --
> Will this always work how I expect or should I add the "Order By
> TempID"? I am asking because I don't know if my Order By clause will
> slow it
> down. I'm sure I wouldn't notice if it was .0000000002 ms slower, but
> this would be good to know, especially if @.startingID = 4,000,000 or
> the @.numberOfRows is very large.
> I hope that all made sense.
The only way to guarantee the order of results is with an ORDER BY
clause. If that column is your PK, you should already have a unique
index on the column. If it's clustered, it will help with sorting. Keep
in mind that sort operations are very costly. While the unique,
clustered index on the PK column might eliminate the need for the sort,
it is no guarantee. You still need the ORDER BY clause.
David Gugick
Quest Software
www.quest.com|||You have a ton of basic mistakes and need to get an intro book on SQL.
Columns are not fields. An auto-increment can never be a relational
key by definition because it is not an attribute of the entities
modeled in the table. Tables by definition have no ordering.
You are confusing an implementation with SQL and proper data modeling.
If you want to be sure that you get your output in order, you need an
ORDER BY clause.
Years ago, SQL Server programmers got screwed up becuase they wrote
code that assumed a GROUP BY clause would be done with a sort under the
covers, so they did not write the ORDER clause. It blew up a ton of
programs on the next release.|||Wow...guess I hit a nerve.
I'm sorry, I should have picked my words a little more carefully.
I figured you would get the jist of it....
Why would auto-increment fields not be an attribute of the entities in the
table?
Isn't that one of the easiest ways to get a unique identifier for that set
of data, making it a key attribute?
"can never be a relational key"...I've never heard that argument.|||There are different schools of thought on using auto-increment fields as
keys. Personally, I tend to agree with Celko on this one. Auto-increment
fields have nothing to do with the data in the table, they are an artificial
way of creating uniqueness. A more meaningful key would suit my tastes.
Using meaningful keys allows you to
1. Reuse the column name and data type in all tables, which makes joins more
intuitive.
Job.deptid = department.deptid
is easier and more intuitive than
job.Department = department.id
2. Use a key value that makes some sort of sense when you look at it.
3. Avoid redundancy in your table and insure normalization.
Often when we have an auto-incremeneting key, there is another column or
columns that also uniquely identify the row.
However, there are many in the field (with as much experience as myself or
Celko) that consider auto-increment keys a requirement in their tables.
As for the terminology, do a search on celko and punch cards to see why he
makes such an issue of it. As long as everone knows what you mean by
"field" and "record" (and we do) I wouldn't lose any sleep over it.
"INeedADip" <INeedADip@.gmail.com> wrote in message
news:eF8AOUEIGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Wow...guess I hit a nerve.
> I'm sorry, I should have picked my words a little more carefully.
> I figured you would get the jist of it....
> Why would auto-increment fields not be an attribute of the entities in the
> table?
> Isn't that one of the easiest ways to get a unique identifier for that set
> of data, making it a key attribute?
> "can never be a relational key"...I've never heard that argument.
>|||So you guys are just refering to the name of the column?
I do have incrementing fields in 99% of my tables like AccountID, QueueID,
CampaignID, ect...
If that is what you guys are talking about, I agree....anyways...
Thanks for educating me on the Order By, I was under the impression that if
I used an index on the field, I could save some time by not using the Order
By. And just to note...I'm wasn't asking this question as a general rule,
but in this one case (this specific table) I was trying to save some time
because the table is so large and I will only be getting chunks in ascending
order EVERY TIME...but if it is not guaranteed, then I will add the "Order
By".
- Thanks|||Hi Jim,
I've got to take the bite and put the surrogate key side forward.
If a natural key exists on the table (that isn't in reality a generated
number in itself - for instance deptid would indicate its auto-generated
some how), most keys in the natural world are actually a surrogate of some
form or another.
There are problems duplicating the natural key everywhere, especially if its
a composite key; there is no difference in joining whether you use a
surrogate key or a natural one to join.
INeedADip - do a search on 'surrogate key' and 'identity' for some really
good and extensive discussions on the benefits of the surrogate key
approach.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uNTKWeEIGHA.376@.TK2MSFTNGP12.phx.gbl...
> There are different schools of thought on using auto-increment fields as
> keys. Personally, I tend to agree with Celko on this one. Auto-increment
> fields have nothing to do with the data in the table, they are an
> artificial
> way of creating uniqueness. A more meaningful key would suit my tastes.
> Using meaningful keys allows you to
> 1. Reuse the column name and data type in all tables, which makes joins
> more
> intuitive.
> Job.deptid = department.deptid
> is easier and more intuitive than
> job.Department = department.id
> 2. Use a key value that makes some sort of sense when you look at it.
> 3. Avoid redundancy in your table and insure normalization.
> Often when we have an auto-incremeneting key, there is another column
> or
> columns that also uniquely identify the row.
> However, there are many in the field (with as much experience as myself or
> Celko) that consider auto-increment keys a requirement in their tables.
> As for the terminology, do a search on celko and punch cards to see why he
> makes such an issue of it. As long as everone knows what you mean by
> "field" and "record" (and we do) I wouldn't lose any sleep over it.
>
> "INeedADip" <INeedADip@.gmail.com> wrote in message
> news:eF8AOUEIGHA.2704@.TK2MSFTNGP15.phx.gbl...
>|||Thanks Tony.
I believe there was a fairly in depth discussion about this last w
,
although I purge my postings frequently and can't locate it. If I recall
correctly, you were one of the folks involved in the discussion. Would you
mind posting a link to it?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eyo318FIGHA.2912@.tk2msftngp13.phx.gbl...
> Hi Jim,
> I've got to take the bite and put the surrogate key side forward.
> If a natural key exists on the table (that isn't in reality a generated
> number in itself - for instance deptid would indicate its auto-generated
> some how), most keys in the natural world are actually a surrogate of some
> form or another.
> There are problems duplicating the natural key everywhere, especially if
its
> a composite key; there is no difference in joining whether you use a
> surrogate key or a natural one to join.
> INeedADip - do a search on 'surrogate key' and 'identity' for some really
> good and extensive discussions on the benefits of the surrogate key
> approach.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:uNTKWeEIGHA.376@.TK2MSFTNGP12.phx.gbl...
Auto-increment
or
he
>|||I think this is one of them...
6d2a8ccf940fe2" target="_blank">http://groups.google.co.uk/group/co...
6d2a8ccf940fe2
but there are many, search on 'surrogate key' rogerson --celko--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eIiy5BGIGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Thanks Tony.
> I believe there was a fairly in depth discussion about this last w,
> although I purge my postings frequently and can't locate it. If I recall
> correctly, you were one of the folks involved in the discussion. Would
> you
> mind posting a link to it?
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eyo318FIGHA.2912@.tk2msftngp13.phx.gbl...
> its
> Auto-increment
> or
> he
>
Were talking about millions of rows.
The name of this field is TempID.
I always want to sort by the TempID ascending...is that something that I
even need to specify?
When I query it in Query Analyzer it seems to always be sorted by the
primary key ascending.
Although this is what I want, will this ALWAYS be the case, or should I just
throw an order by in there for good measure?
Stored Proc:
--
@.numberOfRows int,
@.startingID int
set rowcount @.numberOfRows
Select * From tblList Where TempID > @.startingID
set rowcount 0
--
Will this always work how I expect or should I add the "Order By TempID"?
I am asking because I don't know if my Order By clause will slow it down.
I'm sure I wouldn't notice if it was .0000000002 ms slower, but this would
be good to know, especially if @.startingID = 4,000,000 or the @.numberOfRows
is very large.
I hope that all made sense.Read the BOL for more information under "Clustered Indexes":
"An index on the columns specified in the ORDER BY or GROUP BY clause
may remove the need for the Database Engine to sort the data, because
the rows are already sorted. This improves query performance."
Didi you create them as Clustered keys ?
HTH, Jens Suessmeyer.|||INeedADip wrote:
> I've got a table with an a primary key of type 'int' (auto
> incrementing). Were talking about millions of rows.
> The name of this field is TempID.
> I always want to sort by the TempID ascending...is that something
> that I even need to specify?
> When I query it in Query Analyzer it seems to always be sorted by the
> primary key ascending.
> Although this is what I want, will this ALWAYS be the case, or should
> I just throw an order by in there for good measure?
> Stored Proc:
> --
> @.numberOfRows int,
> @.startingID int
> set rowcount @.numberOfRows
> Select * From tblList Where TempID > @.startingID
> set rowcount 0
> --
> Will this always work how I expect or should I add the "Order By
> TempID"? I am asking because I don't know if my Order By clause will
> slow it
> down. I'm sure I wouldn't notice if it was .0000000002 ms slower, but
> this would be good to know, especially if @.startingID = 4,000,000 or
> the @.numberOfRows is very large.
> I hope that all made sense.
The only way to guarantee the order of results is with an ORDER BY
clause. If that column is your PK, you should already have a unique
index on the column. If it's clustered, it will help with sorting. Keep
in mind that sort operations are very costly. While the unique,
clustered index on the PK column might eliminate the need for the sort,
it is no guarantee. You still need the ORDER BY clause.
David Gugick
Quest Software
www.quest.com|||You have a ton of basic mistakes and need to get an intro book on SQL.
Columns are not fields. An auto-increment can never be a relational
key by definition because it is not an attribute of the entities
modeled in the table. Tables by definition have no ordering.
You are confusing an implementation with SQL and proper data modeling.
If you want to be sure that you get your output in order, you need an
ORDER BY clause.
Years ago, SQL Server programmers got screwed up becuase they wrote
code that assumed a GROUP BY clause would be done with a sort under the
covers, so they did not write the ORDER clause. It blew up a ton of
programs on the next release.|||Wow...guess I hit a nerve.
I'm sorry, I should have picked my words a little more carefully.
I figured you would get the jist of it....
Why would auto-increment fields not be an attribute of the entities in the
table?
Isn't that one of the easiest ways to get a unique identifier for that set
of data, making it a key attribute?
"can never be a relational key"...I've never heard that argument.|||There are different schools of thought on using auto-increment fields as
keys. Personally, I tend to agree with Celko on this one. Auto-increment
fields have nothing to do with the data in the table, they are an artificial
way of creating uniqueness. A more meaningful key would suit my tastes.
Using meaningful keys allows you to
1. Reuse the column name and data type in all tables, which makes joins more
intuitive.
Job.deptid = department.deptid
is easier and more intuitive than
job.Department = department.id
2. Use a key value that makes some sort of sense when you look at it.
3. Avoid redundancy in your table and insure normalization.
Often when we have an auto-incremeneting key, there is another column or
columns that also uniquely identify the row.
However, there are many in the field (with as much experience as myself or
Celko) that consider auto-increment keys a requirement in their tables.
As for the terminology, do a search on celko and punch cards to see why he
makes such an issue of it. As long as everone knows what you mean by
"field" and "record" (and we do) I wouldn't lose any sleep over it.
"INeedADip" <INeedADip@.gmail.com> wrote in message
news:eF8AOUEIGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Wow...guess I hit a nerve.
> I'm sorry, I should have picked my words a little more carefully.
> I figured you would get the jist of it....
> Why would auto-increment fields not be an attribute of the entities in the
> table?
> Isn't that one of the easiest ways to get a unique identifier for that set
> of data, making it a key attribute?
> "can never be a relational key"...I've never heard that argument.
>|||So you guys are just refering to the name of the column?
I do have incrementing fields in 99% of my tables like AccountID, QueueID,
CampaignID, ect...
If that is what you guys are talking about, I agree....anyways...
Thanks for educating me on the Order By, I was under the impression that if
I used an index on the field, I could save some time by not using the Order
By. And just to note...I'm wasn't asking this question as a general rule,
but in this one case (this specific table) I was trying to save some time
because the table is so large and I will only be getting chunks in ascending
order EVERY TIME...but if it is not guaranteed, then I will add the "Order
By".
- Thanks|||Hi Jim,
I've got to take the bite and put the surrogate key side forward.
If a natural key exists on the table (that isn't in reality a generated
number in itself - for instance deptid would indicate its auto-generated
some how), most keys in the natural world are actually a surrogate of some
form or another.
There are problems duplicating the natural key everywhere, especially if its
a composite key; there is no difference in joining whether you use a
surrogate key or a natural one to join.
INeedADip - do a search on 'surrogate key' and 'identity' for some really
good and extensive discussions on the benefits of the surrogate key
approach.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uNTKWeEIGHA.376@.TK2MSFTNGP12.phx.gbl...
> There are different schools of thought on using auto-increment fields as
> keys. Personally, I tend to agree with Celko on this one. Auto-increment
> fields have nothing to do with the data in the table, they are an
> artificial
> way of creating uniqueness. A more meaningful key would suit my tastes.
> Using meaningful keys allows you to
> 1. Reuse the column name and data type in all tables, which makes joins
> more
> intuitive.
> Job.deptid = department.deptid
> is easier and more intuitive than
> job.Department = department.id
> 2. Use a key value that makes some sort of sense when you look at it.
> 3. Avoid redundancy in your table and insure normalization.
> Often when we have an auto-incremeneting key, there is another column
> or
> columns that also uniquely identify the row.
> However, there are many in the field (with as much experience as myself or
> Celko) that consider auto-increment keys a requirement in their tables.
> As for the terminology, do a search on celko and punch cards to see why he
> makes such an issue of it. As long as everone knows what you mean by
> "field" and "record" (and we do) I wouldn't lose any sleep over it.
>
> "INeedADip" <INeedADip@.gmail.com> wrote in message
> news:eF8AOUEIGHA.2704@.TK2MSFTNGP15.phx.gbl...
>|||Thanks Tony.
I believe there was a fairly in depth discussion about this last w
,although I purge my postings frequently and can't locate it. If I recall
correctly, you were one of the folks involved in the discussion. Would you
mind posting a link to it?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:eyo318FIGHA.2912@.tk2msftngp13.phx.gbl...
> Hi Jim,
> I've got to take the bite and put the surrogate key side forward.
> If a natural key exists on the table (that isn't in reality a generated
> number in itself - for instance deptid would indicate its auto-generated
> some how), most keys in the natural world are actually a surrogate of some
> form or another.
> There are problems duplicating the natural key everywhere, especially if
its
> a composite key; there is no difference in joining whether you use a
> surrogate key or a natural one to join.
> INeedADip - do a search on 'surrogate key' and 'identity' for some really
> good and extensive discussions on the benefits of the surrogate key
> approach.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:uNTKWeEIGHA.376@.TK2MSFTNGP12.phx.gbl...
Auto-increment
or
he
>|||I think this is one of them...
6d2a8ccf940fe2" target="_blank">http://groups.google.co.uk/group/co...
6d2a8ccf940fe2
but there are many, search on 'surrogate key' rogerson --celko--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eIiy5BGIGHA.2212@.TK2MSFTNGP15.phx.gbl...
> Thanks Tony.
> I believe there was a fairly in depth discussion about this last w
,> although I purge my postings frequently and can't locate it. If I recall
> correctly, you were one of the folks involved in the discussion. Would
> you
> mind posting a link to it?
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:eyo318FIGHA.2912@.tk2msftngp13.phx.gbl...
> its
> Auto-increment
> or
> he
>
ORDER BY not working with ADO or OLE
I am inserting rows into an Excel file and the ORDER BY is coming out wrong.
When I run the select I get priority 1,2,3,4, ...10, 11, 12, ... as I should.
But in the excel file the rows come out 1,10,11,12,13 ..., 2, 20, 21, ...
SET @.sql = ' INSERT INTO OpenRowset(''Microsoft.Jet.OLEDB.4.0'',''Excel 5.0;Database='+@.finalFile+';HDR=YES'',
''SELECT [ID],[Priority],[Comments] FROM [Sheet1$]'')
SELECT ID,priority,comments FROM OurTable WHERE orgId='+@.orgId+' ORDER BY priority,ID'
EXECUTE (@.sql)
Please help!! Thank you!
The order by is ordering them in string order. The type coming in must be a string type. Try converting them to integer to get the right order (e.g. ORDER BY convert(integer, priority.ID). That should clear things up.
Hope that helps,
John
Friday, March 23, 2012
Order By date and time of insert
I just wanted to know is there any way to order the rows of table based on date and time of insert. I dont have date column in my table. I have to insert values into the table. Based on the system date and time of the values inserted into the table, can i order the rows??As you have posted a question in the articles section it is being moved to SQL Server Forum .
MODERAOTR.|||for that you need to add a new column to the table and store system date and time in that column for every new insert and finally sort the data by that column.
Order By computed columns
Hi all,
I have a long runing query took 70s and returns only 124 rows. I found the
problem is that it uses a compute column in the Order By clause. Something
like this
SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
If I took that Order By away, it only takes 2s. (That make me think my C#
client code can sort better than that :P )
Can anyone show me what are the ways I can do to optimize it?
Any thoughts are appreciated.
ConradConrad Chan wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
If that expression is in the SELECT clause you can use
ORDER BY n
where n is the ordinal number of the expression in the SELECT clause.
E.g.:
SELECT col1, col2, (col4 * 0.25) / 100, ...
FROM ...
ORDER BY 3
Will sort the resultset by the value of the 3rd column in the SELECT
clause.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjdSGYechKqOuFEgEQKsQgCggvSDYuQCwIcw
DXSdEtuVA3YD+b4AnixS
BnAeboIAn+Ja2WD/GUp486uA
=1vFd
--END PGP SIGNATURE--|||If you can do it in the client side, then do it.
AMB
"Conrad Chan" wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||I will say only if db really cannot do a better job.
Thanks
Conrad
"Alejandro Mesa" wrote:
> If you can do it in the client side, then do it.
>
> AMB
> "Conrad Chan" wrote:
>|||Unfortunately it doesn't make a difference :<
Conrad
"MGFoster" wrote:
> Conrad Chan wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> If that expression is in the SELECT clause you can use
> ORDER BY n
> where n is the ordinal number of the expression in the SELECT clause.
> E.g.:
> SELECT col1, col2, (col4 * 0.25) / 100, ...
> FROM ...
> ORDER BY 3
> Will sort the resultset by the value of the 3rd column in the SELECT
> clause.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQjdSGYechKqOuFEgEQKsQgCggvSDYuQCwIcw
DXSdEtuVA3YD+b4AnixS
> BnAeboIAn+Ja2WD/GUp486uA
> =1vFd
> --END PGP SIGNATURE--
>|||You can simplify the ORDER BY clause to
ORDER BY COALESCE(Table1.Field1, ''), COALESCE(CAST(Table2.Field2 AS
nvarchar), '''')
or even to
ORDER BY Table1.Field1, Table2.Field2
HTH,
Gert-Jan
Conrad Chan wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||Conrad,
In General it should never take SQL Server 68 seconds to sort 124
records... SOmething else is going on here ... Run the query in Query
Analyzer with ShowPlan ON, and see what step in the showplan is taking that
long...
"Conrad Chan" wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||Thanks CBretana,
I did look into query analyzer. 90% is done on the Sort. The only thing I
found may be interested is that the estimated row count is 15,000 compared
with 124 row count.
Conrad
"CBretana" wrote:
> Conrad,
> In General it should never take SQL Server 68 seconds to sort 124
> records... SOmething else is going on here ... Run the query in Query
> Analyzer with ShowPlan ON, and see what step in the showplan is taking tha
t
> long...
>
> "Conrad Chan" wrote:
>|||Then you have a filter in the query somewhere, which is reducing the output
from 15,000 to 124, and the sort is happening on the entire 15k recordset,
not the final 124... Suggestion
Rewrite the query as
Select <Stuff>
From (SubSquery: Select Stuff
From <Tables>
Where <Here goes filter predicate tha treduces 15k - 124)
Order By <Order by Clause>
Then inner query must process the filter and deliver the 124 records to the
outer part, where the Order By is...
See if that works...
"Conrad Chan" wrote:
> Thanks CBretana,
> I did look into query analyzer. 90% is done on the Sort. The only thing
I
> found may be interested is that the estimated row count is 15,000 compared
> with 124 row count.
> Conrad
> "CBretana" wrote:
>|||No luck. I simply try to do exactly like you suggest. SQL is smart enough
to realize they are the same. (It is too smart to be stupid)
However, for testing purpose, if I put a TOP inside my sub-select it does
return in 2s.
SELECT * FROM (
SELECT TOP 124 * FROM ...
) ORDER BY 1, 4
Conrad
"CBretana" wrote:
> Then you have a filter in the query somewhere, which is reducing the outpu
t
> from 15,000 to 124, and the sort is happening on the entire 15k recordset,
> not the final 124... Suggestion
> Rewrite the query as
> Select <Stuff>
> From (SubSquery: Select Stuff
> From <Tables>
> Where <Here goes filter predicate tha treduces 15k - 124)
> Order By <Order by Clause>
> Then inner query must process the filter and deliver the 124 records to th
e
> outer part, where the Order By is...
> See if that works...
> "Conrad Chan" wrote:
>sql
I have a long runing query took 70s and returns only 124 rows. I found the
problem is that it uses a compute column in the Order By clause. Something
like this
SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
If I took that Order By away, it only takes 2s. (That make me think my C#
client code can sort better than that :P )
Can anyone show me what are the ways I can do to optimize it?
Any thoughts are appreciated.
ConradConrad Chan wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
If that expression is in the SELECT clause you can use
ORDER BY n
where n is the ordinal number of the expression in the SELECT clause.
E.g.:
SELECT col1, col2, (col4 * 0.25) / 100, ...
FROM ...
ORDER BY 3
Will sort the resultset by the value of the 3rd column in the SELECT
clause.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjdSGYechKqOuFEgEQKsQgCggvSDYuQCwIcw
DXSdEtuVA3YD+b4AnixS
BnAeboIAn+Ja2WD/GUp486uA
=1vFd
--END PGP SIGNATURE--|||If you can do it in the client side, then do it.
AMB
"Conrad Chan" wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||I will say only if db really cannot do a better job.
Thanks
Conrad
"Alejandro Mesa" wrote:
> If you can do it in the client side, then do it.
>
> AMB
> "Conrad Chan" wrote:
>|||Unfortunately it doesn't make a difference :<
Conrad
"MGFoster" wrote:
> Conrad Chan wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> If that expression is in the SELECT clause you can use
> ORDER BY n
> where n is the ordinal number of the expression in the SELECT clause.
> E.g.:
> SELECT col1, col2, (col4 * 0.25) / 100, ...
> FROM ...
> ORDER BY 3
> Will sort the resultset by the value of the 3rd column in the SELECT
> clause.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQjdSGYechKqOuFEgEQKsQgCggvSDYuQCwIcw
DXSdEtuVA3YD+b4AnixS
> BnAeboIAn+Ja2WD/GUp486uA
> =1vFd
> --END PGP SIGNATURE--
>|||You can simplify the ORDER BY clause to
ORDER BY COALESCE(Table1.Field1, ''), COALESCE(CAST(Table2.Field2 AS
nvarchar), '''')
or even to
ORDER BY Table1.Field1, Table2.Field2
HTH,
Gert-Jan
Conrad Chan wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||Conrad,
In General it should never take SQL Server 68 seconds to sort 124
records... SOmething else is going on here ... Run the query in Query
Analyzer with ShowPlan ON, and see what step in the showplan is taking that
long...
"Conrad Chan" wrote:
> Hi all,
> I have a long runing query took 70s and returns only 124 rows. I found th
e
> problem is that it uses a compute column in the Order By clause. Somethin
g
> like this
> SELECT ... ORDER BY ISNULL(Table1.Field1, '') + '|' +
> ISNULL(CONVERT(nvarchar, Table2.Field2), '''')
> If I took that Order By away, it only takes 2s. (That make me think my C#
> client code can sort better than that :P )
> Can anyone show me what are the ways I can do to optimize it?
> Any thoughts are appreciated.
> Conrad|||Thanks CBretana,
I did look into query analyzer. 90% is done on the Sort. The only thing I
found may be interested is that the estimated row count is 15,000 compared
with 124 row count.
Conrad
"CBretana" wrote:
> Conrad,
> In General it should never take SQL Server 68 seconds to sort 124
> records... SOmething else is going on here ... Run the query in Query
> Analyzer with ShowPlan ON, and see what step in the showplan is taking tha
t
> long...
>
> "Conrad Chan" wrote:
>|||Then you have a filter in the query somewhere, which is reducing the output
from 15,000 to 124, and the sort is happening on the entire 15k recordset,
not the final 124... Suggestion
Rewrite the query as
Select <Stuff>
From (SubSquery: Select Stuff
From <Tables>
Where <Here goes filter predicate tha treduces 15k - 124)
Order By <Order by Clause>
Then inner query must process the filter and deliver the 124 records to the
outer part, where the Order By is...
See if that works...
"Conrad Chan" wrote:
> Thanks CBretana,
> I did look into query analyzer. 90% is done on the Sort. The only thing
I
> found may be interested is that the estimated row count is 15,000 compared
> with 124 row count.
> Conrad
> "CBretana" wrote:
>|||No luck. I simply try to do exactly like you suggest. SQL is smart enough
to realize they are the same. (It is too smart to be stupid)
However, for testing purpose, if I put a TOP inside my sub-select it does
return in 2s.
SELECT * FROM (
SELECT TOP 124 * FROM ...
) ORDER BY 1, 4
Conrad
"CBretana" wrote:
> Then you have a filter in the query somewhere, which is reducing the outpu
t
> from 15,000 to 124, and the sort is happening on the entire 15k recordset,
> not the final 124... Suggestion
> Rewrite the query as
> Select <Stuff>
> From (SubSquery: Select Stuff
> From <Tables>
> Where <Here goes filter predicate tha treduces 15k - 124)
> Order By <Order by Clause>
> Then inner query must process the filter and deliver the 124 records to th
e
> outer part, where the Order By is...
> See if that works...
> "Conrad Chan" wrote:
>sql
Order by clause help
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
SimonTry:
select top 50
*
from
MyTable
order by
MyCol desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegroups.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:
>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
SimonTry:
select top 50
*
from
MyTable
order by
MyCol desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegroups.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:
>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
Order by clause help
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon
Try:
select top 50
*
from
MyTable
order by
MyCol desc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegrou ps.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon
|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:
>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon
Try:
select top 50
*
from
MyTable
order by
MyCol desc
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegrou ps.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon
|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:
>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP
Order by clause help
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
SimonTry:
select top 50
*
from
MyTable
order by
MyCol desc
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegroups.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:
>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
SimonTry:
select top 50
*
from
MyTable
order by
MyCol desc
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1141139037.380049.4520@.j33g2000cwa.googlegroups.com...
Hi,
I have a table that contains approx 2000 rows where a timestamp is the
primary key. I want to search between two dates and return the results
order by the timestamp in descending order. The problem arises as I
only want to return a maximum of 50 results starting with the oldest.
E.g. if I search between 28/02/06 and 01/02/06 and this contains over
50 results I want to return the first 50 starting from the 01/02/06 but
ordered by descending i.e the newest date at the top. I have tried to
generate the sql without much success. Any help would be much
appreciated.
Thanks in advance
Simon|||On 28 Feb 2006 07:03:57 -0800, accyboy1981 wrote:
>Hi,
>I have a table that contains approx 2000 rows where a timestamp is the
>primary key. I want to search between two dates and return the results
>order by the timestamp in descending order. The problem arises as I
>only want to return a maximum of 50 results starting with the oldest.
>E.g. if I search between 28/02/06 and 01/02/06 and this contains over
>50 results I want to return the first 50 starting from the 01/02/06 but
>ordered by descending i.e the newest date at the top. I have tried to
>generate the sql without much success. Any help would be much
>appreciated.
>Thanks in advance
>Simon
Hi Simon,
If I understand you correctly, then you need someting like
SELECT Col1, Col2, ..., DateStamp
FROM (SELECT TOP 50 Col1, Col2, ..., DateStamp
FROM YourTable
WHERE DateStamp >= '20060201'
AND DateStamp < '20060301'
ORDER BY DateStamp) AS Der
ORDER BY DateStamp DESC
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP
Order By Case Confusion
I can't seem to figure out what is going on here... try this code. It should return 3 rows with no problem as is.
===========
DECLARE
@.SortOn varchar(20)
SET @.SortOn = 'dicount'
SELECT discounttype,
isNull(stor_id,'') stor_id,
isNull(lowqty,0) lowqty,
isNull(highqty,0) highqty,
discount
FROM pubs..discounts
ORDER BY CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN stor_id
WHEN 'lowqty' THEN lowqty
WHEN 'highqty' THEN highqty
WHEN 'dicount' THEN discount
END
===========
Now set @.SortOn = 'discounttype' and you get:
Server: Msg 8114, Level 16, State 5, Line 5
Error converting data type varchar to numeric.
?
Now for more confusion.. when you comment out lines 3,4,and 5 of the CASE statement (WHEN 'lowqty'...'highqty'...'dicount' lines) you once again get 3 rows returned without error.
Anyone able to shed some light on what is goin on?
[MESSAGE EDITED: misspelled discounttype.. sorry]The problem you are having is that you have numeric types mixed in with a varchar data type in your order by clause. SQL is expecting a numeric - look at data type precedence.|||Try this:
DECLARE @.SortOn varchar(20)
SET @.SortOn = 'dicount'
SELECT discounttype,
isNull(stor_id,'') stor_id,
isNull(lowqty,0) lowqty,
isNull(highqty,0) highqty,
discount
FROM pubs..discounts
ORDER BY
CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN CAST(stor_id AS VARCHAR)
WHEN 'lowqty' THEN CAST(lowqty AS VARCHAR)
WHEN 'highqty' THEN CAST(highqty AS VARCHAR)
WHEN 'dicount' THEN CAST(discount AS VARCHAR)
END|||The only problem with that code is that it will sort the numerics as varchars(ascii character comparision) and not as a true numeric. So values 10,9,12 would be sorted as 10,12,9 (ascending) and 9,12,10 (descending).|||The problem you are having is that you have numeric types mixed in with a varchar data type in your order by clause i've looked at that answer several times, and the error message too, and i still don't understand why it would throw an error
but then, the query's kind of weird too
why not stick the whatever-i-want-to-sort-on-this-week into an extra column in the result set and sort on that...SELECT discounttype
, isNull(stor_id,'') stor_id
, isNull(lowqty,0) lowqty
, isNull(highqty,0) highqty
, discount
, CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN stor_id
WHEN 'lowqty' THEN lowqty
WHEN 'highqty' THEN highqty
WHEN 'dicount' THEN discount
END as sortfield
FROM pubs..discounts
ORDER BY sortfieldmaybe that'll throw the same error, i dunno
rudy
http://rudy.ca/|||Originally posted by r937
maybe that'll throw the same error, i dunno
Nice thought but that errors as well.
I have solved my problem by seperating each datatype with an IF clause like so:
IF @.SortOn in (..) BEGIN -- varchars
SELECT ...
FROM ..
ORDER BY CASE @.SortOn
WHEN (varchars... etc..)
END
END ELSE BEGIN -- numerics
SELECT ...
FROM ..
ORDER BY CASE @.SortOn
WHEN (numerics... etc..)
END
END
Kinda messy but it works. Any thoughts to make it a bit more graceful would be appreciated.
Thanks for the replies!
===========
DECLARE
@.SortOn varchar(20)
SET @.SortOn = 'dicount'
SELECT discounttype,
isNull(stor_id,'') stor_id,
isNull(lowqty,0) lowqty,
isNull(highqty,0) highqty,
discount
FROM pubs..discounts
ORDER BY CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN stor_id
WHEN 'lowqty' THEN lowqty
WHEN 'highqty' THEN highqty
WHEN 'dicount' THEN discount
END
===========
Now set @.SortOn = 'discounttype' and you get:
Server: Msg 8114, Level 16, State 5, Line 5
Error converting data type varchar to numeric.
?
Now for more confusion.. when you comment out lines 3,4,and 5 of the CASE statement (WHEN 'lowqty'...'highqty'...'dicount' lines) you once again get 3 rows returned without error.
Anyone able to shed some light on what is goin on?
[MESSAGE EDITED: misspelled discounttype.. sorry]The problem you are having is that you have numeric types mixed in with a varchar data type in your order by clause. SQL is expecting a numeric - look at data type precedence.|||Try this:
DECLARE @.SortOn varchar(20)
SET @.SortOn = 'dicount'
SELECT discounttype,
isNull(stor_id,'') stor_id,
isNull(lowqty,0) lowqty,
isNull(highqty,0) highqty,
discount
FROM pubs..discounts
ORDER BY
CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN CAST(stor_id AS VARCHAR)
WHEN 'lowqty' THEN CAST(lowqty AS VARCHAR)
WHEN 'highqty' THEN CAST(highqty AS VARCHAR)
WHEN 'dicount' THEN CAST(discount AS VARCHAR)
END|||The only problem with that code is that it will sort the numerics as varchars(ascii character comparision) and not as a true numeric. So values 10,9,12 would be sorted as 10,12,9 (ascending) and 9,12,10 (descending).|||The problem you are having is that you have numeric types mixed in with a varchar data type in your order by clause i've looked at that answer several times, and the error message too, and i still don't understand why it would throw an error
but then, the query's kind of weird too
why not stick the whatever-i-want-to-sort-on-this-week into an extra column in the result set and sort on that...SELECT discounttype
, isNull(stor_id,'') stor_id
, isNull(lowqty,0) lowqty
, isNull(highqty,0) highqty
, discount
, CASE @.SortOn
WHEN 'discounttype' THEN discounttype
WHEN 'stor_id' THEN stor_id
WHEN 'lowqty' THEN lowqty
WHEN 'highqty' THEN highqty
WHEN 'dicount' THEN discount
END as sortfield
FROM pubs..discounts
ORDER BY sortfieldmaybe that'll throw the same error, i dunno
rudy
http://rudy.ca/|||Originally posted by r937
maybe that'll throw the same error, i dunno
Nice thought but that errors as well.
I have solved my problem by seperating each datatype with an IF clause like so:
IF @.SortOn in (..) BEGIN -- varchars
SELECT ...
FROM ..
ORDER BY CASE @.SortOn
WHEN (varchars... etc..)
END
END ELSE BEGIN -- numerics
SELECT ...
FROM ..
ORDER BY CASE @.SortOn
WHEN (numerics... etc..)
END
END
Kinda messy but it works. Any thoughts to make it a bit more graceful would be appreciated.
Thanks for the replies!
Wednesday, March 21, 2012
ORDER BY Bug in SQL Server?
I believe I found a Bug in MS SQL Server 8.0.
I have a query with an ORDER BY clause. The resultset is about 10000 rows.
Sometimes the ordering of the result is not correct, but when I restart the
sqlserver-process, at least the first time the ordering is correct, but then
after executeing the same query a few times, th ordering gets corrupt!
Has anybody encountered a simmilar Problem?
Does anybody know a workaround?
Thank a lot.
Andy Rohr.What do you mean by not correct? Can you post some code and sample data to
reproduce the problem (including the DDL)?
Do the columns in the ORDER BY list represent a unique key? If not, remember
that ORDER BY can produce different valid orderings if the columns are not
unique. For example, with the following query:
SELECT A, B
FROM Sometable
ORDER BY A
the result:
(1,1)
(1,2)
is just as valid as:
(1,2)
(1,1)
Also, in SQLServer ORDER BY columns reference aliased columns in the SELECT
list but can also reference base table columns which do not appear in the
SELECT list, a feature which can cause some confusion.
--
David Portas
----
Please reply only to the newsgroup
--|||> Please reply only to the newsgroup
I repiled to microsoft.public.sqlserver.programmingsql
Order By Adding Rows?
Hi All,
Having trouble with the following SQL statement. If there is multiple
ORDER by's it adds rows to the result. The SQL statement below adds
rows to the result, if I remove block_line_no
from the orer by it returns the correct data.
SELECT *
FROM nc_block_data
WHERE (PROGRAM_ID = 2607) OR
(PROGRAM_ID = 2608) OR
(PROGRAM_ID = 2609) OR
(PROGRAM_ID = 2610) OR
(PROGRAM_ID = 2612) OR
(PROGRAM_ID = 2613) OR
(PROGRAM_ID = 2614) OR
(PROGRAM_ID = 2615) OR
(PROGRAM_ID = 2616) OR
(PROGRAM_ID = 2617) OR
(PROGRAM_ID = 2618) OR
(PROGRAM_ID = 2619) OR
(PROGRAM_ID = 2620) OR
(PROGRAM_ID = 2621)
ORDER BY program_id, block_line_noSkip wrote:
> Hi All,
> Having trouble with the following SQL statement. If there is multiple
> ORDER by's it adds rows to the result. The SQL statement below adds
> rows to the result, if I remove block_line_no
> from the orer by it returns the correct data.
>
> SELECT *
> FROM nc_block_data
> WHERE (PROGRAM_ID = 2607) OR
> (PROGRAM_ID = 2608) OR
> (PROGRAM_ID = 2609) OR
> (PROGRAM_ID = 2610) OR
> (PROGRAM_ID = 2612) OR
> (PROGRAM_ID = 2613) OR
> (PROGRAM_ID = 2614) OR
> (PROGRAM_ID = 2615) OR
> (PROGRAM_ID = 2616) OR
> (PROGRAM_ID = 2617) OR
> (PROGRAM_ID = 2618) OR
> (PROGRAM_ID = 2619) OR
> (PROGRAM_ID = 2620) OR
> (PROGRAM_ID = 2621)
> ORDER BY program_id, block_line_no
Can you post some code to reproduce that problem - CREATE TABLE
followed by INSERTs of one or two sample rows. Also tell us what
version, edition and service pack of SQL Server you are using. If your
server is patched and you can't reproduce the problem then maybe you
are looking at some corrupt data or index somewhere.
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
--|||Here is the table structure. I created a test table but was not able to
reproduce the problem. Could it be something with the table structure?
USE [iFrame]
GO
/****** Object: Table [dbo].[Nc_Block_Data] Script Date: 04/11/2006
14:32:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Nc_Block_Data](
[PROGRAM_ID] [int] NOT NULL,
[BLOCK_LINE_NO] [int] NOT NULL,
[PROG_LINE_NO] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NC_CODE] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HOLE_NAME] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NCBLOCK_STATUS] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NCBLOCK_STATUS_DT] [datetime] NULL,
[ERROR_INF] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF|||Not sure why but it looks like it was bad data, just in one section of
the DB. Does this make sense? If I grab a different group of data all
works fine.|||If you can be certain that the data is not corrupt, try reindexing the
suspicious block_line_no column.
ML
http://milambda.blogspot.com/sql
Having trouble with the following SQL statement. If there is multiple
ORDER by's it adds rows to the result. The SQL statement below adds
rows to the result, if I remove block_line_no
from the orer by it returns the correct data.
SELECT *
FROM nc_block_data
WHERE (PROGRAM_ID = 2607) OR
(PROGRAM_ID = 2608) OR
(PROGRAM_ID = 2609) OR
(PROGRAM_ID = 2610) OR
(PROGRAM_ID = 2612) OR
(PROGRAM_ID = 2613) OR
(PROGRAM_ID = 2614) OR
(PROGRAM_ID = 2615) OR
(PROGRAM_ID = 2616) OR
(PROGRAM_ID = 2617) OR
(PROGRAM_ID = 2618) OR
(PROGRAM_ID = 2619) OR
(PROGRAM_ID = 2620) OR
(PROGRAM_ID = 2621)
ORDER BY program_id, block_line_noSkip wrote:
> Hi All,
> Having trouble with the following SQL statement. If there is multiple
> ORDER by's it adds rows to the result. The SQL statement below adds
> rows to the result, if I remove block_line_no
> from the orer by it returns the correct data.
>
> SELECT *
> FROM nc_block_data
> WHERE (PROGRAM_ID = 2607) OR
> (PROGRAM_ID = 2608) OR
> (PROGRAM_ID = 2609) OR
> (PROGRAM_ID = 2610) OR
> (PROGRAM_ID = 2612) OR
> (PROGRAM_ID = 2613) OR
> (PROGRAM_ID = 2614) OR
> (PROGRAM_ID = 2615) OR
> (PROGRAM_ID = 2616) OR
> (PROGRAM_ID = 2617) OR
> (PROGRAM_ID = 2618) OR
> (PROGRAM_ID = 2619) OR
> (PROGRAM_ID = 2620) OR
> (PROGRAM_ID = 2621)
> ORDER BY program_id, block_line_no
Can you post some code to reproduce that problem - CREATE TABLE
followed by INSERTs of one or two sample rows. Also tell us what
version, edition and service pack of SQL Server you are using. If your
server is patched and you can't reproduce the problem then maybe you
are looking at some corrupt data or index somewhere.
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
--|||Here is the table structure. I created a test table but was not able to
reproduce the problem. Could it be something with the table structure?
USE [iFrame]
GO
/****** Object: Table [dbo].[Nc_Block_Data] Script Date: 04/11/2006
14:32:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Nc_Block_Data](
[PROGRAM_ID] [int] NOT NULL,
[BLOCK_LINE_NO] [int] NOT NULL,
[PROG_LINE_NO] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NC_CODE] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HOLE_NAME] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NCBLOCK_STATUS] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NCBLOCK_STATUS_DT] [datetime] NULL,
[ERROR_INF] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF|||Not sure why but it looks like it was bad data, just in one section of
the DB. Does this make sense? If I grab a different group of data all
works fine.|||If you can be certain that the data is not corrupt, try reindexing the
suspicious block_line_no column.
ML
http://milambda.blogspot.com/sql
Monday, March 12, 2012
Oracle table design
Hello,
I have an oracle table from which rows are retrieved every 10 mins. Some other application puts rows into the same table. There is a timestamp field and I am planning to use that inorder to retrieve new rows. I am concerned about transactions. There might be a point where that application inserts a row when I try to retrieve and so, I will miss that row no matter I check the timestamp. Can somebody throw some light on this?
Also, when I retrieve rows and process, there might be a situation where the processing is faulty and therefore, that row has to be fetched again sometime.
I am thinking of using a new table. Please suggest some ideas regarding my design.
P.S
I am very interested in knowing such scenarios and I would like to know how to deal with such situations at enterprise level ( I wonder what would be the design in time critical applications where multiple applications put and retrieve rows on a single table using timestamps).Don't add a new table to your schema. It will only make things more complicated then they should be.
I suggest the following :
1. Add a column to your table, say : process_timestamp date not null default to_date('01.01.1901','DD.MM.YYYY'). In fact, you can assign any default value you want, but don't use NULL.
2. Your program that retrieves the rows should only read those having process_timestamp = to_date('01.01.1901','DD.MM.YYYY')
3. If the execution succeeds, update process_timestamp to sysdate. If the execution fails, leave it unchanged.
Doing so,
1. You don't have to change anything to the programs that are inserting rows into your table. The default value (to column process_timestamp) will be assigned automatically.
2. You don't have to worry about rows that are inserted while you are processing. You will pick them up later on.
3. On failure, you will be able to reprocess the rows as many times as necessary.|||Many thanks for the reply!
I have an oracle table from which rows are retrieved every 10 mins. Some other application puts rows into the same table. There is a timestamp field and I am planning to use that inorder to retrieve new rows. I am concerned about transactions. There might be a point where that application inserts a row when I try to retrieve and so, I will miss that row no matter I check the timestamp. Can somebody throw some light on this?
Also, when I retrieve rows and process, there might be a situation where the processing is faulty and therefore, that row has to be fetched again sometime.
I am thinking of using a new table. Please suggest some ideas regarding my design.
P.S
I am very interested in knowing such scenarios and I would like to know how to deal with such situations at enterprise level ( I wonder what would be the design in time critical applications where multiple applications put and retrieve rows on a single table using timestamps).Don't add a new table to your schema. It will only make things more complicated then they should be.
I suggest the following :
1. Add a column to your table, say : process_timestamp date not null default to_date('01.01.1901','DD.MM.YYYY'). In fact, you can assign any default value you want, but don't use NULL.
2. Your program that retrieves the rows should only read those having process_timestamp = to_date('01.01.1901','DD.MM.YYYY')
3. If the execution succeeds, update process_timestamp to sysdate. If the execution fails, leave it unchanged.
Doing so,
1. You don't have to change anything to the programs that are inserting rows into your table. The default value (to column process_timestamp) will be assigned automatically.
2. You don't have to worry about rows that are inserted while you are processing. You will pick them up later on.
3. On failure, you will be able to reprocess the rows as many times as necessary.|||Many thanks for the reply!
Monday, February 20, 2012
Oracle 9i to SQL 2000 linked Server Problem..
When i execute a Query from an Oracle Linked server i got 99 rows less then
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick
Oracle 9i to SQL 2000 linked Server Problem..
When i execute a Query from an Oracle Linked server i got 99 rows less then
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick
Oracle 9i to SQL 2000 linked Server Problem..
When i execute a Query from an Oracle Linked server i got 99 rows less then
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick
Oracle 9i to SQL 2000 linked Server Problem..
When i execute a Query from an Oracle Linked server i got 99 rows less then
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick
the original data.
when i run The query in oracle i got 999 rows , when i run in SQL using
openquery i got 900 rows.
Any idea..?
"Melih SARICA" <melihsarica@.hotmail.com> wrote in message
news:OWFMJOqtEHA.1720@.TK2MSFTNGP14.phx.gbl...
> When i execute a Query from an Oracle Linked server i got 99 rows less
then
> the original data.
> when i run The query in oracle i got 999 rows , when i run in SQL using
> openquery i got 900 rows.
> Any idea..?
>
>
Does Oracle have a limit on the amount of data to return in a given query?
A max rows, or max bytes?
I'm just guessing here.
When you created the linked server, what did you specify as the Collation
Compatible?
What query did you run? If it was ANSI then you should get the same number
of rows back, if you used T-SQL commands, then you may not get quite what
you expected. Outer joins in Oracle are done differently than outer joins
in T-SQL.
HTH
Rick
Subscribe to:
Posts (Atom)