The Order Of Insertion Of Rows Into Destination Is Not Same As The Order Of Incoming Rows

Dec 21, 2006

Hi ,

i am dealing with around 14000 rows which need to be put into the sql destination.,But what i see is that the order of the rows in the desination is not the same as in the source,

However it is same for smaller number of rows.

Please help ...i want the order to be same.

View 4 Replies


ADVERTISEMENT

Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?

May 19, 2015

I never paid much attention to this before but I noticed this today in a new table I was creating.

For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.

I have a date table I setup and I noticed it is NOT respecting the sort order.

I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.

I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.

Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?

View 8 Replies View Related

Rows Insert Out Of Order

Oct 30, 2000

I am currently trying to insert or import some rows into a table and sql server always seems to sort it by one of the columns in a different order that I insert the data. I would appreciate any feedback on this issue. Here is my table structure.

columnA columnB columnC columnD columnE columnF columnG
char char int int int smallint char

it keeps sorting by column F and seperates them by odds and evens. Does any have a clue why this is happening? I am just using these two inserts.

insert into tableNAME values('AA', 'A55', 0, 31, 1, 1, 3)
insert into tableNAME values('AA', 'A55', 0, 31, 1, 2, 2)

These two rows would be seperated by any other rows already contained in the table. If I add more rows. It lumps them by odds and evens.

Thanks

View 1 Replies View Related

Different Rows Order In Result Grid

Sep 20, 2001

Hello!
I have two similar servers (hardware) with
SQL Server 2000 (SP1) on Win2000 (SP2).
(sp_helpsort - default)

When I run the same SELECT statement from Query Analyzer on each machine
I get different rows order in result Grid
(the order of rows stays the same for each machine but different for
machine1 and machine2)

SELECT table1.a,
table1.b,
table1.c,
table1.d

FROM table2,
table1,
table3

WHERE table2.nTestDefnId = 1034
AND table2.nDefnId = table1.nDefnId
AND table3.szClinDiscType = 'X'
AND table2.nDiscId = table3.nDiscId

What is a reason for such behavior and how I can fix it?

Thank you in advance,
Elena.

View 1 Replies View Related

Return Set Ordered By Physical Order Of Rows?

Nov 5, 2012

I would like to do a SELECT query and have the return set ordered by the order which the rows are physically stored in the table.

Specifically, something like:

Select a.*, b.normalizedColumn
FROM table a
JOIN table b
(a.id = b.id)
ORDER BY "Physical order of table a rows"

I believe if there is a clustered index on the table, I can just order by that index and it SHOULD be the physical order of the table.

(I would like to see this as I am generating mock data and would like to verify the "randomness" of the data inserted).

View 6 Replies View Related

Flat File To Table - Rows Out Of Order

Jun 12, 2007

Hi,



I noticed something strange today. I created a pkg that reads a flat file and writes the rows to a table.



In checking the data in the file against what's in the table, I noticed that the rows were inserted in a different order than they are in the file.



All the rows appear to be in the table correctly, but they're just not in the same order as in the file. I've never seen this before. But I checked very carefully, and this is indeed the case.



Is this normal??



Thanks

View 8 Replies View Related

How To Change The Order Of Rows In Datatable Randomly?

Mar 26, 2006

Hello!

I would like randomly change the order of the rows in my table. Is there any way to do that? I also have a question about random generator. Is it possible to get a repeatable sequence of random numbers between 1 and 10 in T-SQL? (for example 2,7,6,5,8,9,3,2,....each state with the same probability). But i need the same sequence every time i run my procedure. I know this is just a pseudo generator. I tried to use function rand([seed]) and change the seed value, but I got some strange results...(floor(rand([seed])*100))

Thanx for any idea,

Z.

View 4 Replies View Related

MS SQL Query, What's The Default Order The Rows Returned Are Sorted By?

Jan 12, 2005

i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?

this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in

View 6 Replies View Related

TSQL : How To Delete Duplicated Rows Except The Top 1 Order By Some Fields?

