Quick query question

Discussion in 'ASP General' started by Pooley, Oct 6, 2003.

  1. Pooley

    Pooley Guest

    Hi

    How do I force the order of a SQL query?

    My table has a field called ModelName which could contain 1 of 5 vehicle
    models. I want to force the query to order these in a particular order (not
    alphabetical, but an order that I have determined ('Range Rover',
    'Discovery', Land Rover', 'Jeep', 'Others')

    Let's say my current query is:
    "SELECT DISTINCT ModelName FROM tblVehicles"

    What do I need to tag on the end to order the result as stated?

    Thanks for help!

    --
     
    Pooley, Oct 6, 2003
    #1
    1. Advertising

  2. Pooley

    Tim Guest

    add another numeric field called neworder and use that



    Tim



    "Pooley" <> wrote in message
    news:...
    > Hi
    >
    > How do I force the order of a SQL query?
    >
    > My table has a field called ModelName which could contain 1 of 5 vehicle
    > models. I want to force the query to order these in a particular order

    (not
    > alphabetical, but an order that I have determined ('Range Rover',
    > 'Discovery', Land Rover', 'Jeep', 'Others')
    >
    > Let's say my current query is:
    > "SELECT DISTINCT ModelName FROM tblVehicles"
    >
    > What do I need to tag on the end to order the result as stated?
    >
    > Thanks for help!
    >
    > --
    >
    >
     
    Tim, Oct 6, 2003
    #2
    1. Advertising

  3. Pooley

    Pooley Guest

    Sure. Is that the only way to do it? Just trying to be 'elegant' about it ;)

    --

    "Tim" <> wrote in message
    news:...
    > add another numeric field called neworder and use that
    >
    >
    >
    > Tim
    >
    >
    >
    > "Pooley" <> wrote in message
    > news:...
    > > Hi
    > >
    > > How do I force the order of a SQL query?
    > >
    > > My table has a field called ModelName which could contain 1 of 5 vehicle
    > > models. I want to force the query to order these in a particular order

    > (not
    > > alphabetical, but an order that I have determined ('Range Rover',
    > > 'Discovery', Land Rover', 'Jeep', 'Others')
    > >
    > > Let's say my current query is:
    > > "SELECT DISTINCT ModelName FROM tblVehicles"
    > >
    > > What do I need to tag on the end to order the result as stated?
    > >
    > > Thanks for help!
    > >
    > > --
    > >
    > >

    >
    >
     
    Pooley, Oct 6, 2003
    #3
  4. Pooley

    Bob Barrows Guest

    That is not only the "elegant" way to do it, it is also the most efficient
    way to do it, since you can put an index on the column and get your results
    very quickly.

    There is another way, but it depends on knowing what your database is (type
    and version)

    HTH,
    Bob Barros

    Pooley wrote:
    > Sure. Is that the only way to do it? Just trying to be 'elegant'
    > about it ;)
    >
    >
    > "Tim" <> wrote in message
    > news:...
    >> add another numeric field called neworder and use that
    >>
    >>
    >>
    >> Tim
    >>
    >>
    >>
    >> "Pooley" <> wrote in message
    >> news:...
    >>> Hi
    >>>
    >>> How do I force the order of a SQL query?
    >>>
    >>> My table has a field called ModelName which could contain 1 of 5
    >>> vehicle models. I want to force the query to order these in a
    >>> particular order (not alphabetical, but an order that I have
    >>> determined ('Range Rover', 'Discovery', Land Rover', 'Jeep',
    >>> 'Others')
    >>>
    >>> Let's say my current query is:
    >>> "SELECT DISTINCT ModelName FROM tblVehicles"
    >>>
    >>> What do I need to tag on the end to order the result as stated?
    >>>
    >>> Thanks for help!
    >>>
    >>> --
     
    Bob Barrows, Oct 6, 2003
    #4
  5. Pooley

    Pooley Guest

    Hi Bob

    Thanks for that. I appreciate what you say (in fact I've already put Tim's
    method into practice) but I'm interested to know your other method.
    It's a SQL Server 2000 database.

    --
    Pooley


    "Bob Barrows" <> wrote in message
    news:OCp%...
    > That is not only the "elegant" way to do it, it is also the most efficient
    > way to do it, since you can put an index on the column and get your

    results
    > very quickly.
    >
    > There is another way, but it depends on knowing what your database is

    (type
    > and version)
    >
    > HTH,
    > Bob Barros
    >
    > Pooley wrote:
    > > Sure. Is that the only way to do it? Just trying to be 'elegant'
    > > about it ;)
    > >
    > >
    > > "Tim" <> wrote in message
    > > news:...
    > >> add another numeric field called neworder and use that
    > >>
    > >>
    > >>
    > >> Tim
    > >>
    > >>
    > >>
    > >> "Pooley" <> wrote in message
    > >> news:...
    > >>> Hi
    > >>>
    > >>> How do I force the order of a SQL query?
    > >>>
    > >>> My table has a field called ModelName which could contain 1 of 5
    > >>> vehicle models. I want to force the query to order these in a
    > >>> particular order (not alphabetical, but an order that I have
    > >>> determined ('Range Rover', 'Discovery', Land Rover', 'Jeep',
    > >>> 'Others')
    > >>>
    > >>> Let's say my current query is:
    > >>> "SELECT DISTINCT ModelName FROM tblVehicles"
    > >>>
    > >>> What do I need to tag on the end to order the result as stated?
    > >>>
    > >>> Thanks for help!
    > >>>
    > >>> --

    >
    >
     
    Pooley, Oct 7, 2003
    #5
  6. Pooley

    Bob Barrows Guest

    SELECT DISTINCT ModelName FROM tblVehicles
    ORDER BY
    CASE ModelName WHEN 'Range Rover' THEN 0 ELSE 1 END,
    CASE ModelName WHEN 'Discovery' THEN 0 ELSE 1 END,
    CASE ModelName WHEN Land Rover' THEN 0 ELSE 1 END,
    CASE ModelName WHEN 'Jeep' THEN 0 ELSE 1 END

    Again, this will be inefficient, since a table or index scan will be
    required. However, with a small number of records, it may perform
    acceptably, since a scan will probably be done anyways with a small set of
    records.

    Bob Barrows

    PS. I consider the original solution, the data-driven solution, to be the
    more elegant since it can be modified at will without touching any of your
    query code.

    Pooley wrote:
    > Hi Bob
    >
    > Thanks for that. I appreciate what you say (in fact I've already put
    > Tim's method into practice) but I'm interested to know your other
    > method.
    > It's a SQL Server 2000 database.
    >>>>> My table has a field called ModelName which could contain 1 of 5
    >>>>> vehicle models. I want to force the query to order these in a
    >>>>> particular order (not alphabetical, but an order that I have
    >>>>> determined ('Range Rover', 'Discovery', Land Rover', 'Jeep',
    >>>>> 'Others')
    >>>>>
    >>>>> Let's say my current query is:
    >>>>> "SELECT DISTINCT ModelName FROM tblVehicles"
    >>>>>
    >>>>> What do I need to tag on the end to order the result as stated?
    >>>>>
    >>>>> Thanks for help!
    >>>>>
    >>>>> --
     
    Bob Barrows, Oct 7, 2003
    #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. JKop
    Replies:
    11
    Views:
    921
  2. bermanbp

    Quick SQL 2000 Query Help Needed

    bermanbp, Oct 7, 2008, in forum: .NET
    Replies:
    0
    Views:
    255
    bermanbp
    Oct 7, 2008
  3. Jonathan N. Little

    Re: Quick query re: png transparency and ie

    Jonathan N. Little, Jun 18, 2009, in forum: HTML
    Replies:
    2
    Views:
    359
    Jonathan N. Little
    Jun 19, 2009
  4. Synbot
    Replies:
    0
    Views:
    386
    Synbot
    Jun 19, 2009
  5. jazzman519

    a quick query!

    jazzman519, Oct 15, 2009, in forum: VHDL
    Replies:
    0
    Views:
    516
    jazzman519
    Oct 15, 2009
Loading...

Share This Page