Sort Ascending Isn't Sort Ascending
May 20, 2008
Hi,
have uncovered a curious problem with sort ascending in tables.
In a table I'm grouping on CONTRACT_ID field. The grouping works fine but the sort acending on CONTRACT_ID doesn't.
Here are the actual contract id's I have to sort:
0202018B
0202019C
0202020G
0202021H
0202022J
0202023K
0202800B
The above list is how the Crystal Report I'm converting does this sort - i.e. the normal, common sense, intuitive answer.
Here's how SSRS does the sort:
0202022J
0202021H
0202019C
0202800B
0202018B
0202023K
0202020G
i.e. a completely non-intuitive, non common-sense answer!
I can probably solve this by stripping the last character and converting to int via an expression, but why does one get a completely non-sensical answer with the standard values? What is the logic by which Microsoft have implemented sorting. It seems very odd to me. Can anyone shed any light on this?
thanking you in advance,
cheers,
rob
View 3 Replies
ADVERTISEMENT
Jun 24, 2015
As bcp does not allow for the column names to be included; I have developed a method for providing the columns. The end result is that two Tables are required for each output; a "ColumnNames" table and the Table that contains the actual data; however the bcp command is sorting the data; why this is happening?
According to Microsoft, by default bcp will not apply any sorting unless specified.
Here is the command I am using to perform the bcp output: -
SET
@bcpCommand =(select
'bcp "SELECT * FROM GPReports.dbo.MIS001_BCPColumnNames UNION SELECT * FROM GPReports.dbo.voltemp" queryout '
+ @FilePath+'
-c -t -T')
EXEC
master..xp_cmdshell
@bcpCommand
This is the bcp topic I referred to [URL] ....
View 3 Replies
View Related
Apr 19, 2006
How to sort table in sql2000 with ipaddress(format x.x.x.x) as column with nvarchar datatype in ascending order
without using stored procedure
Ex:
Table: netComputers(3 rows)
Column Name: ipAddress (string data type)
ipAddress
0.0.18.1
0.1.1.2
0.0.0.1
Sql query : if I use the query
Select ipAddress from netComputers order by cast( replace(ipaddress,'.','') as numeric(12)) asc
Gives result as :
ipAddress
0.0.0.1
0.1.1.2
0.0.18.1
Where as expected result should be:
ipAddress
0.0.0.1
0.0.18.1
0.1.1.2
View 18 Replies
View Related
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
Jul 23, 2005
Below is the statement i am working with and i am trying to figure outhow to have the count in ascending order...is this possible? Right nowit groups by FP in alphabetical order but i dont want that i want thewhatever the FP1 count is to be ascending but i am not sure how to goabout doing this...so any help or ideas?SELECT FP1 AS FP, COUNT(FP1) AS Expr1FROM dbo.FP1WHERE (FP1 IS NOT NULL) AND (LTRIM(RTRIM(FP1)) <> '')GROUP BY FP1
View 3 Replies
View Related
Apr 16, 2008
Currently I have a small page that includes a drop down list with about 120 possible choices to chose from.
The list works fine, however, the choices are not sorted ascending/alphabetical order. So it is very slow searching for
the selection you want.
See the link.
http://visualboxscore.com/boxscores/cfb_box_scores.aspx
Does anyone have any easy solution for this problem?
Below is the code I am using.
<%@ Page Language="VB" %>
<html>
<head id="Head1" runat="server">
<title>Visualboxscore.com - College Football Box score Query page</title>
</head>
<body>
<form id="form1" runat="server">
<b>Select Team:</b>
<asp:DropDownList ID="DropDownList1" DataSourceID="SqlDataSource2" AutoPostBack="true"
DataTextField="offense" runat="server" />
<asp:SqlDataSource ID="SqlDataSource2" runat="server" SelectCommand="SELECT DISTINCT [offense] FROM [cfb_boxscores]"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" />
<br />
<br />
<table>
<tr>
<td valign="top">
<asp:GridView ID="GridView1" AllowSorting="True" AllowPaging="false" runat="server"
DataSourceID="SqlDataSource1" DataKeyNames="date"
AutoGenerateColumns="false" Width="427px">
<Columns>
<asp:BoundField HeaderText="Date" DataField="date" DataFormatString="{0:MM/dd/yy}" ItemStyle-HorizontalAlign="Center" SortExpression="date" />
<asp:BoundField HeaderText="Team" DataField="offense" ItemStyle-HorizontalAlign="Center" SortExpression="offense" />
<asp:BoundField HeaderText="Rush No." DataField="rush_no" ItemStyle-HorizontalAlign="Center" SortExpression="rush_no" />
<asp:BoundField HeaderText="Rush Net" DataField="rush_net" ItemStyle-HorizontalAlign="Center" SortExpression="rush_net" />
<asp:BoundField HeaderText="YPC" DataField="yards_per_carry" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_carry" />
<asp:BoundField HeaderText="Pass Att." DataField="pass_att" ItemStyle-HorizontalAlign="Center" SortExpression="pass_att" />
<asp:BoundField HeaderText="Pass Yards" DataField="pass_yards" ItemStyle-HorizontalAlign="Center" SortExpression="pass_yards" />
<asp:BoundField HeaderText="YPA" DataField="yards_per_att" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_att" />
<asp:BoundField HeaderText="No. Plays" DataField="no_plays" ItemStyle-HorizontalAlign="Center" SortExpression="no_plays" />
<asp:BoundField HeaderText="Total Yards" DataField="total_yards" ItemStyle-HorizontalAlign="Center" SortExpression="total_yards" />
<asp:BoundField HeaderText="YPP" DataField="yards_per_play" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_play" />
<asp:BoundField HeaderText="TO Margin" DataField="turnover_margin" ItemStyle-HorizontalAlign="Center" SortExpression="turnover_margin" />
<asp:BoundField HeaderText="APY" DataField="all_purpose_yards" ItemStyle-HorizontalAlign="Center" SortExpression="all_purpose_yards" />
<asp:BoundField HeaderText="Points" DataField="points" ItemStyle-HorizontalAlign="Center" SortExpression="points" />
<asp:BoundField HeaderText="Opponent" DataField="defense" ItemStyle-HorizontalAlign="Center" SortExpression="defense" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="SELECT [date], [offense], [rush_no], [rush_net], [yards_per_carry], [pass_att], [pass_yards], [yards_per_att], [no_plays], [total_yards], [yards_per_play], [turnover_margin], [all_purpose_yards], [points], [defense] FROM [cfb_boxscores] WHERE ([offense] = @offense)"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="offense" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</td>
<td valign="top">
<asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" ID="SqlDataSource3"
runat="server" SelectCommand="SELECT [date], [offense], [rush_no], [rush_net], [yards_per_carry], [pass_att], [pass_yards], [yards_per_att], [no_plays], [total_yards], [yards_per_play], [turnover_margin], [all_purpose_yards], [points], [defense] FROM [cfb_boxscores] WHERE ([date] = @date)">
<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="date" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</td>
</tr>
</table>
<br />
</form>
</body>
</html>
View 2 Replies
View Related
Dec 30, 2005
Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance
View 3 Replies
View Related
Aug 16, 2002
Hi ,
Hopw can i sort the data in the ascending order so that i can see the date of my log files imported at the top of my table .
Right now my log files get imported and appended in the tables but they get appended in the descending order at the bottom of the last data .
Is there any way to sort the data so that i can see it in the reverse way when i open my tables ?
Many Thanks .
Anita.
View 1 Replies
View Related
Apr 18, 2014
I've been trying some examples on line and have not been able to get this to work.
Here is my table followed by what I am trying to output.
Id | ParentId | CategoryName
33 | 0 | Fruits
34 | 0 | Vegetables
35 | 0 | Meats
37 | 33 | Grapes
38 | 33 | Oranges
[Code] ....
I want to output this for a dropdownlist in C# with ascending categories and ascended subcategories
48 0 Electronics
33 0Fruits
3933 -Apples
4033 -Bananas
3733 -Grapes
3833 -Oranges
[Code] ...
This is what I have currently, still a long way off :/
SELECT TOP (100) PERCENT fcat.Id AS fcat_id, fcat.CategoryName AS fcat_name, fcat.ParentCategory AS fcat_parent, fsub.Id AS fsub_id,
fsub.CategoryName AS fsub_name, fsub.ParentCategory AS fsub_parent
FROM dbo.ProductCategories AS fcat LEFT OUTER JOIN
dbo.ProductCategories AS fsub ON fcat.Id = fsub.ParentCategory
ORDER BY fcat_name, fcat_id, fsub_name
View 2 Replies
View Related
Jan 8, 2008
Hi
I have a table with column name starts from col1 to col20. IS their anyway to insert the data and sort the table by col1 and col2 and SAVE IT.
View 3 Replies
View Related
Nov 27, 2007
Hi
I have Constructed a table
Table Name : MasterEntry
Column Name:
MasterEntryNumber
ServiceName
ServiceDepartment
EmployeeName
MasterEntryNumber is GenerationNumber where ever any entry is happen in table
if put a queries [select * From masterentry Order by MasterentryNumber] is give output in ascending order
e.g MasterEntryNumber has
1
2
3 it give correct ordering
but if i exceed more 10 if i put same queries
Output of queries is
1
10
11
2
3 this is the out of the queries if it exceed more 10 rows
i want queries should be look like this
1
2
3
...... 10
did any have experience on this issue please let me know where i can fix
kinds regards
View 8 Replies
View Related
Aug 7, 2007
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
View 1 Replies
View Related
Jul 23, 2015
I have a below table,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ParentItemName, ItemOrder, ReportId)
VALUES('Item1', NULL, 1, 5),('Item1-Child1', 'Item1', 0, 5),('Item1-Child2', 'Item1', 0, 5),('Item2', NULL, 2, 5),
('Item11', NULL, 1, 6),('Item12', NULL, 2, 6),('Item12-Child1', 'Item12', 0, 6),('Item13', NULL, 3, 6)
SELECT * FROM @TBL
Here,
1. for all ReportId, child items's ItemOrder = 0
2. example, for ReportId = 5, both child items ("Item1-Child1" & "Item1-Child1") of parent "Item1" has ItemOrder = 0
I need to,
1. update all child items with ascending numbers starts with 1 against each parent and each report.
2. for each different parent or different report, order by should starts with 1 again.
View 2 Replies
View Related
Jul 5, 2007
I noticed the StockDate is not sorted in proper order, like ascending order...
Code:
select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc
I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd...
The sample data is MM/DD/YYYY...
So, how do I get it ordered propery by Year, Month then Day??
View 2 Replies
View Related
Oct 26, 2006
i encounter this error..
Cannot sort a row of size 8107, which is greater than the allowable maximum
of 8094.?
why this error occur? can someone explain? how to avoid this? thanks!!
View 1 Replies
View Related
Dec 10, 2007
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...
Regards,
Lars
View 2 Replies
View Related
Mar 11, 2008
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
Example Data
Table A
IDDoc Document
---------------------------
1 1467.doc
2 8722.doc
3 A47F.doc
4 88DQ.doc
5 ABCD.doc
Table B
IDDoc Dept First
----------------------------
1 A John
2 A Bob
3 A Ralph
4 A Diane
Results I Want
IDDoc First
-------------------
5 NULL
2 Bob
4 Diane
1 John
3 Ralph
Any help is appreciated. If I've posted in the wrong forum, please feel free to direct me to a better one.
Thanks in advance!
Jim
View 3 Replies
View Related
Oct 12, 2006
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
View 2 Replies
View Related
Feb 27, 2008
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
View 1 Replies
View Related
Mar 18, 2004
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
View 3 Replies
View Related
Aug 31, 2005
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
View 3 Replies
View Related
Mar 29, 2001
When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
View 2 Replies
View Related
Mar 29, 2001
When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
View 1 Replies
View Related
Jul 20, 2000
Hi,
I am trying to restore .DAT file from dump. Its giving me error ..saying that the sort order id used for dumping was 42 not the default value 52.
How can i change the sort order to 42.
I am using sql server 6.5
Thanks
Srinivas
View 4 Replies
View Related
Jan 13, 2000
We have a vendor who insists that sql server 7 be set to a binary sort order. Is there any real advantage to this as opposed to a dictionary sort?
View 2 Replies
View Related
Sep 21, 2000
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?
Thanks.
View 3 Replies
View Related
Sep 20, 2000
HOW CAN I CHECK THE SORT ORDER OF MY OLD SERVER?
HELP
View 1 Replies
View Related
Oct 5, 1999
Is there a query you can run against a 7.0 server to return the chracter set and sort order.
View 1 Replies
View Related
Oct 18, 1999
I'm trying to setup a duplicate of an old SQL Server 4.2 server to put in place while we upgrade the server, but I can't get the sort-order right. I know the existing server uses sort order id 40, but I can't find which sort-order that corresponds to during the install process. If anyone can give me a system table that lists all the sort orders names and id's, or can tell me what the text name for sort order 40 is, I would be very grateful.
Thanks,
Rob.
View 2 Replies
View Related
Jul 13, 2004
Hi All,
I have a table with 3 columns. Product, Location and Value. The data looks like this:
NULL NULL 100
Atlanta NULL 50
Atlanta Cookie1 30
Atlanta Cookie2 20
Dallas NULL 120
Dallas Cookie1 80
Dallas Cookie2 40
This table gets filled with a Groupby with Rollup option. The NULLS show subtotals/total. Is there a way to build a query that returns the results with NULLs at the bottom of each section like:
Atlanta Cookie1 30
Atlanta Cookie2 20
Atlanta NULL 50
Dallas Cookie1 80
Dallas Cookie2 40
Dallas NULL 120
NULL NULL 100
Thanks,
Shab
View 4 Replies
View Related
Jul 17, 1998
I need to copy the structure and data of an existing SQL 6.5 server to one with a different sort order. Normally, I would use the transfer tool to accomplish this, but the servers are on different networks. My question is, is BCP the answer? In other words, will the data copied via BCP from the sending server be able to be copied on the recieiving server. Also, is there a way to automatically generate the BCP statements for all tables? What I would really like is to be able to get at the scripts and data files created by the transfer tool.
View 1 Replies
View Related
Jul 9, 2001
How can I set the sort order to 42, nocase when I install sql server 6.5
does Setup gives you some option to check to set sort order ?
View 1 Replies
View Related
Mar 23, 2001
For SQL 2000,
Can I assign different sort order on the database level?
thanks!
View 2 Replies
View Related