Apr 15, 2004

the table is like :

ID F1 F2 F3
--- --- --- ---
1 A 1 VR
2 B 2 VR
3 A 3 VF
4 A 2 VF
5 B 1 VF
......

the rules is:
if there are rows with same values of F3 field,then choose the top 1 row order by F1,F2 in the group,and delete other rows.

how can I delete rows with ID in (2 , 3 , 5)?

View 7 Replies View Related

Order Of Rows In The Inserted And Deleted Psuedo Tables

Aug 15, 2007

I have a table that sometimes has modifications to column(s) comprising the primary key [usually "end_date"]. I need to audit changes on this table, and naturally, turned to after triggers.

The problem is that for updates, when the primary key composition changes, I'm not able to relate/join using the primary key - obviously, it no longer matches across INSERTED and DELETED. Now, for a single row update, it's easy to check for updates on PK columns and then deduce what changes were made...

So the real question is: are rows in INSERTED and DELETED always in matching order (1st row in INSERTED corresponds to the 1st row in DELETED...)?



I don't want to put a surrogate key (GUID nor IDENTITY) on the base table if at all possible. INSERT... SELECT from the inserted/deleted tables into a temp table with identity column is fine, and is what I'm currently doing; I would like MVP or product engineer level confirmation that my ordering assumption is correct.

Testing using an identity surrogate key on base table, and selecting from the Ins/del tables, and the temp tables without an order by clause seems to always return in proper order (proper for my purposes). I've tested under SQL 2005 RTM, SP1, SP2, and SP2 "3152".

FYI, I've lost the debate that such auditing is better handled by the application, not the database server...

Aside: why doesn't the ROW_NUMBER() function allow an empty OVER( ORDER BY() ) clause? Will SQL ever expose an internal row_id, at least in the pseudo tables, so we can work around this situation?

Thanks
Mike

View 12 Replies View Related

T-SQL (SS2K8) :: Outer Join Add Non Matching Rows To Each Order Group?

Mar 30, 2015

In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?

if OBJECT_ID('tempdb..#left_table') is not null
drop table #left_table;
if OBJECT_ID('tempdb..#right_table') is not null
drop table #right_table;
create table #left_table

[Code]....

View 2 Replies View Related

Randomly Order Selected Rows From Table - Select Procedure Vs Application

Jun 21, 2012

I need to randomly order the selected rows from my table. Is this better to do on the Application level or in a stored procedure using "orderby NewID()"?

Which is faster? There will be about 100 rows returned with 10 columns.

View 5 Replies View Related

Order Of Rows And Columns Returned When Reading With Excel ODBC Driver

Jun 19, 2007

Hi,



I'm reading values from a named range within an Excel spreadsheet using the Excel ODBC driver. If I ask for all columns within a range, using e.g. select * from 'named range', does the driver ensure that the returned rowset has the same column and row ordering as in the spreadsheet? In other words, if a named range on a spreadsheet is the block of cells:



name age

richard 54

jemima 27



I want to make sure that my returned rowset is not going to be:



age name

jemima 27

richard 54



I know that proper databases do not guarantee the order of returned values (unless you specify it) but since Excel is a fixed view of data I was hoping that a returned rowset of values would retain their spreadsheet ordering.



Thanks,



aionaut



View 1 Replies View Related

To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal

Feb 20, 2008

Hi,

When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.

How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.

Any sort of help would be highly appreciated.

Thanks,

View 2 Replies View Related

To Validate # Of Excel Rows(source) And Sql Server Table(destination) Rows Are Equal

Feb 20, 2008

Hi,

When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.

Any sort of help would be highly appreciated.

Thanks,

View 1 Replies View Related

Select Records By Order Of Insertion???

Jan 3, 2008



I need to create a temp table with an identity column based on the contents of a physical table (CONTACTS) that has no key or unique way to identify which record was inserted first,

I can query CONTACTS with no ORDER clause and everything is in the order it was inserted. However, when I create the temp table with a newid() as ID or IDENTITY(1,1) as ID, the duplicate rows as they existsed in the physical table are not inserted into the temp table in the same order.

