Say I have a result set with two fields numbers and letters.
1 A3 A1 B2 B
The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order? When I tryselect distinct Number from MyResultSet
it will reorder the new result set by the Number field and return
123
However, I'd like maintain the Letter order and return
I built a query that brings in 'Discounts' (bolded) to the Order detail by using the bolded syntax below. I started off by running the query without the bolded lines and got exactly what I was looking for but without the ‘Discount’ column. When I tried to add the ‘Discount’ into the query, it duplicated several order lines. Although total ‘Discount’ column ties out to the total amount expected in that column, ‘Total Charges’ are now several times higher than before.
For example, I get 75 records when I run without the bolded syntax and I get several hundred results back when adding back in the bolded syntax when i should still be getting 75 records, just with an additional column ‘PTL Discount’ subtotaled.My question is, how to I introduce a new select or join from another table without duplicating the original data?
l've some excel files controlled by Vendor which changing frequently. The only thing does not change is the header name of each column.
So my question is, is there any way to create a new table based on the excel file selected including the column name in SSIS? So that l can use the data reader as source to select those columns l am interested on and start the integration.
I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ABC]( [id] [uniqueidentifier] NOT NULL,
[Code] ....
When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ABCD]( [id] [uniqueidentifier] NOT NULL,
[Code] .....
The trigger I've currently written looks like this:
/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]
[Code] ...
This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?
I can't sort by uniqueidentifier in descending as those can be random.
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?
I have a table 'wRelated' with the following columns
[related_id] [int] [channel_id] [int] [mui] [varchar] [price_group_id] [type_id] [int] [related_mui] [varchar] (100) [date_started] [smalldatetime] [date_ended] [smalldatetime] [date_entered] [datetime] [deleted] [tinyint], [rank] [int] data in column [mui] is repeated as the table has more than one entries for the same [mui], The requirement is to select the distinct[mui] but value in all the other columns for the same mui should be select in the next row with null for the same [mui] The recordset expected should be something like this.
This query demonstrates a problem I have run across:
USE AdventureWorks
GO
-- This query works fine.
SELECT DISTINCT FirstName AS Name1 FROM Person.Contact ORDER BY FirstName
GO
-- This query also works fine.
SELECT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName
GO
-- This query returns error 145
SELECT DISTINCT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName
GO
The last query returns the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified". It will work if I change ORDER BY to use "Name1" instead of "FirstName", but in the situation I have at hand, the query is generated by third-party software and I don't have the ability to change it. Can anyone explain why what's going on here? Oddly, this same query will work if I run it against SQL Server 2000.
I am trying insert the "Order by" clause into DMX but nothing is working.
INSERT INTO MINING STRUCTURE [ARS] ( [OrderID], [Product_Table](SKIP, [Product]) ) SHAPE { OPENQUERY ([dwMDA on PSD_TEST_TEST], 'Select Top 30000 "OrderID" From "dwMDA"."dbo"."vDetail" Order By "OrderID"') } APPEND ( { OPENQUERY ([dwMDA on PSD_TEST_TEST], 'Select Top 30000 "OrderID","Product" From "dwMDA"."dbo"."vDetail" Order By "OrderID"')} RELATE [OrderID] TO [OrderID] ) AS T
This is the DMX for market basket analysis.
I am also unable to use "order by" when i pull data into excel.
here's an example of something that doesn't work:
Select Top 20000 "OrderID","ProductGroupDescription" From "dwMDA"."dbo"."vDetail"
Order By "OrderID"
Can someone show me the exact working SQL commands they are using for the "Order By" clause when they import data from sql into an excel spreadsheet?
I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.
How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name, A.index
FROM ... ... ORDER BY A.[Index], A.Name ASC
END
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name FROM ... ... ORDER BY A.[Index]
I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?
this query returns the values i want in the table.
select a.quantity * b.price from tblCustomerPurchases as a join tblProduct as b on a.ID=b.ID
I have a table that I want to re-order the ID column. The ID are not in order now due to some insertion and deletion. What are the steps to re-order the ID column?
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!
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
I have two tables, one is called (questions), the second one (answers).
questions columns are (ID,questionTitle)
answers columns are (ID,questionID,answer, answerDate)
I use this query to load data: "SELECT q.questionTitle,COUNT (a.ID),a.answerDate FROM questions q LEFT JOIN answers a ON q.ID=a.questionID" the query is easy, but my problem which I can't solve is how can I fetch the data ordered by the column answerDate, I mean I want the first record to be the one which has the most recent answer and so on.
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
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?
I am attempting to sort the results of a query executed against a table variable in descending order. The data is being inserted into the table variable as expected, however when I attempt to order the results in descending order, the results are incorrect. I have included the code as well as the result set.
DECLARE @tblCustomRange AS TABLE
(
RecordID INTEGER IDENTITY(1,1),
RangeMonth INTEGER,
RangeDay INTEGER
)
DECLARE @Month INTEGER
DECLARE @Day INTEGER
-- Initialize month and day variables.
SET @Month = 8
SET @Day = 11
-- Insert records into the table variable. INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,2)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,27)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (6,10)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (9,22)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (12,16)
-- Select everything from the table variable ordering the results by month, day in -- descending order
Say you have an existing populated SQL 2005 database, with 700+ tables, and you want to just change the order of the columns inside every table. Short of manually building conversion scripts, anyone know an automated way to do this? I was thinking thru ways to do them all in one shot, and have tools like Erwin and DbGhost that could be used also. Basically moving some standard audit columns from the end of the tables to just after the PK columns.
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.
Hello all,I'm using SS2K on W2k.I'v got a table say, humm, "Orders" with two fields in the PK:OrderDate and CustomerID. I would like to add an "ID" column whichwould be auto-increment (and would be the new PK). But, I would reallylike to have orders with the oldest OrderDate having the smallest IDnumber and, for a same OrderDate, I'd to have the smallest CustomerIDfirst. So my question is:How could I add an auto-increment column to a table and make it createits values in a particular order (sort by OrderDate then CustomerIDhere)?In the real situation, the table I want to modify has around 500krecords and the PK has 5 fields and I want to sort on three of them.Thanks for you helpYannick
I was just messing around with some ad hoc views and table returningUDFs today so I could look at and print out data from a small tableand noticed something strange.If I stick my select statement into a View the columns are returned inthe order I specify in the SELECT, but if the same statement is in a UDF(so I can specify a parameter), the columns are not returned in theorder specified in statement.I know that relations don't have a specified column order, but it was myunderstanding that a SELECT statement could be used to define how youwant your data presented. Views seem to respect the order specified inthe SELECT, but functions don't.What am I missing? Is there some way to force the order of the columnsreturned from a SELECT?View:CREATE VIEW dbo.View1ASSELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE '4.2.%')Column order from this view:Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,ElseStmt, NextStmtFunction:ALTER FUNCTION dbo.Function1(@SearchPrm varchar(255))RETURNS TABLEASRETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,ChildStmt, ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE @SearchPrm) )Column order from this function:Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,ParentStmt, ForStmtTable:(I know that this table isn't entirely normalized, but it serves mypurposes to have a matrix instead of a fully normalized relation):CREATE TABLE dbo.tblStmt (StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,Ident VARCHAR(255),Text TEXT,ErrorText TEXT,Type INT,ParentStmt VARCHAR(255),ChildStmt VARCHAR(255),IfStmt VARCHAR(255),ForStmt VARCHAR(255),ThenStmt VARCHAR(255),ElseStmt VARCHAR(255),NextStmt VARCHAR(255),FullName VARCHAR(255),LocalName VARCHAR(255),Method INT)INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')
This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.
In y sql server table has millions of records available. I don't want to drop the tables.
My requirement is I want to change the column order of an existing table. some tables I am able to saving on design window Like Below image.
Even I had generate a script and using that script trying to execute on Management Studio but unable to saving the new column orders. I am getting the Timeout expired error after couple of minutes. How can we save the orders without dropping the table !
date item_id grade in out ------------- --------------- ------- -------- ---------- 01-01-08 001 A 10 0 02-01-08 001 O 8 0 01-02-08 002 O 1 0 03-01-08 001 T 0 10 02-01-08 003 O 20 0 02-01-08 003 T 0 10 02-01-08 003 B 0 8
Result View =======
Item_id A B O T Total
001 10 0 8 -10 8 002 0 0 1 0 1 003 0 8 20 -10 18
where grade could be any Alphabet, the column of result query could varies each grade contain sum of (in-out) of all item_id of detail table.
Is there a good way in SQL to select records that have been sent in an excel spreadsheet and import them into a temporary table?
I have a list of order codes that have different seg codes for example:
Order CodeZSeg 7625 ZBL 717009 ZBL 3277 ZCI 8144 ZCI
Each order code is unique and is classifed into a zseq. Depending on this zseg I need to update an existing table with the following field names and values for each order code:
For example for an order code classified as a ZBL I need to insert into an existing table that uses order code as the PK
Value Code: Patrac Value Description: Patient Race Value Type: ST Units: None Template Name: Lab_Master_ Field Name: AOE_BL_Race
There are multiple Value codes for each Zseg that will be inserted for each order code.
Can someone tell me the best way to do something like this?
