I have quite a long SP which is made up of various tables, temp tables, etc.
I am creating 11 physical tables which i am using to create a DTS package to auto output to an excel spreadsheet (template for a report).
The problem is although i have used ORDER BY statements to sort the data in ascending order the data isnt sorted.
Heres an example of one of the table outputs
--Create Table of Managers
DELETE FROM Weekly_Term_Code_Output7
INSERT INTOWeekly_Term_Code_Output7
SELECT #Temp_Agent.Manager
FROM #temp5 INNER JOIN
#Temp_Agent ON #temp5.agent_login = #Temp_Agent.agent_login
WHERE (NOT (#Temp_Agent.Manager IS NULL))
GROUP BY #Temp_Agent.Manager
ORDER BY #Temp_Agent.Manager
ive also tried placing an order by statement in the output to excel in the DTS package but this also doesnt work?
Hi all,We recently upsized two Microsoft Access Databases to SQL. We'reusing an ADP (2002) as the front end.All the conversion issues have been resolved, except for one:Whenever we insert a record into a table, the table isn't sorted byprimary key like I would expect. Instead, the record can be found atthe end of the table. This makes finding a particular record(especially as time goes on) very difficult.I've tried eliminating all indexes except for the primary key, andalso writing AFTER INSERT triggers, but the table still does not sortcorrectly.Any suggestions would be greatly appreciated!Matt
I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC, case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC, case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC, case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC, case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC, case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC, case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC, case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end, case when @SortColumn = 'FileDataID' then FileDataID end, case when @SortColumn = 'Field2' then FileDesc when @SortColumn = 'VersionNotes' then VersionNotes end
case when @SortOrder = 'DESC' then DESC when @SortOrder = 'ASC' then ASC end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
I am adding this to my c#.Net app...How can I sort all rows in a table?After it is sorted, will it remain so after i do a cnSQL.Close();so that when I then do a:string strSQL = "SELECT InvoiceNumber " +"FROM tblPrintInvoice " ;Is it still sorted for me to do a:drSQL.Read?Thanks,Trint
We have developped an ETL. For development we used small test files (10 000 rows) to test if it works correctly. This runs in less then a minute
In Test we are using a file which contains all rows (7 million). We did twice a test and we first stopped the process after a week and the 2nd time we stopped the process after a weekend.
We are able to trace the problem to the point where it has to sort the tables.
The proces is pretty simple. We use two connectors to directly connect to the tables. Then we have two blocks to sort the data. And then we have one block to merge the data.
Should we which to let SQL do the sorting ? Since it is in staging is has no index on that column. A select on the tables with an order by takes 3 minutes to return all those rows.
Any idea's ?
Also is there a page with the best practices for ETL ?
I have a table report that lists the prior 12 months of sales data. I'd like the report to display in reverse order from current month back. When I sort decedning by month it puts the months in reverse alphabetic order. How can I sort by numeric order?
I have a "Sales shipments" report with several colums. I want a parameter for the user so he choose how to sort. I have two colums in my report I want to sort on: ItemNumber and DeliveryDate.
I added a Report Parameter UserSort with two labels (and values): Item (ItemNumer) and Date (DeliveryDate).
i am currently trying to port a "Business Objects" report to SQL Server Reporting Services.
in B.O., there is a possibility to define the order of the table values by previewing the table values and moving them up or down.
Example : the dataset contains data for 3 items, so in the Custom Sort window, I will see these 3 items and I can order them by moving them up or down in a listbox.
I'm trying out the interactive sort feature (and have searched high and low for this answer), but can not make the sorting work if I have the data in the table grouped. If I remove the group, the sorting works fine but the output is not acceptable due to the duplicated records in the list.
Is there a way to change the order that "select * from #table" returns data? It doesn't appear to return data in the same order that it was inserted into the table. This wasn't a problem with the same code in SQL Server 2005.
i have table in sql and every month i am appending new data in this table, but i want to sort data Culumn Name "account_no' from smallest to largest, and whenever i append new data to this table it auto sort every time,
Hello I have a table with a RowNumber Group which shows 10 rows in each page. it works fine. in the next step i put an Interactive sorting on one column. i expect that the sorting is on the table no regarding the presentation which shows only 10 rows. in reality what happens is that the table was sorted seperated for each part of the 10 rows table, and no for the whole table.
does any one have a suggestion how to force the Interactive Sort , sorting he Whole table first and only later to present 10 rows each page (use the group expression)
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.
I've seem some good posts similair to this, but I haven't been able to find my exact issue.
I have the following table:
ID Name Location Start End
1 Joe NY 2000 2001
1 Joe CA 2002 2004
1 Joe MA 2005 2008
2 Sue NJ 2003 2004
2 Sue FL 2004 2008
3 Bob CA 1999 2000
3 Bob WA 2001 2004
3 Bob OR 2005 2006
3 Bob MI 2007 2008
As you can see, the Location, Start and End dates can vary for each person and I don't know how many rows a single person might have.
The result I want, is a "pivot like" table.
ID Name Location1 Start1 End1 Location2 Start2 End2 Location3 Start3 End3 Location4 Start4 End4
1 Joe NY 2000 2001 CA 2002 2004 MA 2005 2008
2 Sue NJ 2003 2004 FL 2004 2008
3 Bob CA 1999 2000 WA 2001 2004 OR 2005 2006 MI 2007 2008
I assume I can first do a count of the maximum rows for an individual and that is greatest number of columns I would need. But doing that and trying to figure out the rest has really stumped me.
Any thoughts, ideas and suggestions would be greatly appreciated.
This is a newbie question. For some reason when I view the contents of tables I have created on my SQL Server (version 7) database, the sort buttons in the "Data in Table" window are grayed out.
We are using a modeling technique called Anchor Modeling in our data warehouses. You can read more about the technique itself at our homepage http://www.intellibis.se, where we have published a fact sheet and a recently held presentation (TDWI European conference). One of the features with this technique is its simple way to historize data. This is done by having a fromDate column which together with the surrogate key will yield a unique combination. On the tables that has this kind of historization we add a primary key, which in turn will create a clustered index, with the following specification (surrogateKey asc, fromDate desc). This will physically order data on the storage media according to the specificed columns and ordering. Now I move on to create a "latest view" of this table which does a subselect to find the latest version for every surrogateKey using max(fromDate). Should not the optimizer now figure out that data is ordered so that the latest version always comes first for every surrogateKey, hence any sorting would be unneccessary? If I look at the actual execution plan after running a query that uses the view there is a sort in the plan, but the cost is always 0%. Does this mean that it did not sort the data, or that it did call a sorting routine, but it actually took very little time to do the sorting? If so, is there a reason that is has to do the sorting or could it have been left out by an even smarter optimizer?
I would also like to applaud the people behind the optimizer, since it will figure out which tables are in fact necessary to query and eliminate others, even if I have left joined them into the view I am using. This speeds up performance and makes anchor modeling feasible. Unfortunately optimizers from other vendors seem to have trouble doing this...
I've been racking my brain all day and I finally decided to ask for help. I've got two tables with rows from the first that need to be sorted by the second. The problem is that the rows don't always exist in the second table. I've tried various forms of INNER, LEFT, RIGHT, OUTER, LEFT OUTER, CROSS, etc., etc., etc. and nothing (oh yeah UNION too). Every time I get close, I lose the records that don't have matches.
Something close-
SELECT A.IDDoc, B.First FROM A LEFT JOIN B ON A.IDDoc = B.IDDoc WHERE B.Dept = 'A' ORDER BY B.First
SELECT LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) as Date,SUM(CASE a.status_id WHEN 1000 THEN b.act_point ELSE 0 END) as Programming,SUM(CASE a.status_id WHEN 1016 THEN b.act_point ELSE 0 END) as Design,SUM(CASE a.status_id WHEN 1752 THEN b.act_point ELSE 0 END) as Upload,SUM(CASE a.status_id WHEN 1032 THEN b.act_point ELSE 0 END) as Testing,SUM(CASE a.status_id WHEN 1128 THEN b.act_point ELSE 0 END) as Meeting,SUM(CASE a.status_id WHEN 1172 THEN b.act_point ELSE 0 END) as OthersFrom task_table a,act_table b where a.status_id=b.act_id and a.user_id=(select user_id from user_table where user_name='Raghu') and a.task_date like '%/%/2006' GROUP BYLEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4)Output :Aug 2006 294 0 0 80 0 0 Jan 2006 14 0 0 0 0 0 Oct 2006 336 0 0 0 0 0 Sep 2006 3262 20 24 8 16 0 How to sort the date in ascending Order ?Jan 2006Aug 2006Sep 2006Oct 2006
I have: 4 tables and 1 table variable. CCenters (ID, Name) Campaigns (ID, Name) Rel (ID, CCenterID, CampaignID) - [many to many] and @SCampaigns (ID, CampaignID) - represents the selected campaigns by the user
performing the commands below I would get the centers associated with the campaigns selected.SELECT CCenterID FROM Rel INNER JOIN @Campaigns ON @SCampaigns.CampaignID = Rel.CampaignID But what I really want are the common centers to the selected campaigns. Thanks
I am trying to select a record from a table where it has the smallest priority how would you go about doing this is there a cool sort command or is there a select command syntax that can do this thanks
I've made this example and it loads a picture into a database. (MsSql )Take a look at the code, it works just fine however it leaves a process in sleeping mode "avaiting command" in Enterprise manager under "Management/current Activity/Process Info"Is it supposed to be like this or is it supposed to be reemoved after .net is finished??Code snip_______________________________________________________ Dim conn As New SqlConnection("Data Source = (local);Initial Catalog = " & "test;User ID = NAME; Password=PASSWORD;") Dim cmd As New SqlCommand("Select * from tab_bild", cnn) Try conn.Open() Dim myDatareader As SqlDataReader myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection) Do While (myDatareader.Read()) Response.ContentType = myDatareader.Item("PersonImageType") Response.BinaryWrite(myDatareader.Item("PersonImage")) Loop conn.Close() Response.Write("Picture info succesfully retrieved") Catch SQLexc As SqlException Response.Write("Read failed, Reason: " & SQLexc.ToString()) End Try End Sub________________________________________________________________Please can someone explain this for me or sort this out for me.All help is welcome even if its only points me too a direction.RegardsTombola
I have a table that most of the data has the same value, but there are only a few that do not match that value. I want to populate a listbox with all values from the table, but I'd like to have the majority listed first, followed by the others (the few that don't matach). What's the best way to approach this with SQL?
