FAQ
hi all,

i want to order contract numbers id ascending order,
with the exception of 'BTS' that I want to display
first. the contract numbers are numeric and 'BTS' is
text. the numeric values come first when i sort in
asc order - as expected. how can i get BTS (id = 0)
to come up as the first value in my query?

tia...

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Search Discussions

  • Andrej Ricnik-Bay at May 4, 2006 at 6:39 pm

    On 5/5/06, operationsengineer1@yahoo.com wrote:
    hi all,

    i want to order contract numbers id ascending order,
    with the exception of 'BTS' that I want to display
    first. the contract numbers are numeric and 'BTS' is
    text. the numeric values come first when i sort in
    asc order - as expected. how can i get BTS (id = 0)
    to come up as the first value in my query?
    I'm not sure I fully understand the structure of your
    data ... do BTS and the numeric contract numbers
    appear in the same column?


    tia...
    Cheers,
    Andrej


    --
    Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.

    http://www.american.edu/econ/notes/htmlmail.htm
  • Operationsengineer1 at May 4, 2006 at 7:34 pm

    On 5/5/06, operationsengineer1@yahoo.com
    wrote:
    hi all,

    i want to order contract numbers id ascending order,
    with the exception of 'BTS' that I want to display
    first. the contract numbers are numeric and 'BTS' is
    text. the numeric values come first when i sort in
    asc order - as expected. how can i get BTS (id = 0)
    to come up as the first value in my query?
    I'm not sure I fully understand the structure of
    your
    data ... do BTS and the numeric contract numbers
    appear in the same column?
    yes.

    __________________________________________________
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com
  • Richard Broersma Jr at May 4, 2006 at 7:40 pm

    On 5/5/06, operationsengineer1@yahoo.com
    wrote:
    hi all,

    i want to order contract numbers id ascending order,
    with the exception of 'BTS' that I want to display
    first. the contract numbers are numeric and 'BTS' is
    text. the numeric values come first when i sort in
    asc order - as expected. how can i get BTS (id = 0)
    to come up as the first value in my query?
    I'm not sure I fully understand the structure of
    your
    data ... do BTS and the numeric contract numbers
    appear in the same column?>
    yes.
    Perhaps you could union two selects together?

    Regards,
  • Operationsengineer1 at May 4, 2006 at 8:12 pm

    hi all,

    i want to order contract numbers id ascending order,
    with the exception of 'BTS' that I want to
    display
    first. the contract numbers are numeric and
    'BTS'
    is
    text. the numeric values come first when i
    sort
    in
    asc order - as expected. how can i get BTS
    (id =
    0)
    to come up as the first value in my query?
    I'm not sure I fully understand the structure of
    your
    data ... do BTS and the numeric contract numbers
    appear in the same column?>
    yes.
    Perhaps you could union two selects together?
    almost, but not quite. if i use an order by, it
    orders the entire output, not just the results of the
    2nd select - so i'm back where i started. that is,
    unless i'mmissing a piece to this puzzle.

    __________________________________________________
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com
  • Bruno Wolff III at May 4, 2006 at 8:19 pm

    On Thu, May 04, 2006 at 13:11:51 -0700, operationsengineer1@yahoo.com wrote:

    almost, but not quite. if i use an order by, it
    orders the entire output, not just the results of the
    2nd select - so i'm back where i started. that is,
    unless i'mmissing a piece to this puzzle.
    You need to order by column <> 'BTS', column converted to a number
    Doing the column to a number part is a bit tricky, but can be done
    with case as I mentioned previously.
  • Bruno Wolff III at May 4, 2006 at 7:39 pm

    On Thu, May 04, 2006 at 11:33:33 -0700, operationsengineer1@yahoo.com wrote:
    hi all,

    i want to order contract numbers id ascending order,
    with the exception of 'BTS' that I want to display
    first. the contract numbers are numeric and 'BTS' is
    text. the numeric values come first when i sort in
    asc order - as expected. how can i get BTS (id = 0)
    to come up as the first value in my query?
    You can first order by column <> 'BTS'. Ordering by the numeric values is
    trickier, but you should be able to have a case statement with a regular
    expression that checks for valid numbers and in that case return the string
    casted to numeric and for the invalid case return a constant numeric.
  • George Weaver at May 4, 2006 at 9:08 pm
    You might be able to achieve what you want using the fact that Order By
    sorts false before true, so:

    Order By not(contractid = 'BTS'), contractid ASC

    Regards,
    George

    ----- Original Message -----
    From: <operationsengineer1@yahoo.com>
    To: <pgsql-novice@postgresql.org>
    Sent: Thursday, May 04, 2006 1:33 PM
    Subject: [NOVICE] SQL Order Question

    hi all,

    i want to order contract numbers id ascending order,
    with the exception of 'BTS' that I want to display
    first. the contract numbers are numeric and 'BTS' is
    text. the numeric values come first when i sort in
    asc order - as expected. how can i get BTS (id = 0)
    to come up as the first value in my query?

    tia...

    __________________________________________________
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    ---------------------------(end of broadcast)---------------------------
    TIP 3: Have you checked our extensive FAQ?

    http://www.postgresql.org/docs/faq
  • Operationsengineer1 at May 4, 2006 at 8:21 pm

    You might be able to achieve what you want using the
    fact that Order By
    sorts false before true, so:

    Order By not(contractid = 'BTS'), contractid ASC

    Regards,
    George

    ----- Original Message -----
    From: <operationsengineer1@yahoo.com>
    To: <pgsql-novice@postgresql.org>
    Sent: Thursday, May 04, 2006 1:33 PM
    Subject: [NOVICE] SQL Order Question

    hi all,

    i want to order contract numbers id ascending order,
    with the exception of 'BTS' that I want to display
    first. the contract numbers are numeric and 'BTS' is
    text. the numeric values come first when i sort in
    asc order - as expected. how can i get BTS (id = 0)
    to come up as the first value in my query?

    tia...
    George, you are on the money... this does it:

    SELECT contract_id, contract_number FROM t_contract
    ORDER BY not(contract_number = 'BTS'), contract_number
    ASC

    thanks to all who helped.

    __________________________________________________
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedMay 4, '06 at 6:33p
activeMay 4, '06 at 9:08p
posts9
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase