By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,940 Members | 614 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,940 IT Pros & Developers. It's quick & easy.

Q: Simple SQL query question

P: n/a
i have a table with picture filenames, dates, and projectnumbers

i would like to select the 4 most recent filenames for each
projectnumber.

i started thinking like...

select top 4 PicFilename from tblPictures P
ORDER BY picDate DESC;

but i'm not sure how to manage teh projectnumber bit. I'm thinking
i'd have to join the table with itself?

thx
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
ev********@yahoo.com (Evil) wrote in news:e7**************************@posting.google.c om:
i have a table with picture filenames, dates, and projectnumbers

i would like to select the 4 most recent filenames for each
projectnumber.

i started thinking like...

select top 4 PicFilename from tblPictures P
ORDER BY picDate DESC;

but i'm not sure how to manage teh projectnumber bit. I'm thinking
i'd have to join the table with itself?

thx


same sort of thing as Pablo's question, 9 minutes before you posted:

SELECT aa.ProjectNum, aa.PicFilename, aa.picDate, count(bb.ProjectNum) as SampleNum
FROM tblPictures as aa, tblPictures as bb
WHERE aa.ProjectNum = bb.ProjectNum and aa.picDate >= bb.picDate
GROUP BY aa.ProjectNum, aa.PicFilename, aa.picDate
HAVING count(bb.ProjectNum) <= 4

It's not a true join because of the inequality in the where condition.

--
Ross Presser -- rpresser AT imtek DOT com
.... seeking a new quote ...
Nov 12 '05 #2

P: n/a
On 10 Oct 2003 11:55:27 -0700 in comp.databases.ms-access,
ev********@yahoo.com (Evil) wrote:
i have a table with picture filenames, dates, and projectnumbers

i would like to select the 4 most recent filenames for each
projectnumber.

i started thinking like...

select top 4 PicFilename from tblPictures P
ORDER BY picDate DESC;

but i'm not sure how to manage teh projectnumber bit. I'm thinking
i'd have to join the table with itself?

just add the other fields in,
select top 4 PicFilename,projectnumber from tblPictures P
ORDER BY picDate DESC;

or select top 4 * from ...

--
A)bort, R)etry, I)nfluence with large hammer.
Nov 12 '05 #3

P: n/a
JL
Mr. Presser's elegant solution has one small logic error: aa.picDate >=
bb.picDate should be aa.picDate <= bb.picDate. This will list the four most
recent filenames by date for each project number.

SELECT aa.ProjectNum, aa.PicFilename, aa.picDate, count(bb.ProjectNum) as
SampleNum
FROM tblPictures as aa, tblPictures as bb
WHERE aa.ProjectNum = bb.ProjectNum and aa.picDate <= bb.picDate
GROUP BY aa.ProjectNum, aa.PicFilename, aa.picDate
HAVING count(bb.ProjectNum) <= 4
"Evil" <ev********@yahoo.com> wrote in message
news:e7**************************@posting.google.c om...
i have a table with picture filenames, dates, and projectnumbers

i would like to select the 4 most recent filenames for each
projectnumber.

i started thinking like...

select top 4 PicFilename from tblPictures P
ORDER BY picDate DESC;

but i'm not sure how to manage teh projectnumber bit. I'm thinking
i'd have to join the table with itself?

thx



-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #4

P: n/a
"JL" <JL@JL.COM> wrote in news:3f********@corp.newsgroups.com:
Mr. Presser's elegant solution has one small logic error: aa.picDate
= bb.picDate should be aa.picDate <= bb.picDate. This will list the

four most recent filenames by date for each project number.

Oops! Thanks for catching that.
Nov 12 '05 #5

P: n/a
Ross Presser <rp******@NOSPAM.imtek.com.invalid> wrote in message news:<Xn**********************@129.250.170.82>...
ev********@yahoo.com (Evil) wrote in news:e7**************************@posting.google.c om:
i have a table with picture filenames, dates, and projectnumbers

i would like to select the 4 most recent filenames for each
projectnumber.

i started thinking like...

select top 4 PicFilename from tblPictures P
ORDER BY picDate DESC;

but i'm not sure how to manage teh projectnumber bit. I'm thinking
i'd have to join the table with itself?

thx


same sort of thing as Pablo's question, 9 minutes before you posted:

SELECT aa.ProjectNum, aa.PicFilename, aa.picDate, count(bb.ProjectNum) as SampleNum
FROM tblPictures as aa, tblPictures as bb
WHERE aa.ProjectNum = bb.ProjectNum and aa.picDate >= bb.picDate
GROUP BY aa.ProjectNum, aa.PicFilename, aa.picDate
HAVING count(bb.ProjectNum) <= 4

It's not a true join because of the inequality in the where condition.


thanx. it's exactly what i'm looking for. Now i'll spend some time
trying to understand how this manages to work...
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.