SQL query question

On Jun 18, 2006, at 8:50 , Kevin Jenkins wrote:

I have a beginner question. I have a table with a bunch of
filenames and each of them have a date. Multiple files may have
the same name. For example

filename date revision
file1 10/05/06 1
file1 10/05/07 2
file2 10/05/08 1

I want to do a query that will return the greatest date for each
unique filename

I can think of two ways to do this (and there are probably more): one
using standard SQL and one using PostgreSQL extensions. Here's the
standard SQL way:

SELECT filename, date, revision
FROM table_with_bunch_of_filenames
NATURAL JOIN (
SELECT filename, max(date) as date
FROM table_with_bunch_of_filenames
GROUP BY filename
) AS most_recent_dates;

If you don't need the revision, you can just use the subquery-- the
stuff in the
parentheses after NATURAL JOIN.

And here's the way using DISTINCT ON, which is a PostgreSQL extension.

SELECT DISTINCT ON (filename, date)
filename, date, revision
FROM table_with_bunch_of_filenames
ORDER BY filename, date desc;

Hope this helps.

Michael Glaesemann
grzm seespotcode net

On Sat, Jun 17, 2006 at 16:50:59 -0700,
Kevin Jenkins <gameprogrammer@rakkar.org> wrote:

For example

filename date revision
file1 10/05/06 1
file1 10/05/07 2
file2 10/05/08 1

I want to do a query that will return the greatest date for each
unique filename

If the revisions for a filename are guarenteed to be ordered by date, then
another alternative for you would be:

SELECT filename, max(modificationDate), max(revision)
FROM FileVersionHistory
GROUP BY filename
;

Is there a way to send and read binary data directly from memory,
without escaping characters, for SELECT and INSERT queries?

This is for a file repository, such as in source control.

I saw in the manual the section on bytea and binary data, but I don't
want to go through hundreds of megabytes of data escaping to send a
query nor unescaping to get the file back.

All the files I'm adding are already loaded in memory with some binary
modifications. I'm aware of the large object type, but this requires
that I write to disk first, which I hope isn't necessary.

Kevin Jenkins <gameprogrammer@rakkar.org> writes:

Is there a way to send and read binary data directly from memory,
without escaping characters, for SELECT and INSERT queries?

See PQexecParams --- an out-of-line bytea parameter, transmitted in
binary format, seems to be what you want on the send side. For
reading, just ask for the result in binary format.

regards, tom lane

