Обсуждение: DISTINCT ordering

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

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.


Ron

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);

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

On 10/8/04 4:56 pm, "Ron St-Pierre" <rstpierre@syscor.com> 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);

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

The
the
Z

not:

The
Z
the

>
> otherwise something like:
> SELECT lower (SS.name) FROM (SELECT DISTINCT name FROM someview) SS ORDER BY
> 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

> would return 'foo' twice in the output.
>
>
> Ron
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

Jake Stride wrote:

>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

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

On 11/8/04 12:16 am, "Ron St-Pierre" <rstpierre@syscor.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@postgresql.org so that your
>     message can get through to the mailing list cleanly
>

Why not just do?

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








Jake Stride wrote:

> On 11/8/04 12:16 am, "Ron St-Pierre" <rstpierre@syscor.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@postgresql.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
>

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@users.sourceforge.net>
To: "Ron St-Pierre" <rstpierre@syscor.com>; "pgsql-novice"
<pgsql-novice@postgresql.org>
Sent: Wednesday, August 11, 2004 4:06 AM
Subject: Re: [NOVICE] DISTINCT ordering


> On 11/8/04 12:16 am, "Ron St-Pierre" <rstpierre@syscor.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@postgresql.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