How To Know The Order Of Entries In A Table
For example some data has entered into a table in a random manner i.e the pk filed value is not in a serial fashion.Is there any table or index that holds the entries of rows into a particular table as entered .
i.e
'some_table' has data like this
3,entry3
2,entry2
4,entry4
1,entry1
I want some DB table or Index that holds data like this about above 'some_table'
row_id .... .... ....
1
2
3
4
here 1 refers to entry of the first column in 'some_table' i.e 3,entry3
and so on...
View Complete Forum Thread with Replies
Related Forum Messages:
Returning First Entries In Reverse Order
Hi, I need to get first entries orderd by datetime asc, but I need the newest of this block returned first. Imagine to have some entries: 08:00 ... 09:00 ... 10:00 ... .... ... 15:00 16:00 17:00 .... I want to get the first 3 but the highest in time first. Like this: 10:00 ... 09:00 ... 08.00 ... Any idea? Thanks a lot Francesco DaitarnGe
View Replies !
Default Sort Order - Open Table - Select Without Order By
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 Replies !
Getting Random Table Entries
Hi,I have a form that should show 2 pictures based on table entries.I want those 2 pictures to be randomly selected based on a database table. So, my table has all the entries, and I want to pull out a random entry that has been approved to display it.Can someone help me with the sql query?I can do SELECT VoteId FROM tblVotes WHERE Approved=True..But how do I make selection a random one that changes every time the user gets another entry?
View Replies !
Duplicate Entries In The Resulting Table
Hi! I am joining 3 tables in SQL , I am getting the results I want exept it's duplicated. So the resultinmg table fom my stored procedure has 3 rows that have the same bulletin. How do I filter the storedprocedure to output only the rows that don't have duplicate entries for the column 'Bulletin' Thanks. Here is my stored procedure:PROCEDURE [dbo].[spGetCompBulletins] @Userid uniqueidentifier OUTPUT,@DisplayName varchar(200) AS SELECT * FROM dbo.UserProfile INNER JOIN dbo.bulletins ON dbo.UserProfile.UserId = dbo.bulletins.Userid INNER JOINdbo.Associations ON dbo.Associations.BusinessID = dbo.bulletins.Userid WHERE UserProfile.DisplayName=@DisplayName and Userprofile.Userid = @Userid ORDER BY Bulletins.Bulletin_Date Return
View Replies !
Prevent Duplicate Entries In A Table
I have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember. The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID. Here is a sample of some data: User1 Group1 User1 Group2 User2 Group2 User3 Group1 User3 Group3 Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example: User1 Group1 User2 Group2 User1 Group1 I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome. Is there a simpler way to prevent duplicate entries in a table using sql? Thanks a lot, Chris
View Replies !
Truncating Duplicate Entries In A Table
Hi, I have a table with no primary key and i just want to see all the duplicate entries on the basis of two columns. Can anyone suggest me how should i go about it. Can anyone provide me the syntax for the same? I have only 1 table say ISSR_TBL and two columns using which i want to delete the duplicate ones. i.e. MIN and MAX. Please help me out...
View Replies !
Periodic Removal Row Entries In A Data Table?
Hi everyone, I am using this temporary data table which gets cluttered after certain time (table is used for registering data waiting for email confirmation). Is there a possibility to empty a data table automatically every day (at a certain moment)? Kind regards,Maxime
View Replies !
Resolving Duplicates Entries In Table Among 10 Databases
HiOur product uses MS-SQL Server 2000. One of our customer has 10installations with each installation stroring data in its own database.Now the customer wants to consolidate these databases into one and wealready have plan for that by consolidating one DB at a time. But firstthey want to find how many unique or duplicate entries they have acrossall the 10 databasesAssumptions:1. All the databases reside on the same server. (This is just anassumption, not the real environment at customer site)2. Databases can not be merged before it is found how many unique orduplicate rows exist.Table under consideration:Message(HashID PK,....)# of rows in Message table in each of databases: 1 MillionHere is my question: How can I find how many unique or duplicateentries they have across all the 10 databases. I easily find uniquerows for two databases with a query like this:SELECT COUNT(A.HasID) FROM db1.dbo.Message A LEFT OUTER JOIN ONdb2.dbo.Message B ON A.HashID = B.HashID WHERE B.HashID IS NULLHow can I do this for 10 databases. This will require factorial of 10queries to solve this problem.I will appreciate if someone can provide hint on this.RegardsAK
View Replies !
How To Force Unique Entries In A Linking Table?
I have a table 'Group2Operation' that stores many to many relationsbetween the 'Group' table and the 'Operation' table (each group is haspermission to perform one or more of the available operations)PROBLEM=======I need to prevent duplicate entries being created. e.g. lets say thatin the 'Group2Operation' table a record links the 'editor' group tothe 'publish' operation. Should I prevent an administrator creating aduplicate of that record? (Otherwise deleting that permission willhave to be done twice or more for it to be effective)SOLUTION?=========So far I've done this with a trigger:CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2OperationFOR INSERT, UPDATEAS UPDATE Group2OperationSET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTagThe 'NoDuplicate' unique index column in the Group2Operation tablestores a concatenation of the unique group and operation identifiers.So when an attempt is made to create a record, the trigger is fired.If there is a duplicate, this will mean a duplicate entry in the'NoDuplicate' column. As a result, the INSERT or UPDATE will fail andthe duplication will be prevented.WHAT DO YOU THINK?==================What do you think? Am I going about this in the right way? Is atrigger a good way to do this or should I rely on application logic toprevent duplicates?Any help appreciated by this db novice.John Grist
View Replies !
Writting Trigger Or Procedure To Delete Duplicate Entries In A Table?
I am using Sql Server 2000. I have a customer table with fields - CustId, Name, Address, City, StdCode, Phone. I used to insert entries in this table from an excel file. One excel file will contain thousands of customer. In this table combination of StdCode and Phone should not be repeated. If I do it in my VB.Net coding.then application gets drastically slow. So I want to write a procedure or trigger for this. Here what I will do, I will send all records into database then this trigger or procedure will check for any existing entry of combination of StdCode and phone. If entry exists then this will delete new entry or will not allow this new entry. Is this possible to do using Trigger or stored procedure?
View Replies !
Retrieving Data From Table With 7 Million Entries Takes Time
Can anyone help me on this... when i select data from table using select statement it takes huge amount of time....The table contains 7 million entries and when i select by mentioning a criteria it takes around 45 secs..The system has 4GB RAM and Dual Processing CPU. The select statement does not contain any grouping and all.. Will it take this much time to retrieve data.?. The table does include an indexed field, So can anyone help me on the different things i can do to make the retrieval faster? Andy
View Replies !
&"Save&" DELETED Entries To New Table
Hi all I would like to know if its possible to "Save" records when they get deleted. For example: I have a table, tblUsers, with coulmns, UserID, Name, Surname, etc... In VWD I've created a GridView which shows everything on a webpage. I've also added a confirm return('Are you sure you want to delete the user?') option in OnClientClick field. What i want to achieve is, have some sort of log file, or log table if you want to call it that, of which users has been deleted by the end user. So, in later stages, i can see who deleted who, when, where, etc... - by building a report or view. All this should go to a seperate database or seperate table, it doesnt really matter. My delete query:DELETE FROM [tblUsers] WHERE [UserID] = @UserID
View Replies !
Regarding Table Order
Greetings,I have an application that need to get all the userdefined child tables first before their parents.I wrote a query, given in this newsgroup only, as belowSELECT o.nameFROM sysobjects oWHERE o.type='U'ORDER BY case WHEN exists ( SELECT *FROM sysforeignkeys fWHERE o.id = f.fkeyid )THEN 1ELSE 0end, o.namegoWhen i try to truncate the first table of the list, it still tells methat tha table is being referenced by foreign key in another table. Mymain job is to truncate all the user defined tables before loading datainto them.Is there something wrong in the query? Or if someone can tell me abetter approach.Any help will be appreciated.TIA
View Replies !
Table Order
I have a database with 200+ tables. How can I get table order (query) from the system tables so that I know which table I should insert data first. Thanks,
View Replies !
FROM Table Order?
I noticed that some queries against an mssql db require the tables in the FROM part of the statement to be in a particular order. Does anyone know why? For example SELECT * FROM table1, table2, table3 WHERE <blah> May throw an error (Unknown table table3 [I can't remember the exact verbiage of the error]), while simply rearranging the table order to: SELECT * FROM table3, table1, table2 WHERE <blah> will work. It seems like the error has something to do with how mssql handles the joins of the tables, but I can't seem to find any documentation about it.
View Replies !
How To Order This Table.
I have a table merchant contractbr date a 2333 1/1/2005 a A34 3/12/2006 a R78 2/1/2005 .. b b b c c c ..... different merchant has different number of contracts. I want to order the contracts for merchants according to the date. the result table should look like: merchant contractbr date order a 2333 1/1/2005 1 a R78 2/1/2005 2 a A34 3/12/2006 3 .. b 1 b 2 b 3 c 1 c 2 c 3 c 4 c 5 ..... Thanks
View Replies !
Table Order By Clause
When I say to sort on a datetime field on descending order, the date is sorted. However, the time difference is not reflected in the results. Any way, we can fix it. i.e. If I have two records with the same dates but different times, the sorting order is not considering the time.
View Replies !
Replication: Table Order
SQL Trasnsactional Replication. I've set up a transactional replication, but I need to specify in which order the tables are to be replicated (tables with foreign keys last). How/where do set table order ? /Carl
View Replies !
Table Execution Order
I have nested lists and I want to set a global value in my custom code AFTER a specific table footer row. Does anybody know in what order the table elements are rendered? I have tried adding my piece of code into a group value, the hidden property, the color property, and sending it as a parameter to a subreport, but it still sets that variable first before rendering the table footer row that I want to display before I set that variable. I have been pulling my hair out trying to do this one! Help! BJ
View Replies !
How To Use Order By In Pivot Table ?
heed help i have a PROCEDURE that generate PIVOT table how to use ORDER BY in this part how to do this ? ORDER BY unit desc, Fname Code Snippet SELECT p.ID,p.new_unit,p.mhlka_id,p.mhlka, p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11], p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21], p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30] FROM ( SELECT ID,new_unit,mhlka_id,mhlka, DATEPART(DAY, Date) AS theDay, ShiftID FROM v_Employee WHERE Date >= @BaseDate AND Date < DATEADD(MONTH, 1, @BaseDate) ORDER BY unit desc, Fname ) AS y PIVOT ( min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30]) ) AS p END the all code DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT) DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP @BaseDate SMALLDATETIME, @NumDays TINYINT SELECT @WantedDate = '20080401', -- User supplied parameter value @BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'), @NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate)) IF @NumDays = 28 BEGIN SELECT p.ID, p.[1], p.[2], p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11], p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21], p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28] FROM ( SELECT ID, DATEPART(DAY, Date) AS theDay, ShiftID FROM v_Employee WHERE Date >= @BaseDate AND Date < DATEADD(MONTH, 1, @BaseDate) ) AS y PIVOT ( COUNT(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28]) ) AS p END ELSE IF @Numdays = 30 BEGIN SELECT p.ID,p.new_unit,p.mhlka_id,p.mhlka, p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11], p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21], p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30] FROM ( SELECT ID,new_unit,mhlka_id,mhlka, DATEPART(DAY, Date) AS theDay, ShiftID FROM v_Employee WHERE Date >= @BaseDate AND Date < DATEADD(MONTH, 1, @BaseDate) ) AS y PIVOT ( min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30]) ) AS p END
View Replies !
Taking Qry And Then Having It Order By In A Table
I have one query with 3 statments in it, which then creates a table called HCSReturnFile. My problem is that I have an order by in my query, but when I go to create a table that puts all 2 statements into 1, it does not do the order by. What am I doing wrong? Below is my Query. USE [Impact_PROD] GO /****** Object: StoredProcedure [dbo].[p_GenerateHCSReturnFileUPDATE] Script Date: 01/18/2008 14:20:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:Wendy & Mike -- Create date: 11/08/2007 ---Create Date to Production - 12-06-2007 -- Description:This was a touch one. ---This report drop both Queries into a tabled called HCSRetrunFile -- ============================================= ALTER PROCEDURE [dbo].[p_GenerateHCSReturnFileUPDATE] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF OBJECT_ID('IMPACT_PROD..HCSReturnFile') IS NOT NULL DROP TABLE HCSReturnFile CREATE TABLE [dbo].[HCSReturnFile]( [EventNumber] [varchar](50) NULL, [ClaimNumber1] [varchar](50) NOT NULL, [Resolution] [varchar](50) NULL, [Resolution2] [varchar](50) NULL, [ProviderType] [varchar](2) NULL, [Negotiation] [varchar](50) NULL, [NEGOYESORNO] [varchar](50) NULL, [ProviderID] [varchar](50) NOT NULL, [HCSAuthorizationID] [varchar](20) NULL, [PROLASTNAME] [varchar](20) NULL, [proFirstName] [varchar](15) NULL, [ProOffice] [varchar](35) NULL, [TOTALBILLEDAMT] [varchar](50) NOT NULL, [SAVINGS] [varchar](50) NOT NULL, [OONNEGO] [varchar](50) NOT NULL, [CLM_ATT1] [varchar](50) NULL, [CLM_ATT2] [varchar](50) NULL, [CLM_ATT3] [varchar](50) NULL, [CLM_ATT4] [varchar](50) NULL, [CLM_ATT5] [varchar](50) NULL, [NoteData] [varchar](8000) NULL) INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile] ([EventNumber] ,[ClaimNumber1] ,[Resolution] ,[Resolution2] ,[ProviderType] ,[Negotiation] ,[NEGOYESORNO] ,[ProviderID] ,[HCSAuthorizationID] ,[PROLASTNAME] ,[proFirstName] ,[ProOffice] ,[TOTALBILLEDAMT] ,[SAVINGS] ,[OONNEGO] ,[CLM_ATT1] ,[CLM_ATT2] ,[CLM_ATT3] ,[CLM_ATT4] ,[CLM_ATT5] ,[NoteData]) SELECT Distinct --n.note_sys, --c.CLM_id1 AS 'ClaimNumber', e.EVE_id1 AS 'EventNumber', ClaimNumber1 = '', --e.EVE_clm As 'EventEventTable', --c.CLM_elrc AS 'EventClaimTable', e.eve_resl1 as 'Resolution', p.EVEP_RESL1 as 'Resolution2', p.evep_reas as 'ProviderType', p.evep_nego as 'Negotiation', --e.EVE_STAT AS 'STATUS', e.EVE_SW01 AS 'NEGOYESORNO', ProviderID = '', e.EVE_EXNO AS 'HCSAuthorizationID', --p.EVEP_LNAME AS 'PROVIDERLASTNAME', --p.EVEP_FNAME AS 'PROVIDERFIRSTNAME', p.EVEP_LNAME AS PROLASTNAME, p.evep_fname AS proFirstName, p.evep_offic AS ProOffice, --c.CLM_EDID AS 'LOCKDATE', TOTALBILLEDAMT = '', SAVINGS = '', OONNEGO = '', c.CLM_ATT1, c.CLM_ATT2, c.CLM_ATT3, c.CLM_ATT4, c.CLM_ATT5, n.NoteData FROM dbo.EVE e RIGHT JOIN dbo.clm c ON e.EVE_id1 = c.clm_id1 RIGHT JOIN dbo.EVEP p ON e.eve_id1 = p.EVEP_id1 JOIN dbo.notes2 n ON n.eve_id1 = e.EVE_id1 --JOIN evep p2 --ON p.evep_id1 = p2.evep_id1 Where e.eve_resl1 = 'CL'and p.EVEP_RESL1 <> 'NG' and p.evep_reas <>'FA'--and ---means closes Events --p2.evep_nego = 'Y' and --((LTRIM(p.evep_id2) <> '0002' AND p.evep_id2 <> '0001' AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1) --e.EVE_STAT = 'CL'and --e.EVE_id1 IN ('00101965','00102080','00102084','00101962','00101963') --and Order by e.eve_resl1,p.EVEP_RESL1 INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile] ([EventNumber] ,[ClaimNumber1] ,[Resolution] ,[Resolution2] ,[ProviderType] ,[Negotiation] ,[NEGOYESORNO] ,[ProviderID] ,[HCSAuthorizationID] ,[PROLASTNAME] ,[proFirstName] ,[ProOffice] ,[TOTALBILLEDAMT] ,[SAVINGS] ,[OONNEGO] ,[CLM_ATT1] ,[CLM_ATT2] ,[CLM_ATT3] ,[CLM_ATT4] ,[CLM_ATT5] ,[NoteData]) SELECT Distinct --n.note_sys, --c.CLM_id1 AS 'ClaimNumber', e.EVE_id1 AS 'EventNumber', ClaimNumber1 = '', --e.EVE_clm As 'EventEventTable', --c.CLM_elrc AS 'EventClaimTable', e.eve_resl1 as 'Resolution', p.EVEP_RESL1 as 'Resolution2', p.evep_reas as 'ProviderType', p.evep_nego as 'Negotiation', --e.EVE_STAT AS 'STATUS', e.EVE_SW01 AS 'NEGOYESORNO', ProviderID = '', e.EVE_EXNO AS 'HCSAuthorizationID', --p.EVEP_LNAME AS 'PROVIDERLASTNAME', --p.EVEP_FNAME AS 'PROVIDERFIRSTNAME', p.EVEP_LNAME AS PROLASTNAME, p.evep_fname AS proFirstName, p.evep_offic AS ProOffice, --c.CLM_EDID AS 'LOCKDATE', TOTALBILLEDAMT = '', SAVINGS = '', OONNEGO = '', c.CLM_ATT1, c.CLM_ATT2, c.CLM_ATT3, c.CLM_ATT4, c.CLM_ATT5, n.NoteData FROM dbo.EVE e RIGHT JOIN dbo.clm c ON e.EVE_id1 = c.clm_id1 RIGHT JOIN dbo.EVEP p ON e.eve_id1 = p.EVEP_id1 JOIN dbo.notes2 n ON n.eve_id1 = e.EVE_id1 --JOIN evep p2 --ON p.evep_id1 = p2.evep_id1 Where ((e.eve_resl1 = '' and p.evep_reas = 'HO')or (e.eve_resl1 = '' and p.evep_reas = 'PH') or (e.eve_resl1 = '' and p.evep_reas = 'AN')) Order by e.eve_resl1,p.EVEP_RESL1 --and ---means Open Events will not have claims attached to them. we just need the fields in this report. --p2.evep_nego = 'Y' and --((LTRIM(p.evep_id2) <> '0002' AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1) INSERT INTO [IMPACT_PROD].[dbo].[HCSReturnFile] ([EventNumber] ,[ClaimNumber1] ,[Resolution] ,[Resolution2] ,[ProviderType] ,[Negotiation] ,[NEGOYESORNO] ,[ProviderID] ,[HCSAuthorizationID] ,[PROLASTNAME] ,[proFirstName] ,[ProOffice] ,[TOTALBILLEDAMT] ,[SAVINGS] ,[OONNEGO] ,[CLM_ATT1] ,[CLM_ATT2] ,[CLM_ATT3] ,[CLM_ATT4] ,[CLM_ATT5] ,[NoteData]) SELECT Distinct c.CLM_elrc AS 'EventNumber', CASE c.clm_ips WHEN 'C' THEN c.clm_pclm ELSE c.clm_id1 END as "claimnumber1", --ClmClaims."CLAIMNUMBER", --c.CLM_PCLM as "CLAIMNUMBER", --n.note_sys, --c.clm_id1, --e.EVE_clm As 'EventEventTable', e.eve_resl1 as 'Resolution', p.EVEP_RESL1 as 'Resolution2', p.evep_reas as 'ProviderType', p.evep_nego as 'Negotiation', --e.EVE_STAT AS 'STATUS', e.EVE_SW01 AS 'NEGOYESORNO', c.CLM_5 AS 'ProviderID', e.EVE_EXNO AS 'HCSAuthorizationID', --p.EVEP_LNAME AS 'PROVIDERLASTNAME', --p.EVEP_FNAME AS 'PROVIDERFIRSTNAME', --p.EVEP_OFFIC AS 'PROVIDEROFFICE', p.evep_lname AS ProLastName, p.evep_fname AS proFirstName, p.evep_offic AS ProOffice, c.CLM_MCHG AS 'TOTALBILLEDAMT', c.CLM_SPPO AS 'SAVINGS', c.CLM_55d AS 'OONNEGO', c.CLM_ATT1, c.CLM_ATT2, c.CLM_ATT3, c.CLM_ATT4, c.CLM_ATT5, n.NoteData FROM dbo.EVE e Right Join dbo.clm c ON e.EVE_id1 = c.clm_elrc Right join dbo.EVEP p ON e.eve_id1 = p.EVEP_id1 --JOIN evep p2 --ON p.evep_id1 = p2.evep_id1 JOIN dbo.notes2 n ON n.eve_id1 = e.EVE_id1 Where p.EVEP_RESL1 = 'NG'and clm_adjto = '' ---"NG"This report is going to be changed to the negoitated report the field is going to be "NG" --e.eve_resl1 = 'NG'and --p2.evep_nego = 'Y' and --((LTRIM(p.evep_id2) <> '0002' AND p.evep_id2 <> '0001'AND p2.evep_id2 <> '0001') OR (SELECT COUNT(*) FROM evep WhERE evep_id1 = p.evep_id1) = 1) Order by e.eve_resl1,p.EVEP_RESL1 END
View Replies !
Table Order When Inserting Data
I have to import data into a empty database, that has many tables.some tables have to be inserted first than others due to the foreignkeys.How do I find out the order of the tables that I have to insert datainto?Thanks in advance!Sam
View Replies !
Changing Column Order In A Table
This subject has been posted several times, but I haven't seen a goodanswer.Problem:I want to change the order of the columns in a table using T-SQL only.Explanation:After running your code, I want to see the following table...CREATE TABLE [dbo].[TableName] ([First_Column] [int] NULL ,[Second_Column] [varchar] (20) NULL) ON [PRIMARY]look like this...CREATE TABLE [dbo].[TableName] ([Second_Column] [varchar] (20) NULL ,[First_Column] [int] NULL) ON [PRIMARY]Limitations:Don't post if your post would fall in the following categories:1. If you don't think it can be done2. If you think Enterprise Manager is the only way to do this3. If you think I should just change the order of my Selectstatements4. If you want to state that order column doesn't matter in arelational database5. If you want to ask me why I want to do thisWish:Hopefully the answer WON'T involve creating a brand new table, movingthe data from old to new, dropping the old table, then renaming thenew table to the old name. Yes, I can do that. The table I'm workingwith is extremely huge -- I don't want to do the data juggling.Thanks in advance!
View Replies !
Alter Table And Column Order
Is it possible to add a column to a table using the "alter table"statement and specify where in the sequence of columns the new columnsits. If not is there any way to alter the order of columns using TSQLrather than Enterprise Manager / Design Table.TIALaurence Breeze
View Replies !
How To Keep Original Order When Querying A Table?
Hello, everyone: I have a table like: ColName b b b d d d a a c c c I use DISTINCT to filter duplicated row. I want to get the return by original order like: b d a c However, SQL Server re-order it if using DISTINCT and return like: a b c d Can any one have the idea to handle that? Thanks ZYT
View Replies !
Determine Table Load Order
Does anyone have a script that analyzes primary and foreign key relationships of tables and produces a suggested load order based upon dependancies? Thanks, Fred.
View Replies !
ORDER BY (field In Foreign Table)
Newbie question. Can someone show me an SQL statement that sorts the results of a query by a field in a different table? CREATE TABLE `Table1` ( `table1_id` INTEGER AUTO_INCREMENT , `table1_name` VARCHAR(255), PRIMARY KEY (`table1_id`) ) CREATE TABLE `Table2` ( `table2_id` INTEGER AUTO_INCREMENT , `table2_name` VARCHAR(255), `table2_table1` INTEGER, PRIMARY KEY (`table2_id`) ) ALTER TABLE `Table2` ADD FOREIGN KEY (`table2_table1`) REFERENCES `Table1`(`table1_id`); What I'd like is something like: SELECT * FROM Table2 ORDER BY "Table1(table2_table1).tabel1_name",table2_name It's the section in double quotes that I can't figure out how to compose. As an example, if Table 1 has 1, A 2, B 3, C and Table 2 has 1, a, 2 2, b, 1 3, c, 2 then I'd like the sort to return 2, b, 1 1, a, 2 3, c, 2 TIA, Stephen
View Replies !
Table Order In Clustered Index?
I have a table "Client" that has two columns: "ClientID" and "ProductID". I created on clustered index on ClientID and when I opened the table in the management studio, I saw the table was in the order of ClientID. Then I added another non-clustered index on ProductID. When I open the table again, it is in the order of ProductID. Shouldn't the table always be in the order of clustered index? Non-clustered index should be a structure outside of the table itself? Did I do anything wrong? Thanks for any hint.
View Replies !
I Wanna Query A Purchase Order Table!...but Can Not:(
i use microaccess create table, there is a filed call"Complete_PO", value"yes/no" i wrote following statement to select it, but at runtime, there is warning message"...constraint...one or more row violating non-unique and so so..." how to solve it SqlSelectCommand2.CommandText = "SELECT Complete_PO FROM [PURCHASE ORDER] WHERE [PO_No] Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No,4) desc" PoNum_SqlDataAdapter.Fill(PO_DataSet1) TextBox1.Text = PO_DataSet1.Tables("PURCHASE ORDER").Rows(0).Item("Complete_PO").ToString()
View Replies !
Need A Quick Hand Using ORDER BY With Two Fields In The Same Table.
I have a problem with ordering and I am hoping that someone is able to help. In my table I have two fields, "requestdate" and "sentdate", and when I display the records I would like to sort by BOTH fields. I want to do this so that the full query is in order by date. I tried: ORDER BY requestdate, sentdate DESC But obviously all that does is order by requestdate (which is NULL or a date) and than it will order by sentdate. Can somebody tell me how to order by both as if they were the same field? Thanks!
View Replies !
Hash Table (#) Order By Problem With More Records
We have one single hash (#) table, in which we insert data processingpriority wise (after calculating priority).for. e.g.Company Product Priority Prod. QtyProd_Plan_DateC1 P11100C1 P22 50C1 P33 30C2 P11200C2 P42 40C2 P53 10There is a problem when accessing data for usage priority wise.Problem is as follows:We want to plan production date as per group (company) sorted order andpriority wise.==>With less data, it works fine.==>But when there are more records for e.g. 100000 or more , it changesthe logical order of dataSo plan date calculation gets effected.==Although I have solved this problem with putting identity column andchecking in where condition.But, I want to know why this problem is coming.If anybody have come across this similar problem, please let me knowthe reason and your solution.IS IT SQL SERVER PROBLEM?Thanks & Regards,T.S.Negi
View Replies !
Incorrect Order Result Set When Join Table
Hi all, I faced a problem, I have two tables - part and partmaster part : part_no, part_qty (no key) partmaster : part_no, part_description (primary key : part_no ) I want to select table part.* and partmaster.part_description. (run on mssql 2k) select a.*, b.part_description from part a, partmaster b where a.part_no *= b.part_no I want to and expect to have the result order like table "part". However, after the join, the order is different. I try to run it on mssql 7.0, the order is ok. Then I modify and run the statement select a.* from part a, partmaster b where a.part_no *= b.part_no on 2k again. The result order is ok. can anyone tell me the reason? Now I try to fix this problem is adding a sequence field "part_seq" into table "part" and run the statement by adding a order by part_seq. It does work! Regards, Simon
View Replies !
Does Column Order Matter When Creating A Table?
Does column order matter when creating a table? For example, Should NOT NULL columns always come before NULL columns? Should most frequently used columns always be near the top? What about text, ntext and image data types? Should they always appear near the end of the column order?
View Replies !
Newbie Q: How To Change The Field Order In A Table ?
Hi... Everybody, I have a set of tables in a database containing data. But I find that the field order in each table (as I view in the design window) is not the way I wanted. This is because I've added new primary keys to the table (as part of DTS services) which are now located at the end of the table. I need these to be moved to the beginning (along with the data, ofcourse) so that I could see all the primary keys in the beginning. Is there any way I can make the order in which fields appear in the design view to my liking ? Any help is appreciated, BR, Sudhakar
View Replies !
Flat File To Table - Rows Out Of Order
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 Replies !
Northwind - Problem With 'Order Details' Table
Hi Friends I faced a problem when using Northwind sample Database. On of the table in the database is 'Order Details'. I am unable to query on the table because of the space in the name ('Order'<space> 'Details') of the table. When I remove the space in the name, the reports associated in Cognos are not working properly. Let me know whether there is any purpose to put the name like that and if I want to see the data in query mode how it is possible. Thanks in advance Satish
View Replies !
Returning In Table Called Order Using UNION
Hello again, I am using UNION to return these 2 tables: TableA QID Q 1 Name? 2 Age? 3 Phone? TableB QID Q 1 DogName? 2 CatName? When I use the following query; SELECT * FROM TableA UNION SELECT * FROM TableB I get the following return: QID Q 1 whateverquestions 1 2 2 3 but I wish it to return in order of table invoke: QID 1 2 3 1 2 Is that possible? (by not changing QID) Cheers, James
View Replies !
Default Sort Order Of The Data When Inserting From One Table To Another
I have a data load process that reads data from flat file into a Stage table in sql server. The order of the records in the stage table is exactly same as the order in the flat file. The identity column on the Stage table (which is also the clustered index) represents the exact line/row number of the data in the filat file. I perform some transformations on the data in the stage table and then insert it into a cumulative table which has a clustered index on an identity column again. When I do this, does the order of the data in the cumulative table be in the same order as the data in the stage table? Anyone, please let me know if I can rely on SQL server to maintain the same order or I will be forcing a sort order on the Identity column (clustered index) of the stage table when I insert the data into a cumulative table. Thanks in advance!!
View Replies !
|
TRACKER
