DISTINCT ordering

#1

I have a view from which I select values, but I need to do a 'SELECT
DISTINCT' query on a 'varchar' column and order by lower case eg:

SELECT DISTINCT name FROM someview ORDER BY lower(name)

Obviously this doesn't work with the 'DISTINCT' but is there any way to do
it apart from:

SELECT v.name FROM (SELECT DISTINCT name FROM someview) v ORDER BY
lower(v.name)

Or is this the most efficient way?

Thanks

Jake

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

http://www.postgresql.org/docs/faqs/FAQ.html

#2

Jake Stride wrote:

Quote:

I have a view from which I select values, but I need to do a 'SELECT
DISTINCT' query on a 'varchar' column and order by lower case eg:

SELECT DISTINCT name FROM someview ORDER BY lower(name)

If this is what you want, wouldn't 'Foo' and 'foo' both show up in your

output? If you only wanted one 'foo' you could use:

SELECT DISTINCT lower(name) FROM someview ORDER BY lower(name);

otherwise something like:
SELECT lower (SS.name) FROM (SELECT DISTINCT name FROM someview) SS ORDER BY lower(name);
would return 'foo' twice in the output.

Ron

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#3

On 10/8/04 4:56 pm, "Ron St-Pierre" <rstpierre (AT) syscor (DOT) com> wrote:

Quote:

Jake Stride wrote:

I have a view from which I select values, but I need to do a 'SELECT
DISTINCT' query on a 'varchar' column and order by lower case eg:

SELECT DISTINCT name FROM someview ORDER BY lower(name)

If this is what you want, wouldn't 'Foo' and 'foo' both show up in your
output? If you only wanted one 'foo' you could use:

SELECT DISTINCT lower(name) FROM someview ORDER BY lower(name);

Because I don¹t want the name in lower case, what I want is

The
the
Z

not:

The
Z
the

Quote:

otherwise something like:
SELECT lower (SS.name) FROM (SELECT DISTINCT name FROM someview) SS ORDERBY
lower(name);

This is what I have at present, although slightly adjusted, but I wanted to
know if it was possible without 2 selects,

SELECT SS.name FROM (SELECT DISTINCT name FROM someview) SS ORDER BY
lower(name);

Jake

Quote:

would return 'foo' twice in the output.

Ron

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

http://archives.postgresql.org

#4

Jake Stride wrote:

Quote:

Because I don¹t want the name in lower case, what I want is

The
the
Z

not:

The
Z
the

This is what I have at present, although slightly adjusted, but I wanted to
know if it was possible without 2 selects,

SELECT SS.name FROM (SELECT DISTINCT name FROM someview) SS ORDER BY
lower(name);

Jake

In that case I don't think that you can do it without a subquery. I
think that if the db was configured with a different locale (en_GB or
en_US) it would sort the data as you want it, but I think that other
problems might occur. I haven't dealt with the locale settings much
myself, but there are frequently questions(problems?) such as yours
which show up on the GENERAL discussion list. You can search the GENERAL
archives and documentation if you want more background on this it it's a
big problem. Or perhaps someone with more 'locale' knowledge might
confirm this.

hth
Ron

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#5

Ron St-Pierre wrote:

Quote:

Jake Stride wrote:

I have a view from which I select values, but I need to do a 'SELECT
DISTINCT' query on a 'varchar' column and order by lower case eg:

SELECT DISTINCT name FROM someview ORDER BY lower(name)

If this is what you want, wouldn't 'Foo' and 'foo' both show up in your
output? If you only wanted one 'foo' you could use:

SELECT DISTINCT lower(name) FROM someview ORDER BY lower(name);

otherwise something like:
SELECT lower (SS.name) FROM (SELECT DISTINCT name FROM someview) SS
ORDER BY lower(name);
would return 'foo' twice in the output.

Or even

SELECT DISTINCT ON (lower(name)) name
FROM someview
ORDER BY lower(name);

--
Andrew Hammond 416-673-4138 ahammond (AT) ca (DOT) afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (GNU/Linux)

iD8DBQFBGSekgfzn5SevSpoRAkFzAKCU+Drq7TlbRGOwjUAIsm T3wIIuewCeMc55
iR3d3Z0iFiuyXxmmMn9gl+4=
=PxNT
-----END PGP SIGNATURE-----

#6

Andrew Hammond wrote:

Quote:

Ron St-Pierre wrote:

Jake Stride wrote:

I have a view from which I select values, but I need to do a 'SELECT
DISTINCT' query on a 'varchar' column and order by lower case eg:

SELECT DISTINCT name FROM someview ORDER BY lower(name)

If this is what you want, wouldn't 'Foo' and 'foo' both show up in
your output? If you only wanted one 'foo' you could use:

SELECT DISTINCT lower(name) FROM someview ORDER BY lower(name);

otherwise something like:
SELECT lower (SS.name) FROM (SELECT DISTINCT name FROM someview) SS
ORDER BY lower(name);
would return 'foo' twice in the output.

Or even

SELECT DISTINCT ON (lower(name)) name
FROM someview
ORDER BY lower(name);

But then only one 'foo' would show up in the results:

Foo
Z

and not:

Foo
foo
Z

which is what he said he wanted.

Ron

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#7

On 11/8/04 12:16 am, "Ron St-Pierre" <rstpierre (AT) syscor (DOT) com> wrote:

