select query for a single table_ORDER BY


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: select query for a single table_ORDER BY

  1. #1
    Join Date
    Mar 2009
    Posts
    4

    Angry select query for a single table_ORDER BY

    Hi,
    I am having a table with two columns as follows.
    Name Desc
    ------ ----------

    USA United States Of America
    IND Republic of India
    USSR Union Of Soviet Socialist Republics
    Canada
    AFG Afghanisthan


    My requirement is that ,I am having the following select query.
    Select Desc,Name from tablename ORDER BY Desc.
    I want to show only the Desc data in the UI.
    But in case if the Desc is null,I will show the "Name" in the UI.
    So in the above table,for Canada,since Desc is null,I will show the "Name".
    But the problem I am facing is that the above select query returns an ordered set of data with the value "Canada" at the top.
    I want a sorted data sorted by Desc values,but in case Desc is nul,I want Name value to be taken instead .
    When sorted i want the values as follows.


    Afghanisthan
    Canada
    Republic of India
    Union Of Soviet Socialist Republic
    United States Of America


    Can anybody help?


    thanks

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    What type of database are you working with?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Mar 2009
    Posts
    4
    Hi,
    I am using MSSQL Server

    thanks

  4. #4
    Join Date
    Dec 2003
    Posts
    2,750
    How about the following:

    ISNULL(Desc, Name) AS 'Description' ORDER BY Description
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  5. #5
    Join Date
    Mar 2009
    Posts
    4
    Hi,

    I am new to SQL.Could you please give me the entire query?

    Thanks
    Vinod

  6. #6
    Join Date
    Mar 2009
    Posts
    4

    Smile

    Hi Paul,

    Thanks for the support.
    I got an idea from another site.
    I used the following query and it is working.
    SELECT Name,Descr FROM tablename ORDER BY (CASE WHEN Descr = '' THEN Name ELSE Descr END)

    thanks
    Vinod

  7. #7
    Join Date
    Nov 2008
    Posts
    2

    Put name between brackets...

    Hello,
    You can do this:
    Select [Desc],Name from tablename ORDER BY Desc.

    Notice, you can put between brackets all fieldname, usually we do not do that
    but when field's name is not allowed by SQL (because is the same as some syntax word... or has not allowed characters...) we must put the name between brackets.

    It works also in SQL and MsAccess

    Exaple: we have a field called: Start Date
    Name has a space, so SQL des not know if it is 1 field or 2 fields without separator so:
    select Start Date from Table
    will raise an error

    But:
    select [Start Date] from Table
    will work

    Hope this help,
    José Sánchez Carvajal
    Last edited by Hack; 03-26-2009 at 08:01 AM.

  8. #8
    Join Date
    Dec 2003
    Posts
    2,750
    Quote Originally Posted by vindhvp View Post
    Hi,

    I am new to SQL.Could you please give me the entire query?

    Thanks
    Vinod
    SELECT Name, ISNULL(Descr, Name) As 'Descr' FROM tablename ORDER BY Descr
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Similar Threads

  1. cost of select query
    By vishalg in forum Database
    Replies: 1
    Last Post: 01-12-2009, 08:01 AM
  2. Multi-table Select Query using Joins
    By knightsg in forum Database
    Replies: 0
    Last Post: 02-16-2005, 08:07 AM
  3. Trouble with calling Access select query
    By Greg Busby in forum VB Classic
    Replies: 2
    Last Post: 12-22-2002, 12:24 AM
  4. Access - Select query
    By jeppe in forum VB Classic
    Replies: 1
    Last Post: 08-13-2002, 05:14 AM
  5. Select into Delete Query
    By Darrick in forum Database
    Replies: 2
    Last Post: 08-29-2001, 01:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center