December 4, 2007 at 2:50 am
hello my friends,
you have been always helpfully to me.
I need to make a "select distinct" and the data to be ordered by the way they are in the table , not alphabetically as SQL Server do.
Is there a way to stop the automating ordering?
Best regards,
Adrian
December 4, 2007 at 3:51 am
My understanding is, sorting is the most effective way of removing duplicates, as it eliminates need of costly lookups. Could you tell us what is the scenario and goal you would like to achieve? Why do you have duplicates in table?
...and your only reply is slàinte mhath
December 4, 2007 at 4:28 am
If you want the data returned in a specific order, you need to add an order by clause. Data in a table is not considered in any particular order. In relational theory, tables are unordered sets of data
If you don't specify an order by, SQL is under no obligation to return the data in any specific order.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2007 at 4:44 am
I could add the ID field (primary key) but the Distinct clasue will be usefull. Actully I make a bulk import from a xls file and after I need to populate some table with data in the same order as in the file. The code is :
select @openQueryString= 'insert into dbo.MapFile (FormID, VarID, StartCol, EndCol, Punch, QuestionTitle, QuestionText, AnswerText, QuestionType,LevelID)
select * from openrowset
(
''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=' + @fileName + ';HDR=YES;IMEX=1'',
''select * from [field_def$]''
)'
print @openQueryString
exec(@openQueryString)
----------------------------------------------------------
insert into dbo.Questions
(FormID,QuestionTitle, QuestionText, AnswerType,LevelID)
select distinct FormID,QuestionTitle,QuestionText,QuestionType,LevelID
from dbo.MapFile
The "select distinct" is ordering data on the "FormID" field wich is not good to me.
December 4, 2007 at 4:54 am
Alexandru Petre Ionescu (12/4/2007)
insert into dbo.Questions(FormID,QuestionTitle, QuestionText, AnswerType,LevelID)
select distinct FormID,QuestionTitle,QuestionText,QuestionType,LevelID
from dbo.MapFile
The "select distinct" is ordering data on the "FormID" field wich is not good to me.
The order applied on an insert is meaningless. The 'order' that data is stored within the table is up to SQL and depends (partially) on the structure of the table
If you want the data in a specific order when you retrieve it, add an order by on the select that retrieves data from the dbo.Questions table. Don't waste resourcesd ordering an insert when you can't guarentee anything about the order of a returned recordset.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2007 at 5:25 am
Gail is 100% correct. To add to it a bit, if your statement uses an index to retrieve the data, it frequently comes back in index order. The only way to ensure a specific order is to use the ORDER BY clause in the query. No if's and's or but's.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 4, 2007 at 5:32 am
So, add all records (without distinct) and then remove duplicates. There is something not right with the database logic, when it depends on physical order of data.
...and your only reply is slàinte mhath
December 4, 2007 at 5:38 am
Piotr Rodak (12/4/2007)
There is something not right with the database logic, when it depends on physical order of data.
It doesn't. In SQL physical order of data is completely irrelivent. In fact, as I mentioned before, tables are considered unordered sets.
Some query processing operations (including distinct and group by) will order the data to carry out the operation. Still has nothing to do with the order on disk.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2007 at 5:39 am
Gail is right to say that order is meaningless in a SQL table, however order does have meaning in your Excel worksheet. In order to retain this order in SQL try:
1. Add an IDENTITY column to dbo.MapFile. eg MapFileID
2. Add OPTION (MAXDOP 1) to the end of the SELECT statement which inserts into dbo.MapFile.
(This should ensure that only one thread is used to read the data from the spreadsheet, thus insuring that the identity column is assigned in row order.)
Now add a RowOrder column to the Question table and do something like:
INSERT INTO dbo.Questions(FormID,QuestionTitle, QuestionText, AnswerType, LevelID, RowOrder)
SELECT FormID,QuestionTitle,QuestionText, AnswerType,LevelID, MIN(MapFileID)
FROM dbo.MapFile
GROUP BY FormID,QuestionTitle,QuestionText,QuestionType,LevelID
You should now be able to see the questions in the original order using:
SELECT *
FROM dbo.Questions
ORDER BY RowOrder
December 4, 2007 at 5:59 am
Or add a column on the excel spreadsheet that gives the question order, import that column and order by that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 4, 2007 at 6:29 am
That's what I meant Gail. Application should not rely on physical order of rows in a table. Sorry for the confusion 🙂
...and your only reply is slàinte mhath
December 4, 2007 at 8:17 am
Thank you all,
your replies help me to find a solution.
Thank u Ken, I am using now group by and i am ordering by min(ID) and everything is OK.
Best regards to you all,
Adrian
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply