Re: [SQL] SQL challenge--top 10 for each key value?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> How would you go about getting the top N (say, the top 10) for each query?
Assume you have a table "ch" and three sequences 'aa', 'bb', and 'cc'.
(Only 'aa' and 'bb' need to be initially set)
SELECT setval('aa',1,'f');
SELECT setval('bb',1,'f');
SELECT nextval('cc') AS rating,q2 AS query, s2 AS score FROM
(
SELECT 0 AS q1, 0 AS s1, NULL AS cs, nextval('aa') AS v1
UNION ALL
(SELECT *, nextval('aa') AS v1 FROM
(SELECT query AS q1, MAX(score) AS s1, checksum AS cs FROM ch GROUP BY 1,3 ORDER BY
1 ASC, 2 DESC) AS foo)
) AS uno,
(
(SELECT *, nextval('bb') AS v2 FROM
(SELECT query AS q2, MAX(score) AS s2, checksum AS cs FROM ch GROUP BY 1,3 ORDER BY
1 ASC, 2 DESC) AS foo)
UNION ALL
SELECT NULL AS q2, 0 AS s2, NULL AS cs, nextval('bb') AS v2
) AS dos
WHERE v1 = v2 AND q2 IS NOT NULL
AND (
(CASE WHEN q1 != q2 THEN setval('cc',1,'f') ELSE 0 END > 0)
OR
(CASE WHEN currval('cc')<10 THEN 1 ELSE 0 END >0)
);
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200404101029
-BEGIN PGP SIGNATURE-
iD8DBQFAeAZ1vJuQZxSWSsgRAqYuAJ9HaYLotPYkyi1U76I9xnvi8AhLTQCfUyJq
+iVdbz5U7HKep89z0kp49U0=
=6+OH
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Counting rows from two tables in one query
On Wed, Apr 07, 2004 at 10:06:20 +, Stefan Weiss <[EMAIL PROTECTED]> wrote: > Hi. > > What I am trying to get is a list that shows how many records from > 'sub_a' and 'sub_b' are referencing 'main': > > main_id | count_a | count_b > -+-+- > 1 | 2 | 1 > 2 | 12 | 1 > 3 | 7 | 3 > [..] > > This query obviously does not do what I need, it gives me the product > of count_a and count_b in both columns instead: > > select main.idas main_id, > count(sub_a.*) as count_a, > count(sub_b.*) as count_b > from main, > sub_a, > sub_b > where sub_a.main_id = main.id >and sub_b.main_id = main.id > group by main.id > having count(sub_a.*) > 0 >and count(sub_b.*) > 0 > ; > > Is it possible to get a list like the one above with a single query? You want something along the lines of: SELECT main.id, a.acnt, b.bcnt FROM main LEFT JOIN (SELECT main_id, count(*) AS acnt FROM sub_a GROUP BY main_id) AS a ON (main.id = a.main_id) LEFT JOIN (SELECT main_id, count(*) AS acnt FROM sub_b GROUP BY main_id) AS b ON (main.id = b.main_id) ; I haven't tested the above, so there might be some minor problem with it. Also depending on how you want to handle cases where there are no references to an id in main in either sub_a and/or sub_b, you may need to modify the query. The way it is now, you will get NULLs in those cases instead of zero. ---(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] hi sir urgent..required a Query
Rajeev, > I am attaching a document with this file showing two table.. < you.doc>> > > It consist of two table VALUE AND VALUE_TYPE.. > > when i have value_type_id=1 i must get Value_varchar column value > > when i have value_type_id=2 i must get Value_int column value > Please send a SQL query for this... Please re-post this with your table design in the body of your e-mail, and NO doc attachment. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] setting a non-standard date format for the duration of a session
Cris, > I understand I can use the function to_date(s,f) for converting a string > into a date using a non-standard format. > > I'm wondering if there is a way to set a non-standard date format for > the duration of a session, something like the "ALTER SESSION SET > NLS_DATE_FORMAT='f'" Oracle statement. Sort of. See the Docs on "Runtime Configuration" on the GUC variable "Datestyle". -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL challenge--top 10 for each key value?
Rod, Greg > It's not really like Oracles row num at all, though I suppose you can > emulate rownum using it. The intention is that you will use it for > "aggregates" like running totals, moving averages, counting, etc. Yes, that makes a certain amount of sense. I just take exception to the name "Row Number" becuase it confuses newbies about the actual nature of the data being returned and gets them back in the bad space of believing in fixed data ordering -- something which RDBMSes are supposed to avoid. I'm also disgusted at the vendor partiality that this shows. PostgreSQL and MySQL both have working implementations of a very similar concept using the non-confusing term "Limit". However, since Oracle is on the committee, they had to use a more "Oracle-friendly" term, I guess. > In the case of SQL was there ever any pretension otherwise? Was the SQL > standard ever really useful as a "real standard"? I can write useful ANSI > C89 code that will compile and work on any C compiler. Trying to write > portable SQL92 code that does any useful work is about as productive as > stapling bagels to your forehead. Well, there *was* a pretense otherwise, which ended about 1994, just as we were getting SQL on this project. Now the big vendors -- mostly IBM and Oracle since Informix and Sybase are dying -- run everything and adapt the standard to what features their products already have. So, yes, SQL92 needed development and expansion. But we didn't need SQL99. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] begin update ... syntax error
I was trying to speed up a simple update query fri=# begin fri-# update tabA set nobegin=tmp.nobegin, noend=tmp.noend fri-# from tmp fri-# where tabA.acc=tmp.acc; ERROR: syntax error at or near "update" at character 7 The same query can be run with no problem if not starting with BEGIN. Does mean that BEGIN cannot preceed UPDATE? Or I am making some obvious miskate? Kemin ** Proprietary or confidential information belonging to Ferring Holding SA or to one of its affiliated companies may be contained in the message. If you are not the addressee indicated in this message (or responsible for the delivery of the message to such person), please do not copy or deliver this message to anyone. In such case, please destroy this message and notify the sender by reply e-mail. Please advise the sender immediately if you or your employer do not consent to e-mail for messages of this kind. Opinions, conclusions and other information in this message represent the opinion of the sender and do not necessarily represent or reflect the views and opinions of Ferring. ** ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] begin update ... syntax error
try a semicolon after the begin ? begin; commit; Greg Williamson DBA GlobeXplorer LLC -Original Message- From: Kemin Zhou [mailto:[EMAIL PROTECTED] Sent: Sat 4/10/2004 2:32 PM To: [EMAIL PROTECTED] Cc: Subject:[SQL] begin update ... syntax error I was trying to speed up a simple update query fri=# begin fri-# update tabA set nobegin=tmp.nobegin, noend=tmp.noend fri-# from tmp fri-# where tabA.acc=tmp.acc; ERROR: syntax error at or near "update" at character 7 The same query can be run with no problem if not starting with BEGIN. Does mean that BEGIN cannot preceed UPDATE? Or I am making some obvious miskate? Kemin ** Proprietary or confidential information belonging to Ferring Holding SA or to one of its affiliated companies may be contained in the message. If you are not the addressee indicated in this message (or responsible for the delivery of the message to such person), please do not copy or deliver this message to anyone. In such case, please destroy this message and notify the sender by reply e-mail. Please advise the sender immediately if you or your employer do not consent to e-mail for messages of this kind. Opinions, conclusions and other information in this message represent the opinion of the sender and do not necessarily represent or reflect the views and opinions of Ferring. ** ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL challenge--top 10 for each key value?
On Fri, 09 Apr 2004 02:11:44 -0400, Tom Lane wrote: >> ROW_NUMBER() is a spec defined function. (6.10 of SQL200N) > > If the spec doesn't even have a year number yet, you can hardly expect > real implementations to support it ;-) SQL:2003 is finished. Among its new (non-core) OLAP features are a set of "windows functions" (spec section 6.10), which include feature ID T611 (elementary OLAP): - ROW_NUMBER() OVER (...) - RANK() OVER (...) - DENSE_RANK() OVER (...) feature ID T612 (extended OLAP): - PERCENT_RANK() OVER (...) - CUME_DIST() OVER (...) See http://www.acm.org/sigmod/record/issues/0403/index.html#standards for an article which summarizes the news in SQL:2003. ROW_NUMBER() OVER may be used in queries where a PostgreSQL user which use LIMIT. RANK() OVER may be used in queries where a PostgreSQL user would have to come up with a somewhat strange query in order to get acceptable performance, see http://troels.arvin.dk/db/rdbms/#select-top-n-postgresql -- Greetings from Troels Arvin, Copenhagen, Denmark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] begin update ... syntax error
On Saturday 10 April 2004 02:32 pm, Kemin Zhou wrote: begin; Note the semi-colon Then the update query; commit; or rollback; > I was trying to speed up a simple update query > > fri=# begin > fri-# update tabA set nobegin=tmp.nobegin, noend=tmp.noend > fri-# from tmp > fri-# where tabA.acc=tmp.acc; > ERROR: syntax error at or near "update" at character 7 > > The same query can be run with no problem if not starting with BEGIN. > > Does mean that BEGIN cannot preceed UPDATE? > > Or I am making some obvious miskate? > > Kemin > > > > > ** > Proprietary or confidential information belonging to Ferring Holding SA or > to one of its affiliated companies may be contained in the message. If you > are not the addressee indicated in this message (or responsible for the > delivery of the message to such person), please do not copy or deliver this > message to anyone. In such case, please destroy this message and notify the > sender by reply e-mail. Please advise the sender immediately if you or your > employer do not consent to e-mail for messages of this kind. Opinions, > conclusions and other information in this message represent the opinion of > the sender and do not necessarily represent or reflect the views and > opinions of Ferring. > ** > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SQL challenge--top 10 for each key value?
On Sun, 11 Apr 2004 00:13:17 +0200, I wrote: > Among its new (non-core) OLAP features are a set of > "windows functions" Sorry - I meant "window functions"... (Microsoft don't seem to have had much influence in SQL:2003's OLAP-specifications; IBM seems to be the big influencer in those parts of the standard.) -- Greetings from Troels Arvin, Copenhagen, Denmark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL challenge--top 10 for each key value?
Welcome to the real world, Josh. There are people who have full time salaried positions soley to attend standards meetings. Note that ROW_NUMBER() really is handy, regardless of the silly name. And there was a little python function of mine that did it fairly simply, except that you needed to initialize the counter in the connection before use. create or replace function pycounter(integer) returns integer as ' if args[0] == 0: SD["nextno"] = 1 return SD["nextno"] try: SD["nextno"] += 1 except: SD["nextno"] = 1 return SD["nextno"] ' language 'plpythonu'; And clearly it can be done faster as a little C function. elein On Fri, Apr 09, 2004 at 09:06:39AM -0700, Josh Berkus wrote: > Rod, > > > Something along the lines of the below would accomplish what you want > > according to spec. ROW_NUMBER() is a spec defined function. (6.10 of > > SQL200N) > > Great leaping little gods! They added something called "row number" to the > spec? > > Boy howdy, folks were right ... the ANSI committee really has completly blown > off the relational model completely. First there was the addition of > network-database functions so that IBM could make DB2 look more like a real > database, now this > > When a standards committee becomes hostage to a handful of vendors, kiss real > standards goodbye. > > -- > Josh Berkus > Aglio Database Solutions > San Francisco > > ---(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
Re: [SQL] SQL challenge--top 10 for each key value?
This solution will be in Monday's edition of
PostgreSQL General Bits (http://www.varlena.com/GeneralBits).
(In other words, if it doesn't do what you mean, let me know now!)
CREATE TYPE topscores AS
(id integer, query integer, checksum char(32), score integer);
CREATE OR REPLACE FUNCTION topscores(integer) RETURNS SETOF topscores AS
'
DECLARE
t topscores%ROWTYPE;
r RECORD;
q RECORD;
n alias for $1;
BEGIN
FOR q IN SELECT distinct query from table70 order by query LOOP
FOR t IN SELECT id , query, checksum, score
FROM table70
where query = q.query
ORDER BY query, score DESC LIMIT n LOOP
RETURN NEXT t;
END LOOP;
END LOOP;
RETURN;
END;
' language 'plpgsql';
select * from topscores(1) ;
select * from topscores(2) ;
select * from topscores(3) ;
On Thu, Apr 08, 2004 at 07:55:33PM +, Jeff Boes wrote:
> Offered up for anyone with time on their hands. I fiddled around with
> this for half an afternoon, then gave up and did it programmatically in
> Perl.
>
> Given a table that looks something like this:
>
> id | INTEGER
> query| INTEGER
> checksum | char(32)
> score| INTEGER
> include | BOOLEAN
>
>
> The table is unique by "id". "Checksum" may be repeated, but I only care
> if it is repeated within a given group by "query". ("query" is non-null.)
>
> I can get the top scorer for each "query" row by something like this:
>
> SELECT * FROM (
> SELECT DISTINCT ON (checksum) *
> FROM my_table
> ORDER BY checksum, score DESC)
> ORDER BY query;
>
> How would you go about getting the top N (say, the top 10) for each query?
>
> And then, if that's too easy for you--consider a further case where I
> want every row for a given "query" that has "include" TRUE, and enough
> non-"include" rows to make N. I might end up with more than N rows for a
> given value of "query" if there were more than N with "include" set.
>
> I headed off in the direction of groups of SELECTs and UNIONs, and quit
> when I got to something like four levels of "SELECT ... AS FOO" ...
>
> --
> Jeff Boes vox 269.226.9550 ext 24
> Database Engineer fax 269.349.9076
> Nexcerpt, Inc. http://www.nexcerpt.com
>...Nexcerpt... Extend your Expertise
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] SQL challenge--top 10 for each key value?
elein <[EMAIL PROTECTED]> writes: > create or replace function pycounter(integer) > returns integer as > ' >if args[0] == 0: > SD["nextno"] = 1 > return SD["nextno"] >try: > SD["nextno"] += 1 >except: > SD["nextno"] = 1 >return SD["nextno"] > ' language 'plpythonu'; > > And clearly it can be done faster as a little > C function. Does this approach have a hope of working if it's used twice in the same query? -- greg ---(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
[SQL] trigger/for key help
S*t s*t s*t. I have managed to screw up the system tables trying to
delete a foreign key on a new table I was going to start using tomorrow.
elevating-# \d diag_logs
Table "diag_logs"
Column | Type |
Modifiers
-++---
sernum | integer| not null default
nextval('public.diag_logs_sernum_seq'::text)
display | integer| not null
tdate | date | not null
ttime | time without time zone | not null
tstatus | smallint | not null
ttype | smallint | not null
Indexes: diag_logs_display,
diag_logs_tdate,
diag_logs_tstatus
Primary key: diag_logs_pkey
There used to be to foreign key constraints named $1 and $2 (I cut and
pasted sql from a dump of another table that caused the trigger names
that I was trying to get rid of)
These were created with alter table like this :
elevating=# ALTER TABLE ONLY diag_logs ADD CONSTRAINT "$2" FOREIGN KEY
(ttype) REFERENCES test_types(num);
ALTER TABLE
based on a bunch of surfing I deleted the six rows in pg_tigger that
referred to
elevating=# delete from pg_trigger where tgargs like '%diag_logs%';
DELETE 6
elevating=# drop table diag_logs;
ERROR: 2 trigger record(s) not found for relation "diag_logs"
so digging around I found that there were still entries in pg_constraint
elevating=# select * from pg_constraint where conrelid in (select oid
from pg_class where relname = 'diag_logs');
conname | connamespace | contype | condeferrable | condeferred |
conrelid | contypid | confrelid | confupdtype | confdeltype |
confmatchtype | conkey | confkey | conbin | consrc
+--+-+---+-+--+--+---+-+-+---++-++
diag_logs_pkey | 2200 | p | f | f
| 2041950 |0 | 0 | |
| | {1}| ||
$1 | 2200 | f | f | f
| 2041950 |0 | 1027502 | a | a |
u | {2}| {1} ||
$2 | 2200 | f | f | f
| 2041950 |0 | 2041960 | a | a |
u | {6}| {1} ||
(3 rows)
elevating=# select * from pg_constraint where conrelid in (select oid
from pg_class where relname = 'diag_logs') and contype='f';
conname | connamespace | contype | condeferrable | condeferred |
conrelid | contypid | confrelid | confupdtype | confdeltype |
confmatchtype | conkey | confkey | conbin | consrc
-+--+-+---+-+--+--+---+-+-+---++-++
$1 | 2200 | f | f | f |
2041950 |0 | 1027502 | a | a |
u | {2}| {1} ||
$2 | 2200 | f | f | f |
2041950 |0 | 2041960 | a | a |
u | {6}| {1} ||
(2 rows)
elevating=# delete from pg_constraint where conrelid in (select oid from
pg_class where relname = 'diag_logs') and contype='f';
DELETE 2
Still no joy now I seem to have deleted the pk constraint too.
elevating=# select * from pg_constraint where conrelid in (select oid
from pg_class where relname = 'diag_logs') ;
conname | connamespace | contype | condeferrable | condeferred |
conrelid | contypid | confrelid | confupdtype | confdeltype |
confmatchtype | conkey | confkey | conbin | consrc
-+--+-+---+-+--+--+---+-+-+---++-++
(0 rows)
I obviously don't understand the system tables well enough to be dinkin
around in here but here I am and wanting to fix it .
Any ideas, anyone?
Bret
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
