[SQL] Ordering by field using lower()
Hi, please view the output from some queries below. My question is, why is the ordering apparently wrong when using the lower() function? The field "pid" is of type int2 and there is no need to use lower(), but I will need to put a test clause in php if my order type is pid so I do not use lower() in the query. Which is not a problem but I am interested as to why lower() returns a different set of results when being used on int2. Thanks. logs=# select max(pid) from syslogd; max --- 31924 (1 row) logs=# select min(pid) from syslogd; min - 548 (1 row) logs=# select pid from syslogd order by pid asc limit 10; pid -- 548 548 548 548 548 876 876 3983 3983 3983 (10 rows) logs=# select pid from syslogd order by lower(pid) asc limit 10; pid --- 25272 25389 26170 26323 29958 30294 30294 30504 30504 31770 (10 rows) logs=# select pid from syslogd order by pid desc limit 10; pid --- 31924 31924 31924 31924 31924 31923 31923 31923 31923 31922 (10 rows) logs=# select pid from syslogd order by lower(pid) desc limit 10; pid -- 9088 9088 876 876 548 548 548 548 548 5158 (10 rows) logs=# ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Ordering by field using lower()
speedboy <[EMAIL PROTECTED]> writes: > Hi, please view the output from some queries below. My question is, why is > the ordering apparently wrong when using the lower() function? You're getting a textual sort, not a numeric sort. > The field "pid" is of type int2 and there is no need to use lower(), but I > will need to put a test clause in php if my order type is pid so I do not > use lower() in the query. That would be a good plan anyway. It's pure luck for you that there is an implicit int2->text coercion path that allows the text function lower() to be applied to an int2 field. If your app expects to be able to apply lower() to any datatype at all, I'd say your app is broken. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] VARCHAR vs TEXT
> On Wed, 10 Oct 2001, Aasmund Midttun Godal wrote: > > > I am sure this question has been answered in some form or another > > before, but I can't really find anything on exactly this issue. > > > > Are there any differences between varchar and text other than > > > > 1. varchar has limited size > > 2. varchar is SQL 92 text is not? > > > > Especially regarding performance. > > > > Or am I correct to assume that if you need a place to store some text, > > and you are not sure how much (like an email address or a name) you > > are best off using text? > > Pretty much yes. text and varchar are pretty equivalent other than > the fact that varchar specifies a maximum size. I have added the following paragraph to the FAQ: CHAR() is best when storing strings that are usually the same length. VARCHAR() is best when storing variable-length strings, but you want to limit how long a string can be. TEXT is for strings of unlimited length, maximum 1 gigabyte. BYTEA is for storing binary data, particularly values that include NULL bytes. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] MEDIAN as custom aggregate?
"Josh Berkus" <[EMAIL PROTECTED]> writes:
> The query I'll use is this:
> SELECT site, COUNT(pageviews) as count_views, AVG(pageviews) AS
> mean_views, median_views
> FROM sites,
> (SELECT pageviews as median_view
>FROM pageviews
>LIMIT 1 OFFSET middlerec('pageviews')) med
> GROUP BY site, median_views;
> Where middlerec is a custom function that counts the records and returns
> the middle one.
Um ... does that work? I thought LIMIT was fairly restrictive about
what it would take as a parameter --- like, constants or $n parameters
only.
I do not know of any median-finding algorithm that doesn't require a
depressingly large amount of storage...
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] GROUPING
On Sat, 13 Oct 2001, Timothy J Hitchens wrote: > It's been a while since I used postgresql but today I have converted one > of my web apps but with one small problem. I goto do a group as > designed and executed in mysql and I get told that this and this must be > part of the aggreate etc I am puzzled and wonder if someone could bring > me up to stratch with grouping in postgresql this is my current sql: > > SELECT * FROM telemetry WHERE beat > 12 GROUP BY buid; > > Result: > > Attribute telemetry.rpvuid must be GROUPed or used in an aggregate > function > > > Oh then if I include rpvuid I get you must include this field and on it > goes. > > Thanks... Well, that query doesn't necessarily return consistant results. Let's say you have a table a | b - 1 | 1 1 | 2 2 | 3 What should select * from table group by a; give? There are two values of b that could be chosen for that group. I believe the appropriate part of the spec is from query specification (7.9 of my draft) 7) If T is a grouped table, then each in each that references a column of T shall refer- ence a grouping column or be specified within a ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] MEDIAN as custom aggregate?
On Fri, 12 Oct 2001 12:38:12 -0700
"Josh Berkus" wrote:
> For those whose stats terminology is rusty, the "median" is the "middle"
> value in a distribution. For example, if we had the following data:
>
> Table ages
> personage
> Jim 21
> Rusty 24
> Carol 37
> Bob 62
> Leah 78
>
> Our Median would be Carol's age, 37. This is a different figure from
> the Mean, or Average, which is 44.4. Using the combination of the Mean
> and the Median you can do all kinds of interesting statistical analysis.
>
In such case of this, there needs "nextval" in a query to deal with
a large number of rows. I think the following query, is not smart,
will return the necessary rows (or an average of the rows). But even
so it may need considerable time...
-- (on 7.1.3)
create sequence seq_ages start 1;
select a1.age, a1.rank -- or select avg(a1.age)
from (select a0.person, a0.age, (nextval('seq_ages') - 1) as rank
from (select *, setval('seq_ages',1) -- to reset a sequence
from ages
order by age -- this insignificant "order by" is
-- needed in order to work "setval"
) as a0
order by a0.age
) as a1
where exists (select * from ages
where a1.rank >= (select (count(*)+1)/2 from ages)
and a1.rank <= (select count(*)/2+1 from ages)
)
;
Regards,
Masaru Sugawara
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
