Equivalent Of ORDER BY [field] IN (…) In SQL Server
May 19, 2005
Hi,
I have small question regarding ORDER BY clause.
I wanted some value of the fields should come first before other values….Something like:
+----------+----------------------------------------+
| code
| name
|
+----------+----------------------------------------+
| UK
| United Kingdom
|
| US
| United States
|
| AF
| Afghanistan
|
| AL
| Albania
|
| DZ
| Algeria
|
| AS
| American Samoa
|
I want to display country UK and US code first and then rest of the countries using SQL statement. I know in MySQL, I can use ORDER BY code IN (...)
--SQL
SELECT * FROM countries ORDER by code IN ('UK', 'US') desc
But not sure how can I do same thing in SQL Server. Is there any equivalent of ORDER BY [field] IN (…) clause in SQL Server???
Though I can create a new field something like sequence and use it to display these countries but I can not do that…
Please advice.
I have developed a web database application using ASP and MS Access, however the requirement for hosting the application is that it must use an MS SQL Server database. I converted the database to SQL without any problems, and many features of the application work under SQL Server except the 'add record' function. I realised there isn't an 'autonumber' field in SQL Server (which i use as the primary key for many tables), but an 'int' field. I considered pulling out the latest int from the database, incrementing it manually and adding the new record with this number... i also noticed there is a 'unique identifier' field.
Does ORDER BY work on character data type in SQL Server through ODBC? I tried using the SQL Query Tool in SQL Enterprise Manager and it works but using through ODBC I can't get any results.
Query: SELECT company_id, company_name FROM lt_company ORDER BY company_name
In my SQL 2005 database table Records, I have 3 fields, field1, field2, and field3 which are all nvarchar(50) fields. The value of field2 is something like this, MDB006-MD002-0004-3-2007. I would like to order this field but only use the 0004-3-2007 part of the field to order it. Is it possible to put the last 11 charachters (0004-3-2007) in another field and then order it using this new field?
Dear all,In SQL Server 2000 , how to get distinct records sort by onefield .ExampleSELECT DISTINCT A FROM tblTEST ORBER BY BHere, In TableField 'A' contain more than one same data...Field 'B' contain all are different Data......I want distince in Field 'A' and order by Field 'B'..... how to getit.........regardskrishnan
How can I "Order By" the second + third characters of a 7 char field ?
Sample data looks like:
LCA - L
LCB - L
LCF - M
LCE - M
LCE - A
LCA - A
LCB - A If I order by the whole field I get:
LCA - A
LCA - L
LCB - A
LCB - L
LCE - A
LCE - M
LCF - L
LCF - M What I want is:
LCA - L
LCB - L
LCF - L
LCA - A
LCB - A
LCE - A
LCE - M
LCF - M I'm still at the stage in this project were I can 'split' the field (if I have to) into first 3 and last 1. But the sort order of the last 1 is not alphabetic (I want 'L', 'A', "M"). Can I substitute a custom SortOrder some way ? (I've done that with mainframe Cobol). All suggestions appreciated. Thanks Roger
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
Hi AllI am finding unexpected results when inserted into a newly createdtable that has a field of datatype int identity (1,1).Basically the order I sort on when inserting into the table is notreflected in the order of the values from the identity field.Have I been wrong in assuming that it should reflect the order from thesort?The code is ...create table tmp (A varchar(50), L float, C int identity(1,1))insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Lland I don't understand why the values in tmp.C aren't in the ordersuggested by the sort.Any comments most appreciatedBevan
Does anyone knows what is the evaluation order for expressions attached to "Value" property and "BackgroundColor" property for a field.
My problem is if a field is being changed in its value property to a particular value, will this changed value be recognised immediately by the Backgroundcolor property during its rule evaluation, it does not seem to be the case.
We have a web application (database) that uses one field called Application and another called TicketType.
When a user fills out a ticket they can choose up to 3 levels of this field. Eg Application, Application2, Application3
Eg TicketType, TicketType2, TicketType3
The extra two levels not being compulsory.
I am using sql server 2005 // Reporting Services
My query is as below: SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2], Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2], TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName FROM Ticket INNER JOIN TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID WHERE (Ticket.CreatedDate >= @StartDate) ORDER BY Ticket.TicketNumber
End result looks like this:
Application
App 2
App 3
TicketType
Type 2
Type 3
Software
Internal Apps
proACT
SW Other
Office Issues
General
Application
Click Track server
Alert (App)
Service
Network
Other
Network Fault
Software
Internal Apps
Other
User Account
New
Hardware
Network
HW Fault
Application
Click Track server
Alert (App)
Disk space
Office Issues
General
proACT
Configuration
Deployment
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Software
Server Software
SharePoint
SW Fault
App Failure (Function)
Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list. Example ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions? Thanks - Mark
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.
I have 2 SQL statements in Visual Basic(with sybase as backend) 1) "set option DBA.MAX_STATEMENT_COUNT = 1069999900" 2) "set option DBA.MAX_CURSOR_COUNT = 1069999900"
And when I migrated the DB from Sybase to SQL server and try to run the vb code it is giving me error in that SQL statement as MS SQL server might not be recognising the above two statements. Is there an equivalent of this in SQL server. Thanks
So far, I have only used Access which has an autonumber data type so that in some of my tables the id is automatically generated. I guess this is a simple question but is there an equivalent data type in sql server?
Hello all, I'm making a query for this application that is not in the same server where the database is located.
This query I can easily get results when making the query within the server:
USE ADW_Publish; SELECT distinct b.Subject FROM dbo.F_Class_Exam a LEFT OUTER JOIN dbo.D_Course_Catalog b ON a.Course_ID = b.Course_ID WHERE a.Term = '$536870994$' AND a.Class_Exam_Type = 'FIN' AND b.Term = '$536870994$' ORDER BY b.Subject
Here are the details: Servername: SERVER01
What would an equivalent query be if I apply this to an external application (located outside the server?)
I've gotten some hints to use: SELECT * FROM OPENDATASOURCE('MSDASQL', 'DataSource=DataSourceName;UserID=User;Password=SomePassword').DBName.dbo.TableName
But I dont know how to apply the query above to use that..
Any thoughts? Or are there other ways of doing it?
I am migrating a student database from Access to SQL Server. In AccessI have a query that displays grade information (grades are calculatedon a 12-point scale). In the query I average the students' scores andstore it in a column called Avg. I look up and display the equivalentgrade letter using Access' DLookup function from a table calledGradeTable_tbl. Here is how it's built in Access:Grade: DLookUp("[grade_letter]","GradeTable_tbl","[grade_num]= " &Int([Avg]))Here is the structure of the GradeTable_tbl:grade_num grade_letter0 F1 F2 D-3 D......10 B+11 A-12 AHow would I do the same thing in SQL Server? I want my output to besomething like:Student Score1 Score2 Score3 Avg GradeBob 12 10 8 10 B+Nancy 12 11 11 11 A-etc...I appreciate your feedback!-Paul------"You never know enough to know you don't know"
I am trying to convert a complex function from Oracle to SQL Serverand have come across Oracle's Instr() function. I see SQL Server hasCHARINDEX() which is similar, however it does not provide some keyfunctionality I need. Here is an example of the Oracle code:if Instr( sTg , cDelim, 1, 3 ) > 0 thensd := SubStr( sTg, Instr( sTg , cDelim, 1, 1 ) + 1, Instr( sTg,cDelim, 1, 2 ) - Instr( sTg , cDelim, 1, 1 ) - 1)end if;Has anybody converted anything similar to this within SQL Server? Anyhelp is GREATLY appreciated!Thanks.
All,Oracle 9i provides a "USING" clause option for inner joins, thatallows me to say:SELECT * FROM TBL1 JOIN TBL2 USING KeyColumnassuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirablefor our software make use of, but we also support SQL Server. Thereis no USING option available, andSELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumncauses an ambiguous column error on KeyColumn.Is there any equivalent to this Oracle functionality on SQL Server?KingGreg
Hello, I'm trying to build a integration service package importing data from XML files and directs this data to different MS SQL server 2005 Database tables. Can someone suggest me what is equivalent(mapping) data type of DT_UI8 in Sql server 2005 for Integration Services.
Or how to consume DT_UI8 fields in SQL server 2005.
Hi, I've been looking for the equivalent of the MS Access 2003 field caption property in SQL but it appears as though there isn't one. (The caption property gives a friendly name on form labels rather than the field name) How would I do this in SQL so I don't have to overtype all the column names? Could it be with Views? Any pointers appreciated - I'm moving & learning from Access to SQL
Is there an equivalent to Oracle's ROWNUM in SQL Server. ROWNUM, when added to a select statement as a column - the query would return an automatic counter, numbering each row returned.
There are three columns I need to query and rank, then group and total. I'm using MS SQL server 2000 and it doesn’t have a ranking function like the newer SQL server and oracle has. Does anyone have a clever way to create or simulate a ranking function? Thanks so much! Jake :confused:
I'm trying to get a tree structure from a table. Ex : Security table. How can I accomplish that task in SQL Server?? I will do it using Level and connect by operators in Oracle to accomplish the task. Your reply in this regard is highly appreciated. Thanks, Navy
I have a SQL Server database which has one user (UserA) which owns some tables. I've added an additional user (UserB) to the database such that it has access to the tables owned by UserA. What is happening is that when I log on as UserB I have to fully qualify table names and fields in my SQL statements when I deal with tables owned by UserA. Is there a way make the tables accessible without specifying the owner? In Oracle you could create a public synonym for the table eg. <table_name>. Wherever that synonym is referenced the DBMS would know thats its refering to UserA.<table_name>. Is such functionality available in SQL Server? Thanks.
I know I can limit the output rows in SQL Server by using TOP n. But I also want to generate a sequence no. The identity property of SQL Server will not be usefull here because my actaul WHERE clause will be more complex like WHERE resigndate = '01-jan-2004'
