Thread: DISTINCT ordering

DISTINCT ordering

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


Re: DISTINCT ordering

From
Ron St-Pierre
Date:
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


Re: DISTINCT ordering

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

Attachment

Re: DISTINCT ordering

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


Re: DISTINCT ordering

From
Ron St-Pierre
Date:
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


Re: DISTINCT ordering

From
Ron St-Pierre
Date:
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




Re: DISTINCT ordering

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


Re: DISTINCT ordering

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

Re: DISTINCT ordering

From
"Luiz K. Matsumura"
Date:
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