Re: [SQL] Server Side C programming Environment Set up
Peter Eisentraut <[EMAIL PROTECTED]> writes: > make install-all-headers That's not a complete solution though; the headers are only half the problem. Makefiles are the other half, and our story on them is pretty bad. For instance I've been meaning to ask what to do about this open bug report: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=112244 If you don't have working Makefiles, it doesn't help that much to have all the headers. I think Lamar's perennial issues with running the regression tests in an RPM installation are closely related too ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Server Side C programming Environment Set up
Kemin Zhou wrote: > IN chapter 33 Extending SQL > 33.7.5 Writing Code > when run pg_config --includedir-server > I got /usr/local/pgsql/include/server but my machine does have this > directory make install-all-headers It's explained in the installation instructions. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Join issue on a maximum value
On Wed, Apr 21, 2004 at 16:28:10 -0400, Heflin <[EMAIL PROTECTED]> wrote: > > > >The postgres specific way of doing this is: > >SELECT DISTINCT ON (auction.auction_id) > > auction.auction_id, image.image_id, image.image_descr > > FROM auction JOIN image ON auction.auction_id = image.auction_id > > WHERE auction.auction_owner = 'Mabel' > > ORDER BY auction.auction_id, image.image_id DESC > >; > > The thing that disturbs me about your syntax is that I don't really see > an assurance that I'll get the correct image_id. Any chance you can > tell me why this works? The postgres semantic is that when dinstinct on is combined with order by the first distinct row in the order defined by the order by is the one returned. This is described in the documentation and there is an example of a query taking advantage of this. ---(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
Re: [SQL] Join issue on a maximum value
Two choices that work:
Either add another JOIN in which retrieves the MAX(image_id) for each
auction:
SELECT auction.auction_id, image.image_id, image.image_descr
FROM auction
JOIN image USING(auction_id)
JOIN ( SELECT auction_id, MAX(image_id) AS image_id
FROM image
GROUP BY auction_id) max_aid USING (image_id)
WHERE owner = 'Mabel'
ORDER by auction.auction_id;
OR use a sub-select:
SELECT auction.auction_id, image_id, image.image_descr
FROM auction
JOIN image USING (auction_id)
WHERE image_id = ( SELECT max(image_id)
FROM image
WHERE auction_id = auction.auction_id)
AND image_owner = 'Mabel';
Test both with your data - My experience is that the sub-select runs
slower than throwing in the extra join.
Heflin wrote:
OK, it's been a while since I've had to do anything remotely complex
in SQL, so this may just be a pure brain block on my part.
I have 2 tables, auction and image, defined like this:
Table "public.auction"
Column | Type |
Modifiers
-+-+-
auction_id | integer | not null default
nextval('public.auction_auction_id_seq'::text)
auction_descrip | text|
auction_owner | text|
Indexes:
"auction_pkey" primary key, btree (auction_id)
Table "public.image"
Column| Type |
Modifiers
-+-+-
image_id| integer | not null default
nextval('public.image_image_id_seq'::text)
auction_id | integer | not null
image_descr | text|
Indexes:
"image_pkey" primary key, btree (image_id)
Foreign-key constraints:
"$1" FOREIGN KEY (auction_id) REFERENCES auction(auction_id) ON
UPDATE RESTRICT ON DELETE RESTRICT
Current data in the tables:
play=# select * from auction
play-# ;
auction_id | auction_descrip | auction_owner
+-+---
1 | Mabel Auction 1 | Mabel
2 | Mabel Auction 2 | Mabel
3 | Mabel Auction 3 | Mabel
4 | Fred Auction 1 | Fred
5 | Fred Auction 2 | Fred
play=# select * from image;
image_id | auction_id | image_descr
--++-
1 | 1 | image 1
2 | 1 | image 2
3 | 2 | image 3
4 | 3 | image 4
5 | 3 | image 5
6 | 4 | image 7
7 | 3 | image 8
So a basic JOIN gets this:
SELECT auction.auction_id, image.image_id, image.image_descr
FROM auction JOIN image ON auction.auction_id = image.auction_id
WHERE auction.auction_owner = 'Mabel';
auction_id | image_id | image_descr
+--+-
1 |1 | image 1
1 |2 | image 2
2 |3 | image 3
3 |4 | image 4
3 |5 | image 5
3 |7 | image 8
(6 rows)
Now the problem: I can't seem to remember how to get only the max
value for the image_id for each auction_id so that the result set
would be:
auction_id | image_id | image_descr
+--+-
1 |2 | image 2
2 |3 | image 3
3 |7 | image 8
Playing with the max() aggregate seems to be the correct path, but for
the life of me I can't seem to get the syntax to the point that it
produces what I need. Any help would be greatly appreciated!
Thanks,
-Heflin
--
Edmund Bacon <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Join issue on a maximum value
On Wed, Apr 21, 2004 at 14:29:34 -0400, Heflin <[EMAIL PROTECTED]> wrote: > > So a basic JOIN gets this: > > SELECT auction.auction_id, image.image_id, image.image_descr > FROM auction JOIN image ON auction.auction_id = image.auction_id > WHERE auction.auction_owner = 'Mabel'; > > Now the problem: I can't seem to remember how to get only the max value > for the image_id for each auction_id so that the result set would be: The postgres specific way of doing this is: SELECT DISTINCT ON (auction.auction_id) auction.auction_id, image.image_id, image.image_descr FROM auction JOIN image ON auction.auction_id = image.auction_id WHERE auction.auction_owner = 'Mabel' ORDER BY auction.auction_id, image.image_id DESC ; The more standard way to do it would be joining auction and image with a group by and max to get the highest image_id and then joining that result to image again to get the corresponding description. ---(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] Join issue on a maximum value
Bruno Wolff III wrote: On Wed, Apr 21, 2004 at 14:29:34 -0400, Heflin <[EMAIL PROTECTED]> wrote: So a basic JOIN gets this: SELECT auction.auction_id, image.image_id, image.image_descr FROM auction JOIN image ON auction.auction_id = image.auction_id WHERE auction.auction_owner = 'Mabel'; Now the problem: I can't seem to remember how to get only the max value for the image_id for each auction_id so that the result set would be: The postgres specific way of doing this is: SELECT DISTINCT ON (auction.auction_id) auction.auction_id, image.image_id, image.image_descr FROM auction JOIN image ON auction.auction_id = image.auction_id WHERE auction.auction_owner = 'Mabel' ORDER BY auction.auction_id, image.image_id DESC ; The more standard way to do it would be joining auction and image with a group by and max to get the highest image_id and then joining that result to image again to get the corresponding description. Thanks! I was actually trying to do it the more standard way, but I've been bungling up the syntax. I'm going to play with that some more, since it might be useful elsewhere. The thing that disturbs me about your syntax is that I don't really see an assurance that I'll get the correct image_id. Any chance you can tell me why this works? Thanks again, -Heflin smime.p7s Description: S/MIME Cryptographic Signature
[SQL] rule's behavior with join interesting
Here I have a very simple case table1 table1_removed anotherTable create or replace RULE rec_remove as ON DELETE TO table1 do insert into table1_remove select old.*, a.acc from old g join anotherTable a on g.acc=a.other_acc; === the parser complained ERROR: relation "*OLD*" does not exist So I used select old.*, a.acc from table1 g join anotherTable a on g.acc=a.other_acc; This worked find. When I run delete on table1, 213 rows. tmp table received 213X213 = 45369 rows. each row is duplicated 213 times. My question: Is it possible to bring in another table in a rule? Where am I wrong in this case. Certainly I don't want that duplications. My table1_removed contain a primary key for the id. The speed of doing the delete is also very slow apparently it has to do N-square inserts. I have very limited information to read on the manual of postgres. Any solution? 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 8: explain analyze is your friend
[SQL] Server Side C programming Environment Set up
I read a lot of document and did some search and looked at the source code of postgres but did not find a simple solution to my question. How to set up the programming environment for C. IN chapter 33 Extending SQL 33.7.5 Writing Code when run pg_config --includedir-server I got /usr/local/pgsql/include/server but my machine does have this directory I looked at configure --help, no mentioning about how to install server side include and lib files. I do see postgres.h src/include/executor/spi.h /src/include/commands/trigger.h located in the source distribution. Is there a simple configure flag or some program (shell script) in the source distribution to set up the programming environment? 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Join issue on a maximum value
OK, it's been a while since I've had to do anything remotely complex in
SQL, so this may just be a pure brain block on my part.
I have 2 tables, auction and image, defined like this:
Table "public.auction"
Column | Type |
Modifiers
-+-+-
auction_id | integer | not null default
nextval('public.auction_auction_id_seq'::text)
auction_descrip | text|
auction_owner | text|
Indexes:
"auction_pkey" primary key, btree (auction_id)
Table "public.image"
Column| Type |
Modifiers
-+-+-
image_id| integer | not null default
nextval('public.image_image_id_seq'::text)
auction_id | integer | not null
image_descr | text|
Indexes:
"image_pkey" primary key, btree (image_id)
Foreign-key constraints:
"$1" FOREIGN KEY (auction_id) REFERENCES auction(auction_id) ON
UPDATE RESTRICT ON DELETE RESTRICT
Current data in the tables:
play=# select * from auction
play-# ;
auction_id | auction_descrip | auction_owner
+-+---
1 | Mabel Auction 1 | Mabel
2 | Mabel Auction 2 | Mabel
3 | Mabel Auction 3 | Mabel
4 | Fred Auction 1 | Fred
5 | Fred Auction 2 | Fred
play=# select * from image;
image_id | auction_id | image_descr
--++-
1 | 1 | image 1
2 | 1 | image 2
3 | 2 | image 3
4 | 3 | image 4
5 | 3 | image 5
6 | 4 | image 7
7 | 3 | image 8
So a basic JOIN gets this:
SELECT auction.auction_id, image.image_id, image.image_descr
FROM auction JOIN image ON auction.auction_id = image.auction_id
WHERE auction.auction_owner = 'Mabel';
auction_id | image_id | image_descr
+--+-
1 |1 | image 1
1 |2 | image 2
2 |3 | image 3
3 |4 | image 4
3 |5 | image 5
3 |7 | image 8
(6 rows)
Now the problem: I can't seem to remember how to get only the max value
for the image_id for each auction_id so that the result set would be:
auction_id | image_id | image_descr
+--+-
1 |2 | image 2
2 |3 | image 3
3 |7 | image 8
Playing with the max() aggregate seems to be the correct path, but for
the life of me I can't seem to get the syntax to the point that it
produces what I need. Any help would be greatly appreciated!
Thanks,
-Heflin
smime.p7s
Description: S/MIME Cryptographic Signature
Re: [SQL] transaction
On Wed, Apr 21, 2004 at 12:58:56 +0530, [EMAIL PROTECTED] wrote: > > The code looks like: > > update tempxitag set qty = qty + nqty where > ccod = cccod > GET DIAGNOSTICS nFound = ROW_COUNT; > If nFound = 0 then > insert into tempxitag( ccod, qty) > values (cccod, nqty ); > End if; You still can get errors if two transactions try to refer to the same nonexistant record at the same time. Postgres doesn't do predicate locking so the update won't lock the to be inserted row and both transactions may see the record as not existing and both try to do an insert. Updating, checking the count and then trying an insert if the count was 0 and retrying if the insert fails may be a better approach than locking the table. However, since this is an existing application it may be hard to make this complicated of a change. If there is flexibility in how the task gets done, switching to something based on sequences is probably the way to go. ---(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
[SQL] Trigger calling a function HELP ME! (2)
Sorry. I realize I slipped an error in my code: the code is: --- CREATE TABLE public.imp_test ( id int8, value text ) WITHOUT OIDS; CREATE OR REPLACE FUNCTION public.imp_test_to_out_test(imp_test) RETURNS imp_test AS 'begin return $1; end;' LANGUAGE 'plpgsql' STABLE; CREATE OR REPLACE FUNCTION public.imp_test_trigger() RETURNS trigger AS 'begin return imp_test_to_out_test(new); end;' LANGUAGE 'plpgsql' STABLE; CREATE TRIGGER imp_test_trigger_001 BEFORE INSERT OR UPDATE ON public.imp_test FOR EACH ROW EXECUTE PROCEDURE public.imp_test_trigger(); --- regards, = Riccardo G. Facchini ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
Is there some reason you can't do this:
SELECT DISTINCT
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date)
FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id
WHERE uus.x_id=1
ORDER BY
uu.add_date DESC;
This might be faster, as you only have to sort on one field, and I think it should give the desired results
[EMAIL PROTECTED] wrote:
Hello,
I am trying to select distinct dates and order them in the reverse
chronological order. Although the column type is TIMESTAMP, in this
case I want only , MM, and DD back.
I am using the following query, but it's not returning dates back in
the reverse chronological order:
SELECT DISTINCT
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date)
FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id
WHERE uus.x_id=1
ORDER BY
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date) DESC;
This is what the above query returns:
date_part | date_part | date_part
---+---+---
2004 | 2 | 6
2004 | 4 |20
(2 rows)
I am trying to get back something like this:
2004 4 20
2004 4 19
2004 2 6
...
My query is obviously wrong, but I can't see the mistake. I was
wondering if anyone else can see it. Just changing DESC to ASC, did
not work.
Thank you!
Otis
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
--
Edmund Bacon <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Trigger calling a function HELP ME!
Hi all, first of all, let me explain what I'm trying to do. I have a table with a fairly complicated trigger. In this trigger I have a specific set of codelines that can be executed in more than 50 places that works on the new.* fields in order to fix/clean them. In order to improve readability, I created a function that manages this small set of codelines, but I'm stuck on the following error: --- ERROR: return type mismatch in function returning tuple at or near "imp_test_to_out_test" CONTEXT: compile of PL/pgSQL function "imp_test_trigger" near line 2 --- as a model, I've created this run-down example: --- CREATE TABLE public.imp_test ( id int8, value text ) WITHOUT OIDS; CREATE OR REPLACE FUNCTION public.imp_test_to_out_test(imp_test) RETURNS imp_test AS 'begin return new; end;' LANGUAGE 'plpgsql' STABLE; CREATE OR REPLACE FUNCTION public.imp_test_trigger() RETURNS trigger AS 'begin return imp_test_to_out_test(new); end;' LANGUAGE 'plpgsql' STABLE; CREATE TRIGGER imp_test_trigger_001 BEFORE INSERT OR UPDATE ON public.imp_test FOR EACH ROW EXECUTE PROCEDURE public.imp_test_trigger(); --- Whenever I run the following select, I get the a.m. result: --- insert into imp_test (id, value) values(1, 'A'); --- Can somebody help me? regards, = Riccardo G. Facchini ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] datediff script
This is a not-quite complete implementation of the SY/MS sql datediff. The months_between function can be extrapolated from it as well. I looked for it on forums, etc. and all I found were people complaining about the lack of an example. Please post fixes/changes or a link to a better one... if you know of it. CREATE OR REPLACE FUNCTION public.datediff(varchar, timestamp, timestamp) RETURNS int4 AS ' DECLARE arg_mode alias for $1; arg_d2 alias for $2; arg_d1 alias for $3; BEGIN if arg_mode = \'dd\' or arg_mode = \'d\' or arg_mode = \'y\' or arg_mode = \'dy\' or arg_mode = \'w\' then return cast(arg_d1 as date) - cast(arg_d2 as date); elsif arg_mode = \'ww\' then return ceil( ( cast(arg_d1 as date) - cast(arg_d2 as date) ) / 7.0); elsif arg_mode = \'mm\' OR arg_mode = \'m\' then return 12 * (date_part(\'year\',arg_d1) - date_part(\'year\',arg_d2)) + date_part(\'month\',arg_d1) - date_part(\'month\',arg_d2) + case when date_part(\'day\',arg_d1) > date_part(\'day\',arg_d2) then 0 when date_part(\'day\',arg_d1) = date_part(\'day\',arg_d2) and cast(arg_d1 as time) >= cast(arg_d2 as time) then 0 else -1 end; elsif arg_mode = \'yy\' OR arg_mode = \'y\' OR arg_mode = \'\' then return (cast(arg_d1 as date) - cast(arg_d2 as date)) / 365; end if; END; ' LANGUAGE 'plpgsql' VOLATILE; ---(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] Syntax for cmd to EXEC...how many quotes?
David, I tend to use \ to escape things like ' - I find it makes it somewhat easier to debug. What about: sql_string :=\' INSERT INTO temp_table ( view_name, row_count ) SELECT \' || r_rec.viewname || \', count(*) FROM \' || r_rec.viewname || \' ; \'; HTH George - Original Message - From: "David B" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, April 20, 2004 6:24 PM Subject: [SQL] Syntax for cmd to EXEC...how many quotes? > Folks, > > This is driving me crazy...I'm sure it's possible but that I am getting the > #quotes wrong in some way... > I keep getting unterminated string errors...now matter how many quotes I > use. > > I have a FN that I want to loop through all views and populate a table with > a count(*) from each views. > > To do it I'm doing a LOOP around all views...something like: > > FOR r_rec IN SELECT viewname from pg_views > LOOP > > sql_string := 'INSERT INTO temp_table ( view_name, row_count ) SELECT > ''' || r_rec.viewname || ''', count(*) FROM ' || r_rec.viewname || ' ; ' ; > > EXEC sql_string ; > > END LOOP ; > > END ; > > > Building that sql_string is the problem. > Any thoughts folks? > > -D > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.648 / Virus Database: 415 - Release Date: 3/31/2004 > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
Thank you and Denis ([EMAIL PROTECTED]) - that was it. I needed
explicit DESC for each ORDER BY criterium.
Otis
--- Stijn Vanroye <[EMAIL PROTECTED]> wrote:
> > Hello,
> >
> > I am trying to select distinct dates and order them in the reverse
> > chronological order. Although the column type is TIMESTAMP, in
> this
> > case I want only , MM, and DD back.
> >
> > I am using the following query, but it's not returning dates back
> in
> > the reverse chronological order:
> >
> > SELECT DISTINCT
> > date_part('year', uu.add_date), date_part('month', uu.add_date),
>
> > date_part('day', uu.add_date)
> >
> > FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> > ui.id=uu.user_id
> > WHERE uus.x_id=1
> >
> > ORDER BY
> > date_part('year', uu.add_date), date_part('month', uu.add_date),
> > date_part('day', uu.add_date) DESC;
> >
> >
> > This is what the above query returns:
> >
> > date_part | date_part | date_part
> > ---+---+---
> > 2004 | 2 | 6
> > 2004 | 4 |20
> > (2 rows)
> >
> >
> > I am trying to get back something like this:
> > 2004 4 20
> > 2004 4 19
> > 2004 2 6
> > ...
> >
> > My query is obviously wrong, but I can't see the mistake. I was
> > wondering if anyone else can see it. Just changing DESC to ASC,
> did
> > not work.
> >
> > Thank you!
> > Otis
> What you could try to do in your order by clause is the following:
> ORDER BY
> date_part('year', uu.add_date) DESC,
> date_part('month', uu.add_date) DESC,
> date_part('day', uu.add_date) DESC;
> That way you are sure each of the fields is sorted DESC. if you don't
> specify a direction in your order by clause postgres will take ASC as
> the default. I think that he does "ASC,ASC,DESC" instead. I'm not
> sure if he applies the DESC to all specified fields in the order by
> if you declare it only once.
>
>
> Regards,
>
> Stijn Vanroye
>
> ---(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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] transaction
On Tue, Apr 20, 2004 at 09:14:48PM +0200, H.J. Sanders wrote: > > - BEGIN WORK > > - INSERT ROW > > - IF FAILED THEN UPDATE ROW > > - COMMIT WORK You can do it the other way. Begin, update; if 0 rows are updated then insert. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
> Hello,
>
> I am trying to select distinct dates and order them in the reverse
> chronological order. Although the column type is TIMESTAMP, in this
> case I want only , MM, and DD back.
>
> I am using the following query, but it's not returning dates back in
> the reverse chronological order:
>
> SELECT DISTINCT
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date)
>
> FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
> ui.id=uu.user_id
> WHERE uus.x_id=1
>
> ORDER BY
> date_part('year', uu.add_date), date_part('month', uu.add_date),
> date_part('day', uu.add_date) DESC;
>
>
> This is what the above query returns:
>
> date_part | date_part | date_part
> ---+---+---
> 2004 | 2 | 6
> 2004 | 4 |20
> (2 rows)
>
>
> I am trying to get back something like this:
> 2004 4 20
> 2004 4 19
> 2004 2 6
> ...
>
> My query is obviously wrong, but I can't see the mistake. I was
> wondering if anyone else can see it. Just changing DESC to ASC, did
> not work.
>
> Thank you!
> Otis
What you could try to do in your order by clause is the following:
ORDER BY
date_part('year', uu.add_date) DESC,
date_part('month', uu.add_date) DESC,
date_part('day', uu.add_date) DESC;
That way you are sure each of the fields is sorted DESC. if you don't specify a
direction in your order by clause postgres will take ASC as the default. I think that
he does "ASC,ASC,DESC" instead. I'm not sure if he applies the DESC to all specified
fields in the order by if you declare it only once.
Regards,
Stijn Vanroye
---(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] Order by YYYY MM DD in reverse chrono order trouble
Hello,
I am trying to select distinct dates and order them in the reverse
chronological order. Although the column type is TIMESTAMP, in this
case I want only , MM, and DD back.
I am using the following query, but it's not returning dates back in
the reverse chronological order:
SELECT DISTINCT
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date)
FROM uus INNER JOIN ui ON uus.user_id=ui.id INNER JOIN uu ON
ui.id=uu.user_id
WHERE uus.x_id=1
ORDER BY
date_part('year', uu.add_date), date_part('month', uu.add_date),
date_part('day', uu.add_date) DESC;
This is what the above query returns:
date_part | date_part | date_part
---+---+---
2004 | 2 | 6
2004 | 4 |20
(2 rows)
I am trying to get back something like this:
2004 4 20
2004 4 19
2004 2 6
...
My query is obviously wrong, but I can't see the mistake. I was
wondering if anyone else can see it. Just changing DESC to ASC, did
not work.
Thank you!
Otis
---(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] staggered query?
Hi Try this.. SELECT Col1 , Col2 FROM yourtable WHERE to_number(to_char(col1, 'SS'),'99') / 10 ) in (10,20,30,40,50,00); HTH Denis > - Original Message - > From: Vincent Ladlad <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, April 21, 2004 8:23 AM > Subject: [SQL] staggered query? > > > > hi! im new to SQL, and i need to find a solution > > to this problem: > > > > i have a table with two columns, the first column > > is of type timestamp. > > > > the table contains hundreds of thousands of records. > > i need to get all the entries/records at every 10 seconds > > interval. example, given a table: > > > > hh/mm/ss | data > > --- > > 00:00:00 1 > > 00:00:01 2 > > 00:00:02 3 > > 00:00:03 4 > > 00:00:04 5 > > 00:00:05 6 > > 00:00:06 7 > > 00:00:07 8 > > .. > > .. > > > > my query should return: > > 00:00:10 > > 00:00:20 > > 00:00:30 > > (etc) > > > > is this possible? if yes, how do i do it? > > > > thanks! > > > > --- > > Outgoing mail is certified Virus Free. > > Checked by AVG anti-virus system (http://www.grisoft.com). > > Version: 6.0.484 / Virus Database: 282 - Release Date: 5/27/2003 > > > > > > > > ---(end of broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > ---(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
