Обсуждение: SQL query question
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'
);
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.
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
>
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: > 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 >
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. >> >> 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 >
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: >> 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; > >
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], 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
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/ > From each according to his ability. To each according to his ability to litigate.
Вложения
> 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: > 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 ;) >