Hey guys, I have a view with dates (TheDate) meant to be arranged in descending order. When I 'Execute SQL' while in the view, the DESC order works just fine and shows up with the latest date first going down. However, once I 'OPEN VIEW' the order keeps defaulting to ASCending order.
How do I keep it in DESC order for viewing? Here's the statement:
SELECT TOP (100) PERCENT TheDate
FROM dbo.MyDates
ORDER BY TheDate DESC
hi, i have headers at the top of my list and would like for people to click the pubs link and it orders the pubs alphabetically descending and when they click again to ascend also to do this with towns and addresses and postcodes?!
can someone please help?
J x
Does anyone have a general rule or guide on when to use this SQL 2000 option when creating indexes? I was thinking generally on nonclustered indexes where the column would be unique and incremental and usually filtered on by range and often used in the order by clause. Such as columns of datetime or integers datatypes. Thanks.
View 1 Replies View RelatedHi all,I have a SQL statement that allows paging and dynamic sorting of thecolumns, but what I can't figure out without making the SQL a dynamicstring and executing it, or duplicating the SQL statement between anIF and ELSE statement.Following is the SQL statement;set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_search]@search VARCHAR( 80 ), @startRow INT = 1, @endRow INT = NULL, @postcode AS CHAR( 4 ) = NULL, @suburb AS VARCHAR( 40 ) = NULL, @stateIdentity AS TINYINT = NULL, @fromLatitude AS REAL = NULL -- latitude the user is located in, @fromLongitude AS REAL = NULL -- longitude the user is located in, @sort TINYINT = 1ASBEGINSET NOCOUNT ON;DECLARE @calculateDistance BIT;SET @calculateDistance = 0;-- get the longitude and latitude if requiredIF ( NOT @postcode IS NULL )BEGINSELECTDISTINCT@fromLatitude = latitude, @fromLongitude = longitudeFROMtbl_postalcodeWHERE(postalcode = @postcode)SET @calculateDistance = 1ENDELSE IF ( NOT @suburb IS NULL AND NOT @stateIdentity IS NULL )BEGINSELECTDISTINCT@fromLatitude = latitude, @fromLongitude = longitudeFROMtbl_localityWHERE(locality = @suburb)AND(stateIdentity = @stateIdentity)SET @calculateDistance = 1END/*ELSE IF ( @fromLatitude IS NULL AND @fromLongitude IS NULL )BEGINRAISERROR( 'You need to pass a valid combination to this storedprocedure, example: postcode or suburb and state identity or longitudeand latitude', 18, 1 );END*/SELECT D1.[row], D1.[totalRecordCount], D1.[classifiedIdentity], D1.[title], D1.[summary], D1.[price], D1.[locality], D1.[state], D1.[postcode], D1.[addedLast24], D1.[dateStamp], D1.[t2Rank], D1.[t3Rank], D1.[tRank], D1.[distance], F.[originalName], F.[extension], F.[uniqueName]FROM(-- derived tableSELECT ROW_NUMBER() OVER ( ORDER BY CASE @sort WHEN 0 THENCAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) )WHEN 1 THEN C.title WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude, @fromLongitude, L.latitude,L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN ( C.locality + ' ' +C.state ) WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC ) AS row, COUNT( * ) OVER() AS totalRecordCount, C.[classifiedIdentity], C.[title], C.[summary], C.[price], C.[locality], C.[state], C.[postcode], CASE WHEN ( C.[dateStamp] >= DATEADD( day, -1, GETDATE() ) )THEN 1 ELSE 0 END AS addedLast24, C.[dateStamp]/* , t1.RANK AS t1Rank */, t2.RANK AS t2Rank, t3.RANK AS t3Rank, /* COALESCE( t1.RANK, 0 ) + */ COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) AS tRank, CASE @calculateDistance WHEN 1 THEN CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude, @fromLongitude, L.latitude,L.longitude ) ) ELSE 0 END AS distanceFROM [tbl_classified] AS CINNER JOINtbl_locality LONC.localityIdentity = L.localityIdentity/* LEFT OUTER JOINCONTAINSTABLE( tbl_category, title, @keyword ) ASt1ON FT_TBL.categoryIdentity = t1.[KEY] */LEFT OUTER JOINCONTAINSTABLE( tbl_classified, title, @search ) ASt2ON C.classifiedIdentity = t2.[KEY]LEFT OUTER JOINCONTAINSTABLE( tbl_classified, description,@search ) AS t3ON C.classifiedIdentity = t3.[KEY]WHERE ( /* COALESCE( t1.RANK, 0 ) + */COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) ) != 0) AS D1LEFT OUTER JOINtbl_classified_file CFOND1.classifiedIdentity = CF.classifiedIdentityLEFT OUTER JOINtbl_file FONF.fileIdentity = CF.fileIdentityWHERE( row >= @startRow )AND( @endRow IS NULL OR row <= @endRow )ENDThe part I'm having trouble with is making the sort order in thefollowing line dynamicORDER BY CASE @sort WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) ) WHEN 1 THEN C.title WHEN 2 THENCAST( CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude,@fromLongitude, L.latitude, L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN( C.locality + ' ' + C.state ) WHEN 4 THEN CAST( C.price ASCHAR( 10 ) ) END ASCany help would be greatly apprecaited.Thanks
View 1 Replies View RelatedI am using sql statement like SELECT CREATEDBY,FIRSTNAME,BUSINESS,NOTES,NOTESDATE FROM BUSINESS ORDER BY NOTESDATE DESC, CREATEDBY ASC
But NotesDate is sorting descending order, but only sorting based on the date and month not on year
Please help me
I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.
Say I have a table called myTable with columns col1,col2,col3,
Here's what I'm trying to do
with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then
case when @COLTOSORTBY='col1' then col1 asc
when @COLTOSORTBY='col2' then col2 asc
else col3 asc
end
else
case when @COLTOSORTBY='col1' then col1 desc
when @COLTOSORTBY='col2' then col2 desc
else col3 desc
end
end
from myTable
)
Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.
Thanks.
hi
Like Oracle SQL, does SQL server has a 'desc' command which describes the tables of a database?
It would be very helpful instead of using Enterprise Manager
How and where to get table and column descriptions of all system
table and system objects.....etc.,
In the followinf portion of my SQL Statement, I cannot figure out how to specify ASC or DESC for the OrderBy... If I Put OrderDate ASC after the THEN, I get an error and if I put @SortDir after the END I get an error... Can Anyone help with this?
WHERE OrderID IN(SELECT OrderID FROM #rsltTable WHERE ID >= @l_FirstRecord AND ID <= @l_LastRecord)ORDER BYCASE @OrderByWHEN 'OrderDate ASC' THEN OrderDateWHEN 'OrderDate DESC' THEN OrderDateEND
Thank You,Jason
I have a column name "DESC" in SQL Server table and I am getting an error when trying to insert data into this column. I cannot rename this column as it's not in my hand.
Please anybody reply me with good solution I am new to SQL.
Thanks
Hi,
I have been training in SQL on MySQL, and now that I am on TSQL, I can't seem to find an equivalent to the command:
Code Snippet
DESC table_name;--OR
DESCRIBE table_name;
In MySQL, either command returns a list of the columns of the table, as well specifications about these columns (whether or not they can accept NULL values, their default values, etc.)
I have a stored procedure in my SQL 2005 Server database named Foo that accepts two parameters, @paramA and @paramB.In my ASP.NET page, I have these:<asp:GridView id="gv" runat="server" AutoGenerateColumns="true" DataSourceID="DS" AllowSorting="true" DataKeyNames="ID"/><asp:SqlDataSource ID="DS" runat="server" ConnectionString="<%$ ConnectionStrings:CS1 %>" SelectCommand="Foo" SelectCommandType="StoredProcedure" OnSelecting="DS_Selecting"> <asp:Parameter Name="paramA" Type="String" /> <asp:Parameter Name="paramB" Type="String" /></asp:SqlDataSource>In my setup, paramA and paramB are set in DS_Selecting(), where I can access the Command.Parameters[] of DS.Now, here's the problem. As you can see, the GridView allows for sorting. When you click on a header title to sort, however, the GridView becomes empty. My question is, how can I get the GV sorted and in the correct direction (i.e. asc/desc)? My first step in my attempt was to add another parameter to the SqlDataSource and sotred procedure Foo (e.g. @SortByColumn), then changed Foo appropriately: ALTER PROCEDURE Foo @paramA nvarchar(64), @paramB nvarchar(64), @SortColumn nvarchar(16) = 'SearchCount' AS SELECT * FROM Searches ORDER BY CASE WHEN @SortColumn='SearchCount' THEN SearchCount WHEN @SortColumn='PartnerName' THEN PartnerName ELSE ID ENDThat works find and dandy. But wait--I want to get the correct ORDER BY direction too! So I add another parameter to the SqlDataSource and Foo (@SortDirection), then alter Foo: ... SELECT * From Searchces ORDER BY CASE /* Keep in mind that CASE short-circuits */ WHEN @SortColumn='SearchCount' AND @SortDirection='desc' SearchCount DESC WHEN @SortColumn='SearchCount' SearchCount WHEN @SortColumn='PartnerName' AND @SortDirection='desc' PartnerName DESC WHEN @SortColumn='PartnerName' PartnerName WHEN @SortColumn='ID' AND @SortDirection='desc' ID DESC ELSE ID END ...But including DESC or ASC after the column name to sort by causes SQL to error. What the heck can I do, besides convert all my stored procedures into in-line statements inside the ASP page, where I could then dynamically construct the appropriate SQL statement? I'm really at a loss on this one! Any help would be much appreciated!Am I missing a much simpler solution? I am making this too complicated?
View 2 Replies View RelatedThe following statement seems to ignore my sort expression....how can I fix this? Select TB.* FROM TblBlogs TB JOIN (select top 20 blogId, count(*) cfrom tblCommentsgroup by blogIdorder by count(*) desc) T20 ON TB.BlogId = T20.BlogId
View 3 Replies View RelatedThere is a index: CustomerInfo_1
with keys: customerId, EnteryDate DESC
I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored?
I tried system tables such as sysindexes and sysindexkeys tables. But could not find it.
Any help in this regard will be truly appreciated.
Thank you.
Regards,
Anuj Goyal
There is a index: CustomerInfo_1
with keys: customerId, EnteryDate DESC
I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored?
I tried system tables such as sysindexes and sysindexkeys tables. But could not find it.
Any help in this regard will be truly appreciated.
Thank you.
Regards,
Anuj Goyal
I have come across a problem executing a select with a multi-part where clause that only shows up if there are multiple indexes on the table. The situation using a simplified table is shown below
create table tblTest( utcTimestamp datetime NOT NULL, testType int NOT NULL)go
insert into tblTest (utcTimestamp, testType) VALUES('6/1/2003 0:0:0', 100)go
Now, without adding any indexes to the table, I can execute the following select and it works fine, returning the single row in 2003:
select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0' and testType = 100go
Furthermore, if I introduce a single descending index on just the utcTimestamp:
CREATE INDEX IX_tblTest_Timestamp ON tblTest (utcTimestamp DESC)go
the search still works.
HOWEVER, if I now introduce another index:
CREATE INDEX IX_tblTest_EntryType_Timestamp ON tblTest ( testType, utcTimestamp DESC)go
the search does **not** return the row.
However, if I change the where clause to remove the test of testType:
select * from tblTest where utcTimestamp < '1/1/2004 0:0:0' and utcTimestamp > '1/1/2003 0:0:0'go
it works.
Also, strangely, if I populate the table with a number of records with different dates and execute the following search:
select * from tblTest where utcTimestamp > '1/1/2004 0:0:0' and testType = 100go
I get records from **earlier** than 1/1/2004 (i.e. like the sense of the compare is wrong)
Finally, as I was writing this report, I discovered that all of these problems go away if the DESC is removed from the indexes - so that's my workaround, but it still looks like a bug.
Thanks
Lionel
Imagine the following scenario-
Identity(1,1) column ID is primary key and only clustered index key.
Rows will be inserted regularly into this table, hundreds per day.
Queries will be mostly selecting on the most recent records.
In a year, the row will have half a million records or so and only the most recent records will be used. There will be a forward-rolling hot spot, of most recent records.
Does the direction of the ID column in the clustered index make a difference?
I'm thinking no, because query plan will go to that leaf in an index seek regardless of whether it is old or new, "bottom" or "top" of index, especially if the query is very specific on the ID.
I've read this
http://mattadamson.blogspot.com/2005/05/choosing-between-ascending-or.html
but it didn't address (or perhaps didn't need to) this sort of scenario.
Hi,
We recently migrated our application from SQL Server 2000 to SQL Server 2005 and we also migrated from WAS5 to WAS6.
We are getting the following exception when we call any of the stored procedures.
The server failed to resume the transaction. Desc200000003.
Basically we call these stored procedures frequently using the Java thread scheduler.
We didnt face any problem when we use IBM drivers
I guess this is the problem with the drivers.We use JDBC 2005 driver Version 1.1.
But we didnt face this problems when we use JDBC 2000 driver when we used to connect to SQL Server 2000
The Microsoft says that it has fixed the date problems as well as the transaction problems in the version 1.1.But still are facing the issues.
Can anyone of you please help me in getting the solution for the same?
I have a SSIS package that reads data from a dump table, runs a custom script that takes date data and converts it to the correct format or nulls and formats amt fields to currency, then inserts it to a new table. The new table redirects insert errors. This process worked fine until about 3 weeks ago. I am processing just under 6 million rows, with 460,000 or so insert errors that did give error column and code.
Now, I am getting 1.5 million errors. and nothing has changed, to my knowledge. I receive the following information.
Error Code -1071607685 Error Column 0 Error Desc No status is available.
The only thing I can find for the above error code is
DTS_E_OLEDBDESTINATIONADAPTERSTATIC_UNAVAILABLE
To add to the confusion, I can not see any errors in the data written to the error table. It appears that after a certain point is reached in the processing, everything, or most records, error out.
Any help is appreciated.
Thanks
Derrick
Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 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
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?
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 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?
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
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!
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
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?
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 RelatedHi,
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?
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,
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?
I have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?
View 4 Replies View Related