order by where....

Discussion in 'ASP General' started by Joey Martin, Aug 18, 2004.

  1. Joey Martin

    Joey Martin Guest

    Pleas ehelp me with this.

    I am performing a basic sql query.

    select * from tablename order by featured asc, id asc

    BUT...I want all those listings with FEATURED as a Y to show up first.
    All other listings should show up afterwards.

    How do I do this? I may be thinking too hard about it.


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joey Martin, Aug 18, 2004
    #1
    1. Advertising

  2. Joey Martin wrote:
    > Pleas ehelp me with this.
    >
    > I am performing a basic sql query.
    >
    > select * from tablename order by featured asc, id asc
    >
    > BUT...I want all those listings with FEATURED as a Y to show up first.
    > All other listings should show up afterwards.
    >
    > How do I do this? I may be thinking too hard about it.
    >

    What database? Type and version please.
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Aug 18, 2004
    #2
    1. Advertising

  3. Joey Martin wrote:
    >
    > select * from tablename order by featured asc, id asc
    >
    > BUT...I want all those listings with FEATURED as a Y to show up first.
    > All other listings should show up afterwards.


    This will work if [Featured] is CHAR(1):

    ORDER BY CHARINDEX(Featured,'Y') DESC


    In general, you can use this technique on any CHAR/VARCHAR type if you
    muliply the crossed CHARINDEX values:

    SELECT * FROM States
    ORDER BY
    CHARINDEX(Name,"Virginia") * CHARINDEX("Virginia",Name) DESC,
    Name ASC


    The multiplication prevents false positives such as "West Virginia".



    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms. Please do not contact
    me directly or ask me to contact you directly for assistance. If your
    question is worth asking, it's worth posting.
    Dave Anderson, Aug 18, 2004
    #3
  4. Dave Anderson wrote:
    > Joey Martin wrote:
    >>
    >> select * from tablename order by featured asc, id asc
    >>
    >> BUT...I want all those listings with FEATURED as a Y to show up
    >> first. All other listings should show up afterwards.

    >
    > This will work if [Featured] is CHAR(1):
    >
    > ORDER BY CHARINDEX(Featured,'Y') DESC
    >

    Only if he is using SQL Server ...

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Aug 18, 2004
    #4
  5. Bob Barrows [MVP] wrote:
    > Dave Anderson wrote:
    >> Joey Martin wrote:
    >>>
    >>> select * from tablename order by featured asc, id asc
    >>>
    >>> BUT...I want all those listings with FEATURED as a Y to show up
    >>> first. All other listings should show up afterwards.

    >>
    >> This will work if [Featured] is CHAR(1):
    >>
    >> ORDER BY CHARINDEX(Featured,'Y') DESC
    >>

    > Only if he is using SQL Server ...


    Or Sybase.

    But I should think instr() would do the job in Oracle or MySQL...




    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms. Please do not contact
    me directly or ask me to contact you directly for assistance. If your
    question is worth asking, it's worth posting.
    Dave Anderson, Aug 19, 2004
    #5
  6. Dave Anderson wrote:
    > Bob Barrows [MVP] wrote:
    >> Dave Anderson wrote:
    >>> Joey Martin wrote:
    >>>>
    >>>> select * from tablename order by featured asc, id asc
    >>>>
    >>>> BUT...I want all those listings with FEATURED as a Y to show up
    >>>> first. All other listings should show up afterwards.
    >>>
    >>> This will work if [Featured] is CHAR(1):
    >>>
    >>> ORDER BY CHARINDEX(Featured,'Y') DESC
    >>>

    >> Only if he is using SQL Server ...

    >
    > Or Sybase.
    >
    > But I should think instr() would do the job in Oracle or MySQL...


    Maybe. But my point is that the answer depends on the database being used,
    which information was not provided by the OP.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Aug 19, 2004
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Shixx
    Replies:
    0
    Views:
    926
    Shixx
    Jun 8, 2004
  2. Neil Zanella
    Replies:
    1
    Views:
    484
    Jonathan Bromley
    Oct 17, 2003
  3. Soren Kuula
    Replies:
    2
    Views:
    496
    Soren Kuula
    Feb 1, 2004
  4. cspoh
    Replies:
    0
    Views:
    244
    cspoh
    Jul 31, 2003
  5. Stephan Kämper
    Replies:
    2
    Views:
    232
    Stephan Kämper
    Jan 18, 2004
Loading...

Share This Page