Another way is to use correlated-subqueries (refrencing outer
query's columns inside a subquery; hope this feature is supported):

select *
from FileVersionHistory H1
where modificationDate = ( select max(modificationDate)
from FileVersionHistory H2
where H2.filename = H1.filename
);

And if you suspect that some different versions of a file might
have same Date, then you should add DISTINCT to 'select *', else
you'll get duplicates in the result.

Regards,
Gurjeet.

Greeting again,

I am writing records to postgreSQL from an IDE called revolution. At the
time I perform the INSERT command I need to retrieve the value of the
serial_id column from the newly created row.

Is it possible to have a specified column value returned after the
INSERT (rather than the number of rows affected) ?

That would save me doing a SELECT select statement after every INSERT.

Please excuse the terminology if it is not SQL'esque, but I hope you
know what I am getting at.

Thanks in advance

John Tregea

Sorry, I just realised this should have gone to the SQL list... (Bloody
Newbie's) :-[

John Tregea wrote:

John Tregea wrote:

Greeting again,

I am writing records to postgreSQL from an IDE called revolution. At the
time I perform the INSERT command I need to retrieve the value of the
serial_id column from the newly created row.

Is it possible to have a specified column value returned after the
INSERT (rather than the number of rows affected) ?

That would save me doing a SELECT select statement after every INSERT.

Please excuse the terminology if it is not SQL'esque, but I hope you
know what I am getting at.

Thanks in advance

John Tregea

It's not supported now, however it has been discussed several times, and
there is a TODO entry for it at

http://www.postgresql.org/docs/faqs.TODO.html

using syntax along the lines of INSERT ... RETURNING ...

Search for the word "returning" in the todo list and you'll find the
entry. Your options include waiting for someone to make it happen (no
telling how long that will be), or helping to make it happen (for which
we would all thank you :-) ). In the meantime you'll have to work around
it, as you suggested.

Tim

--
-----------------------------------------------
Tim Allen tim@proximity.com.au
Proximity Pty Ltd http://www.proximity.com.au/

Hi Tim,

Thanks for the advice, it saves me continuing to dig in the help files
and my reference books any longer. I don't know how much help I could be
in adding features but I am glad to participate in any way I can in the
community. I will follow your link to the TODO pages.

Thanks again.

Regards

John

Tim Allen wrote:

John Tregea wrote:

Greeting again,

I am writing records to postgreSQL from an IDE called revolution. At
the time I perform the INSERT command I need to retrieve the value of
the serial_id column from the newly created row.

We have an after-insert trigger that raises it as a notice. NOTICE
SKEY(xxx)

Tim Allen wrote:

using syntax along the lines of INSERT ... RETURNING ...

SQL Server had a nifty feature here. You could simply toss a SELECT
statement at the end of a trigger of sproc and the results would be
returned.

This in effect made a table the potential return type of all commands,
which could be exploited very powerfully.

Do the hackers have any thoughts along those lines?

SQL Server had a nifty feature here. You could simply toss a SELECT
statement at the end of a trigger of sproc and the results would be
returned.

This in effect made a table the potential return type of all commands,
which could be exploited very powerfully.

Do the hackers have any thoughts along those lines?

It's also a "for instance" where inline creation of variables is useful. As
in:

select id1 = nextval(somesequence)
insert into tbl (id...) values (id1...)
select id2 = nextval(somesequence)
insert into tbl (id...) values (id2...)
select id3 = nextval(somesequence)
insert into tbl (id...) values (id3...)
select id1, id2, id3;

Or returning multiple result sets...

insert into tbl (id...) values (nextval(somesequence)...) returning new.id;
insert into tbl (id...) values (nextval(somesequence)...) returning new.id;
insert into tbl (id...) values (nextval(somesequence)...) returning new.id;

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

Scott, Ken and Tim,

Thanks for the assistance, I appreciate the advice.

Scott,

The example of

select id1 = nextval(somesequence)

could work for me. I have multiple users with our GUI and imagine I
could use transaction protection to ensure no duplicates between
selecting and incrementing the somesequence...

Thanks again all.

Regards

John

Scott Ribe wrote:

John Tregea wrote:

The example of

select id1 = nextval(somesequence)

could work for me. I have multiple users with our GUI and imagine I
could use transaction protection to ensure no duplicates between
selecting and incrementing the somesequence...

You won't have duplicates[1]Unless you manage to make it wrap around after 2^32 (or 2^64?) calls of nextval. But that's quite unlikely. -- Alban Hertroys alban@magproductions.nl, it's a sequence. It's its purpose.

Now I may have missed something, I didn't follow this thread.

[1]: Unless you manage to make it wrap around after 2^32 (or 2^64?) calls of nextval. But that's quite unlikely. -- Alban Hertroys alban@magproductions.nl
of nextval. But that's quite unlikely.
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

On Mon, Jun 26, 2006 at 11:31:32AM +0200, Alban Hertroys wrote:

[1] Unless you manage to make it wrap around after 2^32 (or 2^64?) calls
of nextval. But that's quite unlikely.

Even then, only if have wrapping enabled. With wrapping disabled,
nextval() will simply fail rather than return a value already returned.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

You won't have duplicates[1], it's a sequence. It's its purpose.

Now I may have missed something, I didn't follow this thread.

Yes, what you quoted was more the intro. The actual question was how to find
out what ids were generated during a sequence of insertions.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

Scott Ribe wrote:

You won't have duplicates[1], it's a sequence. It's its purpose.

Now I may have missed something, I didn't follow this thread.

Yes, what you quoted was more the intro. The actual question was how to find
out what ids were generated during a sequence of insertions.

That's where you use currval ;)

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

Hi all,

Thanks for the continued suggestions on this question. I will reply
again once it is implemented and working.

Kind regards

John

Alban Hertroys wrote: