This is the output as it is written in clear text from the SQL SELECT command.
Is there a simple way to put things at the end rather than at the beginning!?
As you may figure, I’m lazy here and using stars would just be a simple way to solve it…
Is it possible, in SQL SELECT, to ORDER BY and use names without stars and names with stars!?
(Excuse me for the poor English! What is the * -sign called in compatible English…!?)
SELECT name FROM table WHERE name not in (‘Bugreport’, ‘Feedback’, ‘Wishlist’) order by name
UNION
SELECT name FROM table WHERE name in (‘Bugreport’, ‘Feedback’, ‘Wishlist’) order by name
Norman! No offence, but such solution is not very dynamic…! But yes, it works…!
' ## UNION
SQL = "SELECT strName FROM [support] WHERE intParentID = 0 AND strName NOT LIKE '%*%' " _
& " UNION " _
& " SELECT strName FROM [support] WHERE intParentID = 0 AND strName LIKE '%*%' ORDER BY strName "
Thank you!
It works! Sort of! But the result is the same as I show above! The stars comes first, the names after.
I also tried: SELECT s1.strName FROM [support] s1 … --> ORDER BY s1.strName, s2.strName
I also tried: SELECT strName as Name1 FROM [support] … --> ORDER BY Name1, Name2
Both failing.
I must leave the office now.
In the mean while… I’ll think of Plan B!
[quote=215849:@Norman Palardy]SELECT name FROM table WHERE name not in (‘Bugreport’, ‘Feedback’, ‘Wishlist’) order by name
UNION
SELECT name FROM table WHERE name in (‘Bugreport’, ‘Feedback’, ‘Wishlist’) order by name[/quote]
Probably depends on your db engine. The two uses of order by won’t work in SQL Server:
select top 5 fname
from T_CUSTOMER
where LEFT(fname,1) = 'c'
and LEN(fname) > 7
order by fname <----- Incorrect syntax error
union
select top 5 fname
from T_CUSTOMER
where LEFT(fname,1) = 'b'
and LEN(fname) > 7
order by fname
This works but it combines the two queries first and then sorts them:
select top 5 fname
from T_CUSTOMER
where LEFT(fname,1) = 'c'
and LEN(fname) > 7
union
select top 5 fname
from T_CUSTOMER
where LEFT(fname,1) = 'b'
and LEN(fname) > 7
order by fname
I think what you’re missing is that in the OP he does say those * do not exist but if he put them in things WOULD sort in that order :)[quote=215813:@Jakob Krabbe]Is it possible, in SQL SELECT, to ORDER BY and use names without stars and names with stars!?[/quote]
SELECT strName,
sort_ord = case when strName like 'Bugreport%' or strName like 'Feedback%' or strName like 'Wishlist%' then 2 else 1 end
FROM [support]
WHERE intParentID = 0
order by sort_ord, strName
[quote=215875:@Jeff Tullin]Does this engine have a replace?
Maybe sort by the name after replacing the star space by zzzz to put it at the end?
SELECT strName FROM [support] WHERE intParentID = 0 order by replace(strName,’* ',‘ZZZZ’);[/quote]
There’s NO star space
Thats a manual hack he used to make it sort that way but he would rather NOT do that
[quote=215893:@Norman Palardy]@Scott Griffitts Then if there are no stars: @Jakob Krabbe Norman! No offence, but such solution is not very dynamic…! But yes, it works…!
:)[/quote]
I was trying to provide an example that could have wider use. Using order by twice in a union doesn’t work everywhere.
[code]Danish
English
Frensh
German
Norwegian
Swedish
Bugreport
Feedback
Wishlist[/code]
' ## DOUBLE TROUBLE!
SQL = "SELECT strName FROM [support] WHERE intParentID = 0 AND strName NOT LIKE '%*%' ORDER BY strName "
set mySQL = Conn.Execute(SQL)
IF NOT mySQL.EOF THEN arrArray = mySQL.GetRows()
IF IsArray(arrArray) THEN
myRow = arrArray
FOR i = 0 TO UBound(myRow,2)
response.write myRow(0,i) & "<BR>"
NEXT
END IF
SQL = "SELECT strName FROM [support] WHERE intParentID = 0 AND strName LIKE '%*%' ORDER BY strName "
set mySQL = Conn.Execute(SQL)
IF NOT mySQL.EOF THEN arrArray = mySQL.GetRows()
IF IsArray(arrArray) THEN
myRow = arrArray
FOR i = 0 TO UBound(myRow,2)
response.write myRow(0,i) & "<BR>"
NEXT
END IF
It works. It’s not beautiful, but it works!
In life, you have to choose your battles!
I usually have a column named SORTFIELD (integer) where the order is stored. It works beautiful!
But in this case, the entire support is ORDER BY date or, as in this case, name.
I think for now, this will do.
Thank you for all input and all ideas! I’ve tried many different options!
For now, Plan B ended up as the most attractive alternative…!
This is the output as it is written in clear text from the SQL SELECT command.
Is there a simple way to put things at the end rather than at the beginning!?
As you may figure, I’m lazy here and using stars would just be a simple way to solve it…
Is it possible, in SQL SELECT, to ORDER BY and use names without stars and names with stars!?
(Excuse me for the poor English! What is the * -sign called in compatible English…!?)[/quote]
If you want to sort on the asterix only in the first column, then this works:
SELECT SomeText
FROM SomeTable
ORDER BY CHARINDEX('*', LEFT(SomeText, 1)), SomeText
But yes, it works…!