The order of the output, SQL(??)

[code]* Bugreport (0) … Trash **** >> Select, view

  • Feedback (0) … Trash **** >> Select, view
  • Wishlist (0) … Trash **** >> Select, view
    Danish (1) … **** >> Select, view
    English (2) … **** >> Select, view
    Frensh (1) … **** >> Select, view
    German (1) … **** >> Select, view
    Norwegian (0) … Trash **** >> Select, view
    Swedish (3) … **** >> Select, view[/code]

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…!?)

if I understand the limited information provided.
you have two types of “NAMES”… one type contains stars and one type does not?

SELECT name FROM table WHERE name IS NOT LIKE "%\\*%" ORDER BY name
UNION
SELECT name FROM table WHERE name IS LIKE "%\\*%" ORDER BY name

and I THINK it requires the “” to escape the “*” which is used normally as a LIKE pattern character

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…! :slight_smile: 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! :slight_smile:

[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

Well IF this were me I’d maybe have a “ordering” column somewhere that I could simply sort by that and not the names themselves

In fact that’d be easy since it would be just adding one small table with two columns - Name and Order

Then you query would be

SELECT name FROM table ORDER BY (select order from OrderingTable where OrderingTable.name = name )

Change the ordering in the ordering table and you can make it sort however you want

Depending on the db and if you have a mid() , left(), substring() or equivalent function:

SELECT strName, sort_ord = case when substring(strName, 1, 1) = '*' then 2 else 1 end FROM [support] WHERE intParentID = 0 order by sort_ord, strName

Or a more universal option:

SELECT strName, sort_ord = case when strName like '*%' then 2 else 1 end FROM [support] WHERE intParentID = 0 order by sort_ord, strName

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]

Then if there are no stars:

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

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=215870:@Scott Griffitts]Then if there are no stars:
[/quote]

[quote=215857:@Jakob Krabbe]Norman! No offence, but such solution is not very dynamic…! :slight_smile: But yes, it works…!
[/quote]
:slight_smile:

[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…! :slight_smile: 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.

Right
A secondary ordering table does and isn’t hard coded either :slight_smile:

[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! :slight_smile:

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! :slight_smile:
For now, Plan B ended up as the most attractive alternative…!

Many people say ‘if it works, do not fix it’

But:

… if there are stars in the names, can I ask again:

Does this engine have a replace() function?

SELECT strName FROM [support] WHERE intParentID = 0 order by replace(strName,’*’,‘ZZZZ’);

care should be taken (as I mentioned in another post)… SOME SQL engines use “*” as part of the LIKE pattern

That’s a killer!

You posted above but I just didn’t understand…

[code]SQL = "SELECT strName FROM [support] WHERE intParentID = 0 ORDER BY REPLACE(strName, ‘*’, ‘zzzzz’) "

Microsoft JET Database Engine error ‘80040e14’

Undefined function ‘REPLACE’ in expression.

/support/index.asp, line 5 [/code]

Microsoft has it all! But not always when you need it! :slight_smile:

[quote=215813:@Jakob Krabbe][code]* Bugreport (0) … Trash **** >> Select, view

  • Feedback (0) … Trash **** >> Select, view
  • Wishlist (0) … Trash **** >> Select, view
    Danish (1) … **** >> Select, view
    English (2) … **** >> Select, view
    Frensh (1) … **** >> Select, view
    German (1) … **** >> Select, view
    Norwegian (0) … Trash **** >> Select, view
    Swedish (3) … **** >> Select, view[/code]

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

NB: This example assumes MSSQL (Transact SQL).