Is it possible to select records from a physical table into a temp table in the same order while creating a unique field?

Here is how they exist in the physical table:

CLIENTID CHANGEDATE NEWVALUE

------------ ----------------------- ----------------------------------------------------------------

C0HTRA200005 2007-11-19 21:11:57.000 NULL

C0HTRA200005 2007-11-19 21:11:57.000 NULL

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-21 06:46:17.000 Acquisition

C0HTRA200005 2007-11-22 07:47:56.000 Acquisition

Here is how they exist after being inserted into the temp table using DISTINCT:

ID CLIENTID CHANGEDATE NEWVALUE

----------- ------------ ----------------------- ----------------------------------------------------------------

15 C0HTRA200005 2007-11-19 21:07:20.000 NULL

16 C0HTRA200005 2007-11-19 21:10:52.000 NULL

17 C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

18 C0HTRA200005 2007-11-19 21:11:57.000 NULL

19 C0HTRA200005 2007-11-21 06:46:17.000 Acquisition

20 C0HTRA200005 2007-11-22 07:47:56.000 Acquisition


I've tried this many different ways using variations of ORDER BY/ no ORDER BY.
I've tried creating the temp table first with the IDENTITY field, then insert into it.
I've also tried SELECT INTO syntax while creating the IDENTITY field with the select. I've done this with newid() as well.

Any ideas would be huge.

Thanks much.

View 5 Replies View Related

Capture System.IO Rows From Incoming File And Insert Into Table

Mar 24, 2006

Ok, I'm not quite sure how to approach this one.  This is a VB.NET console app in which I want to capture each row and throw it into a table.  The reason being, they want a report on what was processed...which I'll be able to do easily in Reporting Services 2005 once this crap is in a table where it should be.
1) What should I use to do this, dataset?  I want to use stored procedures also, not inline SQL
Function here takes an incoming file, and splits it up into separate files.  I want to insert each row that is succesfully split
    Public Sub ProcessFiles(ByVal sIncomingfile As String, ByVal sOutputDirectory As String)
        If sIncomingfile <> "" And sOutputDirectory <> "" Then
            Dim f As New Security.Permissions.FileIOPermission(Security.Permissions.PermissionState.None)            f.AllLocalFiles = Security.Permissions.FileIOPermissionAccess.Read
            Dim file As New IO.FileInfo(sIncomingfile)            Dim filefs As IO.FileStream = Nothing            If file.Exists Then                Try                    filefs = New IO.FileStream(file.FullName, IO.FileMode.Open)  'Place: 1                Catch ex As Exception                    SendEmail("Incoming .mnt or .naf Filename Invalid or not found", "Place: 1")                    Application.Exit()                End Try            End If
            Dim reader As New IO.StreamReader(filefs)            Dim counter As Integer = 0
            Dim CurrentFS As IO.FileStream            Dim CurrentWriter As IO.StreamWriter            Dim extension As String = IO.Path.GetExtension(file.FullName)
            If extension = ".mnt" Then                While Not reader.Peek < 0                    Dim Line As String = reader.ReadLine                    If IsNumeric(Line.Substring(0, 1)) Then                        Dim Parts() As String = Line.Split(" "c) ' split row into parts                        If Parts(0).Length = 8 Then ' if first part is 8 then know we hit another header so cut and then write to file                            counter += 1                            If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()                            CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(59, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create)                            CurrentWriter = New IO.StreamWriter(CurrentFS)                        End If
                        If Not CurrentWriter Is Nothing Then                            CurrentWriter.WriteLine(Line)                        End If
                    End If                End While
                If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
                MoveFilesFTP(sOutputDirectory, "mnt")
            ElseIf extension = ".naf" Then                While Not reader.Peek < 0                    Dim Line As String = reader.ReadLine                    If Not IsNumeric(Line.Substring(0, 1)) Then ' if first part is not a number, then we know it's a header so split the file                        counter += 1                        If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()                        CurrentFS = New IO.FileStream(IO.Path.Combine(IO.Path.GetDirectoryName(sOutputDirectory), Line.Substring(6, 4) & "[" & counter.ToString & "]" & Now.ToString("MM-dd-yyyy") & IO.Path.GetExtension(file.FullName)), IO.FileMode.Create)                        CurrentWriter = New IO.StreamWriter(CurrentFS)                    End If
                    If Not CurrentWriter Is Nothing Then                        CurrentWriter.WriteLine(Line)                    End If
                End While
                If Not CurrentWriter Is Nothing Then CurrentWriter.Flush() : CurrentWriter.Close()
                MoveFilesFTP(sOutputDirectory, "naf")            End If        Else            'input file not valid            SendEmail("Incoming .mnt or .naf Filename Invalid", "Place: 1")        End If    End Sub

