Professional Web Applications Themes

sorting library of congress numbers - PostgreSQL / PGSQL

I have a table where one of the columns is a library of congress number. I would like to be able to ORDER BY this column. Currently, the column is a varchar. Unfortunately, a normal string comparison will not work. First, has anyone done this already? If not, I'm thinking I would need to: 1) define a new type to hold LOC numbers. 2) write a function that determines less than, greater than It seems like I'm missing something here. I have not yet defined a new type.is defining less/greater part of that process? Is there a simpler way to ...

  1. #1

    Default sorting library of congress numbers

    I have a table where one of the columns is a library of congress number.

    I would like to be able to ORDER BY this column.



    Currently, the column is a varchar. Unfortunately, a normal string
    comparison

    will not work.



    First, has anyone done this already?



    If not, I'm thinking I would need to:

    1) define a new type to hold LOC numbers.

    2) write a function that determines less than, greater than



    It seems like I'm missing something here. I have not yet defined a new

    type.is defining less/greater part of that process?



    Is there a simpler way to do this?



    Any advice is appreciated.




    Rick Schumeyer Guest

  2. #2

    Default Re: sorting library of congress numbers

    On Wed, 2005-01-19 at 16:37, Rick Schumeyer wrote:
    > I have a table where one of the columns is a library of congress
    > number.
    >
    > I would like to be able to ORDER BY this column.
    >
    >
    >
    > Currently, the column is a varchar. Unfortunately, a normal string
    > comparison
    >
    > will not work.
    >
    >
    >
    > First, has anyone done this already?
    >
    >
    >
    > If not, I’m thinking I would need to:
    >
    > 1) define a new type to hold LOC numbers.
    >
    > 2) write a function that determines less than, greater than
    >
    >
    >
    > It seems like I’m missing something here. I have not yet defined a
    > new
    >
    > type…is defining less/greater part of that process?
    >
    >
    >
    > Is there a simpler way to do this?
    You might want to do something along the lines of storing the PARTs of
    the loc number, then using lpad/rpad in a function to put them together
    in such a way to as to render them orderable. Is there an URL on how
    LOC numbers are ordered?

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Scott Marlowe Guest

  3. #3

    Default Re: sorting library of congress numbers

    > -----Original Message-----
    > From: [email]pgsql-general-ownerpostgresql.org[/email] [mailto:pgsql-general-
    > [email]ownerpostgresql.org[/email]] On Behalf Of Scott Marlowe
    > Sent: Wednesday, January 19, 2005 5:54 PM
    > To: Rick Schumeyer
    > Cc: [email]pgsql-generalpostgresql.org[/email]
    > Subject: Re: [GENERAL] sorting library of congress numbers
    >
    > On Wed, 2005-01-19 at 16:37, Rick Schumeyer wrote:
    > > I have a table where one of the columns is a library of congress
    > > number.
    > >
    > > I would like to be able to ORDER BY this column.
    > >
    > >
    > >
    > > Currently, the column is a varchar. Unfortunately, a normal string
    > > comparison
    > >
    > > will not work.
    > >
    > >
    > >
    > > First, has anyone done this already?
    > >
    > >
    > >
    > > If not, I'm thinking I would need to:
    > >
    > > 1) define a new type to hold LOC numbers.
    > >
    > > 2) write a function that determines less than, greater than
    > >
    > >
    > >
    > > It seems like I'm missing something here. I have not yet defined a
    > > new
    > >
    > > type.is defining less/greater part of that process?
    > >
    > >
    > >
    > > Is there a simpler way to do this?
    >
    > You might want to do something along the lines of storing the PARTs of
    > the loc number, then using lpad/rpad in a function to put them together
    > in such a way to as to render them orderable. Is there an URL on how
    > LOC numbers are ordered?
    LOC numbers are described at:

    [url]http://library.dts.edu/Pages/RM/Helps/lc_call.shtml[/url]

    I thought about breaking the pieces up. I will have to think a bit more
    about whether that will work.


    ---------------------------(end of broadcast)---------------------------
    TIP 4: Don't 'kill -9' the postmaster

    Rick Schumeyer Guest

  4. #4

    Default Re: sorting library of congress numbers

    > -----Original Message-----
    > From: [email]pgsql-general-ownerpostgresql.org[/email] [mailto:pgsql-general-
    > [email]ownerpostgresql.org[/email]] On Behalf Of Mike Rylander
    > Sent: Wednesday, January 19, 2005 6:10 PM
    > To: Rick Schumeyer; PgSql General
    > Subject: Re: [GENERAL] sorting library of congress numbers
    >
    > On Wed, 19 Jan 2005 17:37:20 -0500, Rick Schumeyer <rschumeyerieee.org>
    > wrote:
    > >
    > > I have a table where one of the columns is a library of congress number.
    > >
    > > I would like to be able to ORDER BY this column.
    > >
    >
    > First off, by LOC numbers do you mean Title Control Numbers like
    > "o00325992" or "i0824506030" with an optional vendor name in front of
    > them?
    I mean the library of congress classification numbers. For example,
    the book "PostgreSQL" by Douglas & Douglas has the classification:
    QA76.9.D3 D685 2003




    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Rick Schumeyer Guest

  5. #5

    Default Re: sorting library of congress numbers

    -----BEGIN PGP SIGNED MESSAGE-----
    Hash: SHA1

    Why not write a function that converts the LOC number to an integer,
    such that sorting on those integers will sort the numbers correctly?
    Apparently you can even build an index on this type of function now, to
    accelerate the sort.

    cvt_loc(TEXT) -> INTEGER

    ORDER BY cvt_loc(loc_num)


    or something like that...

    On Jan 19, 2005, at 6:35 PM, Rick Schumeyer wrote:
    >> -----Original Message-----
    >> From: [email]pgsql-general-ownerpostgresql.org[/email] [mailto:pgsql-general-
    >> [email]ownerpostgresql.org[/email]] On Behalf Of Mike Rylander
    >> Sent: Wednesday, January 19, 2005 6:10 PM
    >> To: Rick Schumeyer; PgSql General
    >> Subject: Re: [GENERAL] sorting library of congress numbers
    >>
    >> On Wed, 19 Jan 2005 17:37:20 -0500, Rick Schumeyer
    >> <rschumeyerieee.org>
    >> wrote:
    >>>
    >>> I have a table where one of the columns is a library of congress
    >>> number.
    >>>
    >>> I would like to be able to ORDER BY this column.
    >>>
    >>
    >> First off, by LOC numbers do you mean Title Control Numbers like
    >> "o00325992" or "i0824506030" with an optional vendor name in front of
    >> them?
    >
    > I mean the library of congress classification numbers. For example,
    > the book "PostgreSQL" by Douglas & Douglas has the classification:
    > QA76.9.D3 D685 2003
    >
    >
    >
    >
    > ---------------------------(end of
    > broadcast)---------------------------
    > TIP 1: subscribe and unsubscribe commands go to
    > [email]majordomopostgresql.org[/email]
    >
    >
    - -----------------------------------------------------------
    Frank D. Engel, Jr. <fde101fjrhome.net>

    $ ln -s /usr/share/kjvbible /usr/manual
    $ true | cat /usr/manual | grep "John 3:16"
    John 3:16 For God so loved the world, that he gave his only begotten
    Son, that whosoever believeth in him should not perish, but have
    everlasting life.
    $
    -----BEGIN PGP SIGNATURE-----
    Version: GnuPG v1.2.4 (Darwin)

    iD8DBQFB7vdJ7aqtWrR9cZoRAvd9AJwO9Ev7ZaU3IEXtssjmV6 pyj5VcbwCcCtcL
    oNbZSA44H4GVfGXMGC5tFZM=
    =coyv
    -----END PGP SIGNATURE-----



    __________________________________________________ _________
    $0 Web Hosting with up to 120MB web space, 1000 MB Transfer
    10 Personalized POP and Web E-mail Accounts, and much more.
    Signup at [url]www.doteasy.com[/url]


    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Frank D. Engel, Jr. Guest

Similar Threads

  1. Fwd: sorting library of congress numbers
    By Mike Rylander in forum PostgreSQL / PGSQL
    Replies: 0
    Last Post: January 20th, 12:08 AM
  2. ~/Library/ vs ~/System/Library vs /User/Library/
    By Doug Brown in forum Mac Applications & Software
    Replies: 8
    Last Post: July 20th, 11:56 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139