Selecting a default always and return the other results after to it

[gottimukkala]

Hi I have a stored procedure that returns all the docs uploaded ordered by creation date. Now i want to change it like show a document with the id "1234" as always on top and the remaining results as today after to that document. For example: Today we have stored procedure that returns the result as: Id Title CreationDate Lang 1pqr About obama 24/02/2009 en xyz You and Me 10/01/2009 abc KnowHow 01/12/2008 de 12345 Yes And No 29/10/2008 de Now I want the result like: Id Title CreationDate Lang xyz You and Me 10/01/2009 3prt Stadt 25/02/2009 sr 1pqr About obama 24/02/2009 en abc KnowHow 01/12/2008 de 12345 Yes And No 29/10/2008 de Does anyone have any one idea. Any Help please......! Thanks in advance

[Rob Philpott]

So, is the document with id '12345' distinct in some way to all the others? If that's what you mean, you could create a temporary table, insert the 12345 record into it, and then insert the results of the existing query (minus the 12345) into it as well. Then, just return the temporary table.

Regards, Rob Philpott.

[Wendelius]

This could be done in several ways. One way is that you use a simple union statement. Something like:

SELECT 1, Id, Title, CreationDate, Lang
FROM TableName
WHERE Id = 12345
UNION ALL
SELECT 2, Id, Title, CreationDate, Lang
FROM TableName
WHERE Id <> 12345
ORDER BY 1

The WHERE clause would actually contain a relevant condition. I take it that you don't want to use id as a condition, but for example MAX(CreationDate) or something else. ALso the ORDER BY could have more columns on it.

The need to optimize rises from a bad design.My articles[^]

[WoutL]

SELECT Id, Title, CreationDate, Lang
FROM TableName
order by case when Id = 12345 then 0 else 1 end, CreationDate

Wout Louwers