Order a query except one record...

[Albano]

Hi,

I have this query, and I need to order by all by a field except a particular text.

this is the query, I qhant to order all by "CLINOME", but not include in the order the text 'Mostrar Tudo'.

SELECT NULL AS USRLOGIN, NULL AS CLICOD, 'Mostrar Tudo' AS CLINOME
UNION ALL
SELECT CLIENTES.USRLOGIN,CLIENTES.CLICOD, CLIENTES.CLINOME
FROM CLIENTES
INNER JOIN VISITAS
ON CLIENTES.CLICOD=VISITAS.CLICOD
WHERE CLIENTES.CLIACT = 1
AND CLIENTES.USRLOGIN = (CASE WHEN @pUSRLOGIN IS NULL THEN CLIENTES.USRLOGIN ELSE @pUSRLOGIN END)
AND CLIENTES.TIPCOD = (CASE WHEN @pTIPCOD IS NULL THEN CLIENTES.TIPCOD ELSE @pTIPCOD END)
ORDER BY CLINOME

Thanks

 

[hmckillop]

Where in the returned list should 'Mostrar Tudo' appear then

e.g. if you have CLINOME data of
abc
def
ghi
etc

Where in this should ''Mostrar Tudo'' fit?

What you are asking doesnt really make sense.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Albano]

''Mostrar Tudo'' appear at the begin

e.g. if you have CLINOME data of
Mostrar Tudo
abc
def
ghi
etc

this is for populate a combo box, for a creteria to do a report, and 'Mostrar Tudo'(show all data)

 

[r937]

ORDER BY case when CLINOME='Mostrar Tudo'
then 0 else 937 end, CLINOME


r937.com | rudy.ca

 

[hmckillop]

excellent solution rudy, worth a star.

"I'm living so far beyond my income that we may almost be said to be living apart