Hi!

First I want to say thanks for writing PostgreSQL. It's nice to have
a free alternative.

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

So the result would be
filename date revision
file1 10/05/07 2
file2 10/05/08 1

The best I can figure out is how to get the biggest date for a
particular named file:

SELECT * from FileVersionHistory WHERE modificationDate = (SELECT
max(modificationDate) FROM FileVersionHistory WHERE filename='File1');

The best I can accomplish is to run the query once for each file in a
loop in C++ code. But that's inefficient. I don't want to name the
files in the query.

I want one query that gives me the final result.

Ideas?

In case you need it, here's the table setup

const char *command =
"BEGIN;"
"CREATE TABLE Applications ("
"applicationKey serial PRIMARY KEY UNIQUE,"
"applicationName text NOT NULL UNIQUE,"
"installPath text NOT NULL,"
"changeSetID integer NOT NULL DEFAULT 0,"
"userName text NOT NULL"
");"
"CREATE TABLE FileVersionHistory ("
"applicationKey integer REFERENCES Applications ON DELETE CASCADE,"
"filename text NOT NULL,"
"content bytea,"
"contentHash bytea,"
"patch bytea,"
"createFile boolean NOT NULL,"
"modificationDate timestamp NOT NULL DEFAULT LOCALTIMESTAMP,"
"lastSentDate timestamp,"
"timesSent integer NOT NULL DEFAULT 0,"
"changeSetID integer NOT NULL,"
"userName text NOT NULL,"
"CONSTRAINT file_has_data CHECK ( createFile=FALSE OR ((content IS NOT
NULL) AND (contentHash IS NOT NULL) AND (patch IS NOT NULL)) )"
");"
"COMMIT;";

Add an application and file

-- Insert application
INSERT INTO Applications (applicationName, installPath, userName)
VALUES ('Game1', 'C:/', 'Kevin Jenkins');

-- Insert file (I would do this multiple times, once per file)
INSERT INTO FileVersionHistory (applicationKey, filename, createFile,
changeSetID, userName)
VALUES (
1,
'File1',
FALSE,
0,
'Kevin Jenkins'
);


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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




---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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
;

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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.


On 6/18/06, Bruno Wolff III <bruno@wolff.to> wrote:
> 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
> ;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

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
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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.
>>
>> 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
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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)


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org