Postgres Pro
Downloads
Home   >   mailing lists

Thread: SQL Order Question

SQL Order Question

From
Date:
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

Re: SQL Order Question

From
"Andrej Ricnik-Bay"
Date:
On 5/5/06, operationsengineer1@yahoo.com <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

Re: SQL Order Question

From
Date:
> On 5/5/06, operationsengineer1@yahoo.com
> <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

Re: SQL Order Question

From
Bruno Wolff III
Date:
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.

Re: SQL Order Question

From
Richard Broersma Jr
Date:
> > On 5/5/06, operationsengineer1@yahoo.com
> > <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,

Re: SQL Order Question

From
Date:
> > > > 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

Re: SQL Order Question

From
Bruno Wolff III
Date:
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.

Re: SQL Order Question

From
Date:
> 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

Re: SQL Order Question

From
George Weaver
Date:
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
>