View 2 Replies View Related

Transact SQL :: Update Incoming Rows Based On Percentage Calculation

Apr 2, 2015

This is on SQL Server 2008. Please find a detailed description and the file of the data, that I am working on.

Requirements:

1.
If
'Channel'
is
not
equal to "Omnibus"
where
the 'Trans Description'is
equal to "Purchase"
and
"Redemption"
for
one purchase and
one redemption that match on 'System'
,
'Account TA Number'
,
'Product Name'
,
'Settled Date'
,
and
where
the 'Trade Amount'
of the purchase and
redemption is
within 5%,
then
display those set of records.

2.
If
deemed wash trades,
allow user to update the purchase and
redemption pair 'Trans Description'
from
"Purchase"
to "Exchange In"
and
'Trans Description'
from
"Redemption"
with
"Exchange out"

System Channel Dealer Name Firm Name Product Cusip Product Name Product Share Class Trade ID Settled Date Account TA Number Trans Description  Trade Amount 

SCHWABPORTAL US - ASG MILLIMAN MILLIMAN 64128K777 Strategic Income Fund A 29806259 30-Jan-15 000BY00F2RW Redemption  $      25,68,458.15

[Code] .....

View 36 Replies View Related

SQL Server 2008 :: Update Incoming Rows Based On Percentage Calculation

Mar 13, 2015

I have below records coming in from source files

ProdName Amount TranType
P1 100 A
P1 100 S
P2 200 A
P2 205 S

In case the ProdName is same, and Amount = or (within +/- 5%) of Amount, I have to update the TranType column as IN/OUT respectively as shown below in the tables.

I am okay with using 2 different tables if needed as in the records comes in one table and then i can reference that table to upload the values in another.

ProdName Amount TranType
P1 100 IN
P1 100 OUT
P2 200 IN
P2 205 OUT

The order of the records coming in can be different order, they need not be subsequent.

This is happening in SQl Server 2008.

View 3 Replies View Related

How Do I Order Columns In My Custom Destination Component?

Nov 17, 2006

Here is the situation. I have created a package that takes 50 columns from a comma delimited flat file. I then validate and clean the data. Next I add two columns that were not in the original source file. These two columns need to be in the 5th and 9th column position when the file is then re-written to a text file. How do i get those two columns to write out in the desired order? Any ideas?



K. Lyles

View 1 Replies View Related

Order Of Columns In Flat File Destination

Oct 23, 2007

I need to create a number of flat files, all with the same layout and sourced from the the same table, but with different criteria.

The first set of (three) flat files file is created out of a simple Conditional Split transformation: If Source Table row number > 40,000 route to File 3; if row number > 20,000, route to file 2, otherwise route to file 1. This gives me 20,000 rows in files 1 & 2 and the remainder in file 3.

I also want to create a fourth flat file by joining the Source Table with a sample table and selecting only those rows where the Customer numbers match. I'm currently doing this in two stages: An Execute SQL Task performs the join and inserts the selected rows into a Destination table (identical layout to source table), and then a simple data flow moves the rows from the Destination table into the fourth flat file.

