Re: [SQL] solved: Coalesce() in outer join between views
Hi all; I found the problem (stupid human error ;-) ) Basically it was a broken join condition. Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 1: 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] difference between all RDBMSs
Dhanashree <[EMAIL PROTECTED]> wrote: > > i m an engineering student and i m looking out for differrences between > oracle v/s sybase v/s sql v/s plsql v/s mysql with respect to the following > points > 1. rdbms > 2. data independence > 3. support of plsql > 4. integrity > 5. security > 6. use and application > 7. support to thirdparty products > 8. functionability > 9. operatability > 10. reliability > > please reply me as soon as possible. So there isn't much time left for you to submit your homework/report? SCNR, Alex. -- "Opportunity is missed by most people because it is dressed in overalls and looks like work." -- Thomas A. Edison ---(end of broadcast)--- TIP 1: 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
[SQL] Counting Row
Hi postgre people. Can anybody help to fix performance of my
query?
Let say, I have n
number of queue ticket. (Which I use SELECT).
Is there faster method to know where is my ticket number in
queue?
I use (while in PHP script ) and count one by one until
my ticket number match with row field .
$sql="select * from [MYTABLE] where [CONDITION] order
by [FIELDORDER];
$rs=&$ctclink->Execute($sql);
$ctr=1;
$pos=0;
while (!$rs->EOF) {
if
($rs->fields[0]==$recid) {
$pos=$ctr;
break;
} else {
$ctr++;
$rs->MoveNext(); }
}
print $pos
is there any method to do that with simple??
Thanks,
[SQL] Counting Row
Hi postgre people. Can anybody help to fix performance of my
query?
Let say, I have n
number of queue ticket. (Which I use SELECT).
Is there faster method to know where is my ticket number in
queue?
I use (while in PHP script ) and count one by one
until my ticket number match with row field .
$sql="select * from [MYTABLE] where [CONDITION] order
by [FIELDORDER];
$rs=&$ctclink->Execute($sql);
$ctr=1;
$pos=0;
while (!$rs->EOF) {
if
($rs->fields[0]==$recid) {
$pos=$ctr;
break;
} else {
$ctr++;
$rs->MoveNext(); }
}
print $pos
is there any method to do that with simple??
Thanks,
[SQL] Different encodings in different DBs in same cluster
Hi All, I was going through the docs for Postgres 8 for info on setting the character set (to UTF8). In the docs here: http://www.postgresql.org/docs/8.0/interactive/multibyte.html#MULTIBYTE-CHARSET-SUPPORTED I see: Since these locale settings are frozen by initdb, the apparent flexibility to use different encodings in different databases of a cluster is more theoretical than real. It is likely that these mechanisms will be revisited in future versions of PostgreSQL. One way to use multiple encodings safely is to set the locale to C or POSIX during initdb, thus disabling any real locale awareness. Does anyone know what "more theoretical than real" mean in this context? If I set the locale to C, is it going to work correctly with UTF8 encoded data? Thanks, -j -- Jamie Lawrence[EMAIL PROTECTED] It's strange to hear people like Gordon Liddy talking about morality. He hasn't been out of jail all that long. - Ben Bradlee ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ids from grouped rows
Lindsay schrieb: > Lets say i do this: > > SELECT name, MAX(age) > FROM Person > GROUP BY name > > This will group people who have the same name together, and return the > highest age for each group. I want to be able to find the id for the > person with the highest age in each group - > > Ideally, it would be something like this > > SELECT name, MAX(age), id_for_row_with_max_age > FROM Person > GROUP BY name SELECT name, age, id FROM Person RIGHT JOIN (SELECT name, MAX(age) AS m FROM Person GROUP BY name) AS t ON (Person.name=t.name AND Person.age=t.m) Stefan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Counting Row
I'd suggest : - first getting the 'order fields' value for the ticket you want : SELECT field_order FROM mytable WHERE condition AND identifier=the_one_you're_looking_for ORDER BY field_order DESC LIMIT 1 - then counting all the tickets up to this order : SELECT count(*) FROM mytable WHERE condition AND field_order <= (SELECT field_order FROM mytable WHERE condition AND identifier=the_one_you're_looking_for ORDER BY field_order DESC LIMIT 1) You could also use a plpgsql function to reimplement your PHP thing, which should be faster than doing it in PHP, but probably slower than the example above. # But there is a simpler solution. How do you model the position of a ticket in the queue ? What is the 'order field' you mention ? If your table has a SERIAL PRIMARY KEY, it's monotonous, so you can use this without further worries. table tickets_queues (id SERIAL PRIMARY KEY, queue_id, ticket_id, UNIQUE( queue_id, ticket_id) ) to get the position of a ticket (by its ticket_id) in a queue : SELECT count(*) FROM tickets_queue WHERE queue_id=# AND id <= (SELECT id FROM tickets_queue WHERE ticket_id=# AND queue_id=#) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Different encodings in different DBs in same cluster
Jamie Lawrence <[EMAIL PROTECTED]> writes: > I see: >Since these locale settings are frozen by initdb, the apparent >flexibility to use different encodings in different databases of a >cluster is more theoretical than real. > Does anyone know what "more theoretical than real" mean in this context? It means there are some locales that actively fail (you get inconsistent comparison and sorting behavior) when presented with multibyte data that doesn't match their encoding expectations. IMHO such locale definitions are broken and should be fixed, but they are not under our control. > If I set the locale to C, is it going to work correctly with UTF8 > encoded data? C will work "correctly" for suitably small values of "correctly" --- non-ASCII characters may not sort where you'd wish, and it won't know anything about case-folding for non-ASCII characters. But it will at least give consistent results. When you use a non-C locale, it's best to stick to the encoding that the locale expects. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Counting Row
I understand you need to count of tickets smaller than $recid given [CONDITION], in that case:
$sql="select count(*) from [MYTABLE] where [CONDITION] and ticketnumber <=" .
$recid ;
On 7/21/05, Ricky Sutanto <[EMAIL PROTECTED]> wrote:
Hi postgre people. Can anybody help to fix performance of my
query?
Let say, I have n
number of queue ticket. (Which I use SELECT).
Is there faster method to know where is my ticket number in
queue?
I use (while in PHP script ) and count one by one until
my ticket number match with row field .
$sql="select * from [MYTABLE] where [CONDITION] order
by [FIELDORDER];
$rs=&$ctclink->Execute($sql);
$ctr=1;
$pos=0;
while (!$rs->EOF) {
if
($rs->fields[0]==$recid) {
$pos=$ctr;
break;
} else {
$ctr++;
$rs->MoveNext(); }
}
print $pos
is there any method to do that with simple??
Thanks,