Quote:

Andrew Hammond wrote:

Ron St-Pierre wrote:

Jake Stride wrote:

I have a view from which I select values, but I need to do a 'SELECT
DISTINCT' query on a 'varchar' column and order by lower case eg:

SELECT DISTINCT name FROM someview ORDER BY lower(name)

If this is what you want, wouldn't 'Foo' and 'foo' both show up in
your output? If you only wanted one 'foo' you could use:

SELECT DISTINCT lower(name) FROM someview ORDER BY lower(name);

otherwise something like:
SELECT lower (SS.name) FROM (SELECT DISTINCT name FROM someview) SS
ORDER BY lower(name);
would return 'foo' twice in the output.

Or even

SELECT DISTINCT ON (lower(name)) name
FROM someview
ORDER BY lower(name);

But then only one 'foo' would show up in the results:

Foo
Z

and not:

Foo
foo
Z

which is what he said he wanted.

Ron

I must have misunderstood what you meant, sorry. Andrew Hammonds answer
works how I want it to, I guess my example was a little trival, my solution
was needed to over come the following ordering:

The company
The one more company
the another company

So that is was

the another company
The company
The one more company

(in a contacts database)

Thanks

Jake

Quote:

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#8

Why not just do?

SELECT DISTINCT name, LOWER(name) FROM someview ORDER BY lower(name)

Jake Stride wrote:

Quote:

On 11/8/04 12:16 am, "Ron St-Pierre" <rstpierre (AT) syscor (DOT) com> wrote:

Andrew Hammond wrote:

Ron St-Pierre wrote:

Jake Stride wrote:

I have a view from which I select values, but I need to do a 'SELECT
DISTINCT' query on a 'varchar' column and order by lower case eg:

SELECT DISTINCT name FROM someview ORDER BY lower(name)

If this is what you want, wouldn't 'Foo' and 'foo' both show up in
your output? If you only wanted one 'foo' you could use:

SELECT DISTINCT lower(name) FROM someview ORDER BY lower(name);

otherwise something like:
SELECT lower (SS.name) FROM (SELECT DISTINCT name FROM someview) SS
ORDER BY lower(name);
would return 'foo' twice in the output.

Or even

SELECT DISTINCT ON (lower(name)) name
FROM someview
ORDER BY lower(name);

But then only one 'foo' would show up in the results:

Foo
Z

and not:

Foo
foo
Z

which is what he said he wanted.

Ron

I must have misunderstood what you meant, sorry. Andrew Hammonds answer
works how I want it to, I guess my example was a little trival, my solution
was needed to over come the following ordering:

The company
The one more company
the another company

So that is was

the another company
The company
The one more company

(in a contacts database)

Thanks

Jake

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#9

I don't know if is so late, but you can use something like

SELECT DISTINCT name , lower(name) AS lower_name FROM someview ORDER BY 2

Luiz

----- Original Message -----
From: "Jake Stride" <nsuk (AT) users (DOT) sourceforge.net>
To: "Ron St-Pierre" <rstpierre (AT) syscor (DOT) com>; "pgsql-novice"
<pgsql-novice (AT) postgresql (DOT) org>
Sent: Wednesday, August 11, 2004 4:06 AM
Subject: Re: [NOVICE] DISTINCT ordering

Quote:

On 11/8/04 12:16 am, "Ron St-Pierre" <rstpierre (AT) syscor (DOT) com> wrote:

Andrew Hammond wrote:

Ron St-Pierre wrote:

Jake Stride wrote:

I have a view from which I select values, but I need to do a 'SELECT
DISTINCT' query on a 'varchar' column and order by lower case eg:

SELECT DISTINCT name FROM someview ORDER BY lower(name)

If this is what you want, wouldn't 'Foo' and 'foo' both show up in
your output? If you only wanted one 'foo' you could use:

SELECT DISTINCT lower(name) FROM someview ORDER BY lower(name);

otherwise something like:
SELECT lower (SS.name) FROM (SELECT DISTINCT name FROM someview) SS
ORDER BY lower(name);
would return 'foo' twice in the output.

Or even

SELECT DISTINCT ON (lower(name)) name
FROM someview
ORDER BY lower(name);

But then only one 'foo' would show up in the results:

Foo
Z

and not:

Foo
foo
Z

which is what he said he wanted.

Ron

I must have misunderstood what you meant, sorry. Andrew Hammonds answer
works how I want it to, I guess my example was a little trival, my
solution
was needed to over come the following ordering:

The company
The one more company
the another company

So that is was

the another company
The company
The one more company

(in a contacts database)

Thanks

Jake

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DISTINCT ordering

comp.databases.postgresql.novice comp.databases.postgresql.novice

DISTINCT ordering - 08-10-2004 , 06:24 AM

Re: DISTINCT ordering - 08-10-2004 , 10:56 AM

Re: DISTINCT ordering - 08-10-2004 , 12:32 PM

Re: DISTINCT ordering - 08-10-2004 , 01:37 PM

Re: DISTINCT ordering - 08-10-2004 , 02:53 PM

Re: DISTINCT ordering - 08-10-2004 , 06:16 PM

Re: DISTINCT ordering - 08-11-2004 , 02:06 AM

Re: DISTINCT ordering - 08-24-2004 , 10:09 PM

Re: DISTINCT ordering - 08-26-2004 , 07:05 PM