Complicated Query - Please Help!!
-
I have tried to exlain this questiona few times and its hard to... bear with me.
I have a table that includes 3 fields: ProjectID, uploaddate, and Filename.
I want to return all the records
I want the records grouped by ProjectID
I want the Project with the most recent post (uploaddate) to return first, and then the second most recently updated Project, and so on. (this is where I seem to lose people).
Ideas??
Thanks!
-Jon
-
Won't an ORDER BY uploaddate DESC work?
-
SELECT ProjectID,
uploaddate,
Filename
FROM <table name>
GROUP BY ProjectID, uploaddate, Filename
ORDER BY ProjectID, uploaddate DESC
-
This doesn't really do it... see result below.... I need it to take whats below and sort the groups of projects by date... so that the project at the top is the one with the most recent new record (most recent uploaddate).
1, 2003-05-12 00:00:00, File name goes here
1, 2003-05-12 00:00:00, File name goes here
1, 2003-04-25 00:00:00, File name goes here
1, 2003-04-25 00:00:00, File name goes here
1, 2003-04-23 00:00:00, File name goes here
1, 2003-04-17 00:00:00, File name goes here
1, 2003-04-09 00:00:00, File name goes here
1, 2003-01-23 00:00:00, File name goes here
1, 2003-01-23 00:00:00, File name goes here
1, 2003-01-23 00:00:00, File name goes here
10, 2003-01-24 00:00:00, File name goes here
10, 2003-01-23 00:00:00, File name goes here
10, 2003-01-23 00:00:00, File name goes here
10, 2003-01-23 00:00:00, File name goes here
10, 2003-01-23 00:00:00, File name goes here
10, 2003-01-23 00:00:00, File name goes here
100, 2003-10-28 00:00:00, File name goes here
100, 2003-10-28 00:00:00, File name goes here
101, 2003-11-07 00:00:00, File name goes here
101, 2003-11-07 00:00:00, File name goes here
101, 2003-10-29 00:00:00, File name goes here
102, 2004-03-03 00:00:00, File name goes here
102, 2004-03-01 00:00:00, File name goes here
102, 2004-02-02 00:00:00, File name goes here
102, 2003-11-07 00:00:00, File name goes here
103, 2003-11-13 00:00:00, File name goes here
103, 2003-11-13 00:00:00, File name goes here
103, 2003-11-13 00:00:00, File name goes here
-
Here you go:
SELECT #t.ProjectID, mud, uploaddate, [Filename] FROM #t INNER JOIN (SELECT ProjectID, MAX(uploaddate) mud FROM #t GROUP BY ProjectID) s ON #t.ProjectID = s.ProjectID ORDER BY mud DESC, #t.projectID, uploaddate
It's the inner join to the same table to get the Max Upload Date that's the trick.
-
GreyLyon,
I see what you are trying to do, but I believe I messed the syntax in translation. What have I done wrong here? My table name is rfq_files.
ODBC Error Code = S0002 (Base table not found)
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'rqf_files'.
SQL = "SELECT rqf_files.ProjectID, mud, uploaddate, Filename FROM rqf_files INNER JOIN (SELECT ProjectID, MAX(uploaddate) mud FROM rqf_files GROUP BY ProjectID) s ON rqf_files.ProjectID = s.ProjectID ORDER BY mud DESC, rqf_files.projectID, uploaddate"
-
Double check the tablename, SQLServer doesn't think that table is in your DB. (you typed rFQ_files at top, but rQF_files in SQL statement...)
I bet if you do a simple 'SELECT TOP 10 * FROM rqf_files' You'll get the same error. (I only used the TOP predicate in case your table is large)
The syntax you typed in this thread looks correct to me.
-
GregLyon,
That was it!!! Thanks for this solution!! Thank you!!!
-Jon
-
by the way, what is 'mud DESC'??
-
Greg,
One more question, I currently use an inner join to pull in the name of the project from another table that has the project ID and the ProjectName. Can I include that inner join somehow in this query too?
The other table is table2 and the fields are projectID and ProjectName.
Thanks!
-
Lost my reply just now...Re: MUD DESC... mud is an abbreviation for Max Upload Date, the field in the subquery that gets the most recent activity for a project id. By using it in an ORDER BY clause DESCending order, we sort the project with the most recent activity first. (Probably confused things that I chose an abbreviation that is in itself a word, it's kind of a tongue-in-cheek thing I do sometimes
.)RE: Adding another table, SURE. Just add another join:
INNER JOIN Table2 to ON rfq.projectID = t2.projectID
Put it right before the ORDER BY clause, and add ProjectName to the SELECT list, probably right after ProjectID
-
jasals,
by the way, what is 'mud DESC'??
That is an alias for the column 'uploaddate' that had the max function applied to it. See what is bolded below:
SELECT #t.ProjectID, mud, uploaddate, [Filename]
FROM #t
INNER JOIN (SELECT ProjectID, MAX(uploaddate) mud FROM #t GROUP BY ProjectID) s
ON #t.ProjectID = s.ProjectID
ORDER BY mud DESC, #t.projectID, uploaddate
A column alias can be created using the word "as", as in "MAX(uploaddate) as mud", or "MAX(uploaddate) mud" (omit the quotation marks). Both forms work eqally well so it is just a matter of personal preference.
The "ORDER BY mud DESC" statement is so that the ouput from the query will be "ordered by" the "mud" column row with the most recent date being placed at the top of the table, the oldest date at the bottom of the table, and all intervening rows in corresponding order recent to oldest. It's simply a way of sorting the output of the query by date.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply