SQL Server Query?

Question

I have a table with column name Friendly_Name(varchar(250)) which displays the values for the query:

SELECT Workstations.Friendly_Name FROM Workstations WHERE Hits > 0 AND (Workstations.Friendly_Name LIKE '%') ORDER BY Workstations.Friendly_Name

192.168.5.1
192.168.5.10
192.168.5.11
192.168.5.111
192.168.5.112
cosupport1\admin
cosupport1\administrator
testlab1\admin11
testlab1\admin12
testlab1\admin14
testlab1\admin15

...Now I want the query to dispaly the records in the following order...

cosupport1\admin
cosupport1\administrator
testlab1\admin11
testlab1\admin12
testlab1\admin14
testlab1\admin15
192.168.5.1
192.168.5.10
192.168.5.11
192.168.5.111
192.168.5.112

Iam new to SQL Server and not familiar with buil in functions in SQL Server! Please help me!

Thanks in Advance!

Answer 1

You can do this: add a column to your query that returns whether or not the Friendly_Name starts with a number. Then, order by that column, then by Friendly_Name.

I dont remember the exact syntax but it should be something like this:

SELECT Workstations.Friendly_Name, IsNumeric(Substring(Workstations.Friendly_Name, 1, 1)) N
FROM Workstations
WHERE Hits > 0 AND (Workstations.Friendly_Name LIKE '%')
ORDER BY N, Workstations.Friendly_Name

The IsNumeric will return a 1 or 0, and all records with a 0 (i.e., starting with a letter) will be returned before all those starting with a number.

Answer 2

I am familiar with MySQL rather than SQL server but the answer should be pretty much the same. First things first: as far as I know there is no way of producing the search result as you desire without some 'hack' or weird nested/joined query.

Secondly, your original search query should be:

SELECT Friendly_Name FROM Workstations WHERE Hits > 0 AND Friendly_Name IS NOT NULL ORDER BY Friendly_Name

There are two ways of sorting: Ascending (the default setting) and Descending. Ascending sorts in lexicographical (aka 'alphabetical' order, the order in which words appear in the dictionary) whilst Descending orders in the reverse.

Hence the only other way of doing your search is:

SELECT Friendly_Name FROM Workstations WHERE Hits > 0 AND Friendly_Name IS NOT NULL ORDER BY Friendly_Name DESC

But this will produce the search results as follows:

testlab1\admin15
testlab1\admin14
testlab1\admin12
testlab1\admin11
cosupport1\administrator
cosupport1\admin
192.168.5.112
192.168.5.111
192.168.5.11
192.168.5.10
192.168.5.1

Liz

Answer 3

You can use two select statements:

SELECT Workstations.Friendly_Name FROM Workstations WHERE Hits > 0 AND (Workstations.Friendly_Name LIKE '%') AND Workstations.Friendly_Name > 9 ORDER BY Workstations.Friendly_Name

This will give you all the names that come after "9" in the "alphabet," i.e. everything that starts with a letter.

SELECT Workstations.Friendly_Name FROM Workstations WHERE Hits > 0 AND (Workstations.Friendly_Name LIKE '%') AND Workstations.Friendly_Name < 9 ORDER BY Workstations.Friendly_Name

This will give you all the names that come before "9," i.e. all the IP addresses.

Answer 4

Try this

SELECT Workstations.Friendly_Name
FROM Workstations
WHERE Hits > 0 AND (Workstations.Friendly_Name IS NOT NULL)
ORDER BY case when
ASCII(Friendly_Name)
< ASCII('A') then 1 else 0 end

Answer 5

ORDER BY Workstations.Friendly_Name DESC

This will put the list in descending order.

Excpet that I don't know whether you can get the IP addresses in exactly the order you want.