1. We have moved to a new forum! There may be a few things not working properly so please let us know if you find a bug. Remember to use the bbCode [ sql ] tag for SQL statements.
New XenForo forum! Google Ads

How to order rows in a predefined pattern?

Discussion in 'SQL - Questions and Answers' started by ben, May 13, 2006.

  1. ben Administrator

    The idea is to always have predefined rows at the beginning of a record set.

    For example if you have a column:

    id
    --------
    article_1
    article_2
    article_3

    How can I construct a query to return the rows in the order article_2, article_1, article_3?
    ben, May 13, 2006
    #1

  2. ben Administrator

    Dialect: MySQL
    This is actually a problem I had when working on the articles module for this site.

    I wanted the "README" and the "FEEDBACK" articles to always be at the top of the list and the other articles ordered in descending order.

    This is what I did:
    SQL:

    
SELECT * FROM articles ORDER BY id = 1 DESC, id = 2 DESC, id DESC

    I also tried:
    SQL:

    
SELECT * FROM articles ORDER BY id IN (1,2) DESC, id DESC

    but that would place article 2 in front of article 1.
    ben, May 13, 2006
    #2

  3. Umesha N Shetty Guest

    Dialect: Access
    In a similar situation, I derived a newid based on the id by padding extra characters in the beginning.
    e.g. README, new id was ZZZREADME
    and FEEDBACK, new id was ZZYFEEDBACK
    and all other's newid was as is.

    SQL:

    
Following solution not tested.


    SQL:

    
Function NewId(Id as Variant) as Variant
    
    NewId = id
    
    If id = README then
    
       NewId = "ZZZ" &  id
    
    end if
    
    If id = FEEDBACK then
    
       NewId = "ZZY" &  id
    
    end if
    
End Function

    SQL:

    
SELECT * FROM articles ORDER BY NewId(id) DESC
    Umesha N Shetty, May 20, 2006
    #3

Share This Page