My problem is that the order of the columns in the first three flat files is different from the fourth file. I've tried creating the fourth flat file with a single data flow using a Merge Join transformation which didn't work because the tables aren't sorted in the correct sequence, and I couldn't get an OLE DB Command transformation to work either.

I'm not sure why the column order of the 4th file should be different seeing as how its contents are sourced from the same Source table, but is there a cunning way of setting this up so that the columns end up in the same order?

View 8 Replies View Related

OLEDB To Flat File Destination - Comes Out In Wrong Order

Jun 8, 2007

Hello,



My OLE DB Source is getting data from the following column types:

ID varchar(50), Name varchar(100), Date datetime, Currency char(3), Cost numeric(30,10)



My OLE DB Source outputs my information in the following order when I click Preview:

ID Name Date Currency Cost



When I connect the OLE DB Sorce to a Flat File Destination, it comes out in the wrong order.When I examine the "line" between them (Data Flow Path Editor) I get:



Currency DT_STR Length: 3

ID DT_STR Lenght: 50

Name DT_STR Lenght: 100

Date DT_DBTIMESTAMP

Cost DT_NUMERIC



What is the easiest way for me to change this so the Flat File Destination will output my data in the same order as the OLE DB Source:

ID Name Date Currency Cost



Thank you very much!

View 6 Replies View Related

Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz

Jan 7, 2007

Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...

SQL Server Express edition

Visual Studios 2005

Jet 4.0 newest upgrade

.Net Framework 2.0 (or should I use 3.0)

VS2005 Security upgrade

Anything else I need for just creating a database for my VS2005 Visual Basic project?

I was trying to use MS Access as my backend db but would like to try SQL Express



Thank you, Mark





View 7 Replies View Related

Conditional Order By - Sort Result Set By Employee Number Ascending Order

Sep 24, 2012

In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception

1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)

2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)

How shall I achieve this?

View 4 Replies View Related

Calculate Total Amount Of Order Details Based On Particular Order

Apr 10, 2014

I have a query that calculate the total amount of order details based on a particular order:

Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID

My question is what if I wanted to create a formula to something like:

UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount

Do I need to create a function for that? Also is it possible to have m y query as a table variable?

View 7 Replies View Related

Default Sort Order - Open Table - Select Without Order By

Mar 27, 2008

Hi!

I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.

The table returns the data in the same order in SQL Manager "Open Table"

So I started to wonder what deterimins the sort order when there is no order by clause ?

I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.

Peace.

/P

View 5 Replies View Related

How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?

Jan 4, 2008

Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.

below is a Stored Procedure that i have wrote in creating a PO:



CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)

AS

BEGIN

INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)

END



SET @POno = @@IDENTITY

RETURN


However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?


CREATE TRIGGER trgInsertPOItem

ON PurchaseOrderItem

FOR INSERT

AS

BEGIN


'What do i entered???'
END

RETURN


help is needed asap! thanks!

View 14 Replies View Related

Find Order By Date Range Or Order Id

May 8, 2007

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL
SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))">
 but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!
Thanks
Jez

View 4 Replies View Related

Default Sort Order When Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go


insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:

Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 14 Replies View Related

Recordset's Order And Database's Physical Order?

Jul 20, 2005

Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!

View 2 Replies View Related

Default Sort Order When The Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 5 Replies View Related

Order By Clause In View Doesn't Order.

May 18, 2006

I have created view by jaoining two table and have order by clause.

The sql generated is as follows

SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth



If I run sql the results are displayed in proper order but the view only order by first item in order by clause.

Has somebody experience same thing? How to fix this issue?

Thanks,

View 16 Replies View Related

Inconsistent Sort Order Using ORDER BY Clause

Mar 19, 2007

I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.

Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS

for example,

create table test_sort
( description varchar(75) );

insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');

then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));

select
*
from
test_sort
order by
cast( description as nvarchar(75));

Resultset1
----------
Non-A
Non-O
Noni
Nons

Resultset2
----------
Non-A
Noni
Non-O
Nons


Any ideas?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved