Bottom line question:
Does it make a difference which table is referenced first following the ON keyword in a FROM clause when creating a join?
--Example 1:
SELECT * FROM TABLE01 t1, JOIN TABLE02 t2
ON t1.ID = t2.ID
--Example 2:
SELECT * FROM TABLE01 t1, JOIN TABLE02 t2
ON t2.ID = t1.ID
Does the sequencing of the tables after the ON keyword make a difference?
Thanks in advance for your help.
Consider this SQL:SELECT my_field FROM my_table WHERE my_field IN ('value2', 'value1','value3')Simple enough, but is there anyway to specify that the result should beordered exactly like the "IN" clause states? So when this recordsetcomes back, I want it like this:my_field------------value2value1value3Possible?Deane
View 5 Replies View Relatedi add new column using alter command but i always found it in the end of table but
i want to add it in particular position between the columns...........
how to do so .............:)
Hello,
I am trying to format a matrix report so that columns appear in a specified order.
An example of what I mean is, I have 3 columns; New, Additional and Old.
When these columns are dynamically generated by RS they are put in alphebitic order. I want them to appear in the order in which I have them above.
The dataset returns a sequence (int) for each of the columns, so 'New' = 1, 'Additional' = 2 and 'Old' = 3. I am ordering on that sequence, but still can't get it to work. These are actually column groupings.
What am I doing wrong? I don't want the column sorted (i.e. data sorted within a row), but the columns to appear in a specific order.
Many thanks
Shawn
Hello,
I am new to SSIS.
I am trying to write a simple package to export data from some SQL 2005 tables and into a flat file.
In my data flow, I am using the OLE-DB data source and then the flat file destination.
This all works fine except that I cant get the package to write the columns out in the order I want. Even when I drive the OLE-DB source by a query, they columns are getting written to the flat file in a different order than I want.
How is SSIS determining what order to write the columns in and, more importantly, how can I change it to do it in the order I want?
Please help if you can. As mentioned I am new to SSIS so please give clear+simple answers.
Thanks
Mgale1
I'm using SQL server 200
Table A has columns CompressedProduct, Tool, Operation
Table B in a differnt database has columns ID, Product, Tool Operation
I cannot edit table A. I can select records from A and insert into B. And I can select only the records that are in both tables.
But I want to be able to select any records that are in table A but not in Table B.
ie. I want to select records from A where the combination of Product, Tool and Operaton does not appear in Table B, even if all 3 on their own do appear.
This code return all the records from A. I need to filter out the records found in Table B.
---------------------------------------------------------------------------------------------------------------------------------
SELECT ID, CompressedProduct, oq.Tool, oq.Operation FROM OPENQUERY (Lisa_Link,
'SELECT DISTINCT CompressedProduct, Tool, Operation FROM tblToolStatus ts
JOIN tblProduct p ON ts.ProductID = p.ProductID
JOIN tblTool t ON ts.ToolID = t.ToolID
JOIN tblOperation o ON ts.OperationID = o.OperationID
WHERE ts.ToolID=66
') oq
LEFT JOIN Family f on oq.CompressedProduct = f.Product and oq.Tool = f.Tool and oq.Operation = f.Operation
I have the following query
SELECT [KPI].*
FROM
OPENQUERY(LINKED_OLAP,'SELECT
HEAD(TAIL(DESCENDANTS
(TAIL([Time].[CalendarMonth].[Year],1), [Time].[CalendarMonth].[Month]),4),3) ON COLUMNS,
([Game].[Game Code].&[1] ,
[Code] ...
The last three columns are dynamically generated because they change during time. Next month they will be different.
I like to introduce aliases for them and to have them in the select as 'TWO_MONTHS_AGO','ONE_MONTH_AGO', 'CURRENT_MONTH'
I wonder if exists something like [KPI].(0), [KPI].(1), and etc.. of the OPENQUERY to get the selected columns by their ordering number...
I have a table and want to filter on 2 columns: (table indexed on c2 and c3)
---------------------------------------------
c1 c2 c3
aaa 1 1/2/2013
bbb 1 2/2/2013
ccc 1 3/2/2013
ddd 2 1/2/2013
--------------------------------------
select * from x where c2=1 and c3=1/2/2013 order by c3 - does not work.
Where clause is wrong, or need special indexes??
Hi Folks,
Is there any way to add a derived column into the where clause.
Example:
Select Name, Date, Procedure#,
(Case When Procedure# in ('1','2','3') then 'Y' else 'N') AS Class
From Procs
Where Class = 'Y'
Thanks,
Ray
I am writing a fairly simple sql, and I would like to write something like
Code Snippet
select
firstname as firstname,
case
when firstname = 'Peter' then 'yes'
else
'no'
end as whatever
from
MyTable
where
whatever = 'yes'
And this should then select out the rows where column number 2 is 'yes'.
It doesn't work, and I have to copy the firstname = 'Peter' into the where clause.
But why?
Right now I have to do something like this and it is time consuming every time I have to query a specific table...
SELECT lots_of_columns
FROM table
WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1')
AND other_query_critiera_here
Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).
I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
my example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
The first select is running fine but due to extra values added to the table the list of manual difined columns must be added manualy each time new values occur.
Is it possible to make the PIVOT's IN clause dynamicly as stated in the second script (it is based on the same table #source) when running it prompts the next error;
Msg 156, Level 15, State 1, Line 315
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 315
Incorrect syntax near ')'.
adding or moving ')' or '(' are not working.......
select *
into #temp
from #source
pivot ( avg(value) for drive in ([C], [D], [E], [F], [G], [H], [T], [U], [V] )) as value
select * from #temp order by .........
versus
select *
into #temp
from #source
pivot ( avg(value) for drive in (select distinct(column) from #source)) as value
select * from #temp order by .....
I have an SP which concatenates 2 columns in a where clause - ie WHERE [Column1] + [Column2] = @var. This, as far as I'm aware, is not going to access any seek on an index on a table.
My task is to create an index on this table and get the SP to access said index.
Aside from combining the two columns into one column on the table, how I can get an SP to access the newly created index when queried by the SP?
One thought of mine is to firstly index Column1 and allow the SP to access this index by inserting the rows into a temp table. Then a search on the temp table to retrieve the records on a search on Column2.
Hi,
I would like to know how i can handle multiple columns returned by a subquery via IN clause in case of sql server 2005. I can do that in oracle by using the following statement:
DELETE FROM TEST1
WHERE (ID, ID1) NOT IN (SELECT ID,ID1 FROM TEST2);
Thanks and Regards
Salil
I have a question in SQL server. For example I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.
Example table:
numez numhx
111 111
111 111
0 111
111 0
111 0
and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0
I am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below
UPDATE s
SET Exception_Ind = 1
OUTPUT s.Master_Id, s.TCK_NR
INTO #temp2
FROM Master_Summary s
INNER JOIN Exception d
ON d.Id = LEFT(s.Id, 8)
AND d.Barcode_Num = s.TCK_NR
WHERE s.Exception_Ind IS NULL
The above code is throwing an error as follows:
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.Master_Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.TCK_NR" could not be bound.
Usually it is better to include the columns in the index that are in where clause, select list and join.I am thinking that the columns in the selected list is better to keep as index columns and the columns that are in the where clause is better to keep in key columns.Where do we use join column is it better to create as main key column or included column.
View 4 Replies View RelatedIn SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).
View 4 Replies View RelatedHelp.
I have a table with a composite key (two primary keys) that is a foreign key to another table. I need to select all records in the primary key table where there are no matches in the foreign key table... I have no idea how to do this..
Here is how to join the tables:
Select A.Key1, A.Key2 From PrimaryKeyTable A, ForiegnKeyTable B Where A.Key1 = B.Key1 and A.Key2 = B.Key2
I need to query all records that do not join, so basically the inverse of this query. How do I do this? Please let me know if this is in the wrong forum or you need any clarifcation.
Just FYI, on a single Primary key column I would just simply do the following:
Select A.Key From PrimaryTable A, ForiegnTable B Where A.Key not in ( Select A.Key From PrimaryTable A, ForignTable B Where A.Key = B.Key)
but how to with a composite key?
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
Is it possible to update the filed used in the inner join
Update t1 set t1.name=t2.name2
From t1 inner join t2 on t1.name = t2.name
Hi all -
Heres the deal,
I have six tables that are loosly related,
four in one join, two in the other....
in the first join there is a column called commentID -
and is based on a join of the four tables, the results of the
query is based on a where clause
and this needs to be tied into the second set of joined
tables (they are joined on a commentID themselves)
I have both the queries and joins working -
The primary query (4 joined tables) needs to do a lookup on the second (2 joined tables) and add its selected value, into the final result set....
thanks a million for the help!!
take care
tony
How do I delete from a joined table....
I want to delete all the records from transaction AND order table where status is > 4
sumfing like:
DELETE ORDERS.*, TRANSACTIONS.*
from ORDERS
INNER JOIN TRANSACTIONS
where ORDERS.order_no = TRANSACTIONS.order_no
and status > 4
but doesnt seem 2 like dat???
THANKS :)
I create a view with 2 joined tables:
with cte as (
select
DataID,
Name,
SubType,
FileType,
MimeType,
VersionID,
Version
from dtree A1, dversdata A2
where A1.dataid=A2.Docid And A1.Subtype='144'
AND
A2.mimetype='application/news-message-id'
update cte set MimeType = 'application/x-outlook-msg', Subtype=749
what I want to do is to update two columns. Both are from different tables and I get an error..
I have a table with almost a million rows, although it's quite slim with just ID, date, userID, JobID etc.
Now I want to the ability to add comments to some (probably less than 1%) of those lines.
The question is whether to create a separate comments table to join to it, or to create a comments field within the existing table? The comments field would obviously default to NULL, so wouldn't bloat the table unnecessarily if I add that field (right?), and would always be selected with the row from that table, so I'm leaning towards the latter alternative.
Any thoughts, words of warning?
Thanks
Mark
I posted Wednesday thinking a SELECT Distinct would solve my problem but it didn't. I have a stored procedure that is used to grab data from 4 tables that I need to join.
The 1st table (Application) holds a job applicant's name and some other data
The 2nd table (Jobs) holds the Job name and test type
The 3rd table (Locations) holds the locations
Then there is a foreign key many to many table (Application_Locations) that holds the applicants UserID and a LocationID. This table may have multiple rows for the same applicant with different locations in each row.
When the procedure is ran I want all the data that I am requesting from the Application table, and all the data that I am requesting from the Jobs table but only the 1st returned result of the Join on the Locations and Application_Locations table. What do I need to do to correct this so that I only display 1 row for each UserID no matter how many locations thay may have applied to. (You will notice that there are some IF statements so only the 2nd and 4th queries in the sproc are the ones that apply )
Here is the SPROC that is currently in place but is displaying a row for each location.
CREATE PROCEDURE sp_AdminListApplicants
@LocationID int,
@FolderID smallint,
@JobID int,
@SortOrder char(1)
AS
IF @JobID <> 9999
BEGIN
IF @LocationID <> 9999
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
AL.LocationID= @LocationID
AND A.FolderID= @FolderID
AND A.JobID = @JobID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
ELSE
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
A.FolderID= @FolderID
AND A.JobID = @JobID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
END
ELSE
BEGIN
IF @LocationID <> 9999
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
AL.LocationID= @LocationID
AND A.FolderID= @FolderID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
ELSE
BEGIN
SELECT
A.UserID,
A.Completed,
A.FolderID,
A.AppDateTimeStart,
A.ResumeFileName,
A.FirstName,
A.LastName,
A.PrescreenScore,
A.JobID,
A.ViewPre,
A.ViewApp,
A.ViewReport,
A.ViewResume,
J.JobTitle,
J.TestType,
L.BranchAbbreviation,
AL.LocationID
FROM
Locations L
INNER JOIN Application_Locations AL ON AL.LocationID = L.LocationID
INNER JOIN Application A ON AL.UserID = A.UserID
INNER JOIN Jobs J ON J.JobID = A.JobID
WHERE
A.FolderID= @FolderID
ORDER BY
CASE
WHEN @SortOrder = '4' THEN A.AppDateTimeStart
END DESC,
CASE
WHEN @SortOrder = '6' THEN A.PreScreenScore
END DESC,
CASE
WHEN @SortOrder = '2' THEN A.LastName
END DESC,
CASE
WHEN @SortOrder = '5' THEN A.PreScreenScore
END ASC,
CASE
WHEN @SortOrder = '3' THEN A.AppDateTimeStart
END ASC,
CASE
WHEN @SortOrder = '1' THEN A.LastName
END ASC
END
END
GO
Miranda
Hi all,Currently our product has a setup that stores information abouttransactions in a transaction table. Additionally, certain transactionspertain to specific people, and extra information is stored in anothertable. So for good or ill, things look like this right now:create table TransactionHistory (TrnID int identity (1,1),TrnDT datetime,--other information about a basic transaction goes here.--All transactions have this infoPrimary Key Clustered (TrnID))Create Index TrnDTIndex on TransactionHistory(TrnDT)create table PersonTransactionHistory (TrnID int,PersonID int,--extended data pertaining only to "person" transactions goes--here. only Person transactions have thisPrimary Key Clustered(TrnID),Foreign Key (TrnID) references TransactionHistory (TrnID))Create Index TrnPersonIDIndex on PersonTransactionHistory(Person)A query about a group of people over a certain date range might fetchinformation like so:select * from TransactionHistory THinner join PersonTransactionHistory PTHon TH.TrnID = PTH.TrnIDwhere PTH.PersonID in some criteriaand TH.TrnDT between some date and some dateIn my experience, this poses a real problem when trying to run queriesthat uses both date and personID criteria. If my guesses are correct thisis because SQL is forced to do one of two things:1 - Use TrnPersonIDIndex to find all transactions which match the personcriteria, then for each do a lookup in the PersonTransactionHistory tofetch the TrnID and subsequently do a lookup of the TrnID in the clusteredindex of the TransactionHistory Table, and finally determine if a giventransaction also matches the date time criteria.2 - Use TrnDTIndex to final all transaction matching the date criteria,and then perform lookups similar to the above, except for personID insteadof datetime.Compounding this is my suspicion (based on performance comparison of whenI specify which indexes to use in the query vs when I let SQL Serverdecide itself) that SQL sometimes chooses a very non optimal course. (Ofcourse, sometimes it chooses a better course than me - the point is I wantit to always be able to pick a good enough course such that I don't haveto bother specifying). Perhaps the table layout is making it difficult forSQL Server to find a good query plan in all cases.Basically I'm trying to determine ways to improve our table design here tomake reporting easier, as this gets painful when running report forlarge groups of people during large date ranges. I see a few options basedon my above hypothesis, and am looking for comments and/or corrections.1 - Add the TrnDT column to the PersonTransactionHistory Table aswell. Then create a foreign key relationship of PersonTransactionHistory(TrnID, TrnDT) references TransactionHistory (TrnID, TrnDT) and createindexes on PersonTransactionHistory with (TrnDT, PersonID) and(PersonID, TrnDT). This seems like it would let SQL Server makemuch more efficient execution plans. However, I am unsure if SQL servercan leverage the FK on TrnDT to use those new indexes if I give it a querylike:select * from TransactionHistory THinner join PersonTransactionHistory PTHon TH.TrnID = PTH.TrnIDwhere PTH.PersonID in some criteriaand TH.TrnDT between some date and some dateThe trick being that SQL server would know that it can use PTH.TrnDT andTH.TrnDT interchangably because of the foreign key (this would support allthe preexisting existing queries that explicitly named TH.TrnDT - any thatdidn't explicitly specify the table would now have ambigious columnnames...)2 - Just coalesce the two tables into one. The original intent was to savespace by not requiring extra columns about Persons for all rows, many ofwhich did not have anything to do with a particular person (for instance acontact point going active). In my experience with our product, the enduser's decisions about archiving and purging have a much bigger impactthan this, so in my opinion efficient querying is more important thanspace. However I'm not sure if this is an elegant solution either. It alsomight require more changes to existing code, although the use of viewsmight help.We also run reports based on other criteria (columns I replaced withcomments above) but none of them are as problematic as the situationabove. However, it seems that if I can understand the best way to solvethis, I will be able to leverage that approach if other types of reportsbecome problematic.Any opinions would be greatly appreciated. Also any references to goodsources regarding table and index design would be helpful as well (onlineor offline references...)thanks,Dave
View 3 Replies View RelatedTwo days of downtime does not make a happy DBA.
In our frustration we dropped database "Search" completely as restoring it from backup was giving us other authorization errors. Fortunately we could rebuild the searches on the fly.
So now our service broker activation procedure tells us:
04/03/2008 17:49:51,spid55s,Unknown,The activated proc [dbo].[MessageProcessor_NameChange] running on queue AMG_Search.dbo.NameChangeQueue output the following: 'The database owner SID recorded in the master database differs from the database owner SID recorded in database 'Search'. You should correct this situation by resetting the owner of database 'Search' using the ALTER AUTHORIZATION statement.'
My username created the database. I am dbo. So what exactly am I supposed to change the owner to?
I try this:
ALTER AUTHORIZATION ON DATABASE::[Search] TO MySelf
Where MySelf is my username. And I am told Msg 15110, Level 16, State 1, "The proposed new database owner is already a user or aliased in the database" So what the heck we'll make SA the owner.
And that works.
I DON'T GET THIS PEOPLE. I created the database I am not allowed to own it anymore? We formatted the server. We reinstalled SQL server from scratch. We recreated all the usernames. Was our mistake restoring the old data from backup? Was I supposed to re-run the DDL and key all the data back in again? Is that the reccomended restore procedure? The users will be thrilled to hear this :-)
My days as an Oracle DBA were nothing like this. Security was just not this troublesome...
So if someone older, wiser, and with more experience would explain the above error message and how it occurs I would appreciate it, because something tells me I'm going to be stuck in this place again...
The joined view is named "dbo.viewExecView" and is like:
SELECT Bank_No, data_center
FROM [ALPHASQL2000].ev_db.dbo.Bank
The new view that joins to the above view is like:
SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)
Note: The data_center column (an int) was recently added to the Bank table in the linked ev_db database and it shows up there. It also shows up in the view "dbo.viewExecView". It does not appear in the new view that joins to "dbo.viewExecView". And when I run the 'new' view, I get an Error Message: Invalid column name 'data_center'.
I've tried to simplify this as much as possible while still including the pertinent information. Any help very much appreciated, I am currently stumped.
Regards,
Joe
I am using SQL Server 2005 to publish joined tables for SQL Mobile subscribers for merge replication and column level tracking.
Using Management Studio I am trying to join tables and specify row filters on the joined tables. I.E. table 1 is joined with table 2. I need to define row filters for table 1 and row filters specific to table 2.
An example would be: Table 1 is a customer table that I filter on a specific customer. Table 2 might be an orders table that I need to join to get the customers orders but I also want to filter for open orders only.
When I specify the row filter for table 2 the join appears to be ignored and I receive the complete table 2 with the row filter applied.
I have searched the online books and the web and I have not run accross an example of using both joins and row filters where the filters are specified for both joined tables.
Is this possible via the Management Studio?
Thanks,
Ron
Hi there!
We are experiencing a problem using order clause in the statement below:
SELECT
person.pkey
, first_name
FROM
person
, private_person
WHERE
fkey_person = person.pkey
AND first_name = 'A'
ORDER BY
first_name
, person.pkey
Where person table contains the following columns:
pkey, name etc
and private_person
pkey, fkey_person (indexed), first_name (indexed)
The output is:
STEP 1
The type of query is SELECT
FROM TABLE
private_person
Nested iteration
Index : i$private_person$first_name
FROM TABLE
person
Nested iteration
Using Clustered Index
pkey first_name
----------- ---------------------
2000512 A
10994 A
2299 A
1097 A
1218 A
5133 A
1329 A
1387 A
1465 A
7532 A
5513 A
1884 A
512 A
591 A
(14 row(s) affected)
STEP 1
The type of query is SETOFF
Why SQL server does not order by pkey? Is there any information about it somewhere? Is it a bug or what?
If we are ordering by fkey_person everything is Ok. Can anybody help?
Thanks a lot!
Kind Regards,
Vladimir
I have a table named as C1_Messages with fields Id, Messages, Dates.
My Id filed looks like this:
1000
1001
1008
1009
1084
1093
1098 etc.
But here I need to make these Ids in a order, means it sould be in a consecutive order. Like
1000
1001
1002
1003
1004
1005 etc.
So is there anyway to do this method in SQL?
Regards
Shaji