Querying Data with Nested subqueries

[gavjb]

Hi,

I have a Notes table with a notesid field, jobref field & date field where I want to return the record with the earliest date by jobref

Notes ID   JobRef     NotesDate      Notes
1          MY-06-014  11/05/06 11:23 test 1
2          MY-06-014  11/05/06 11:27 test 2
3          MY-06-010  11/05/06 11:35 test 3
4          MY-06-010  11/05/06 11:27 test 4

from the above I want to display just records 1 and 4 as they have the lowest NotesDate for the jobref, only problem is I cant work out how to do this, I need to be able to do this in a way where I can edit the data in a form.

Also the data I am using is stored in SQL Server with ADP FE.


Thanks,


Gavin,

 

[lespaul]

if you don't need the NotesID:

SELECT JobRef, Notes, Min(NotesDate) From TableName GROUP BY JobRef, Notes

if you need the NoteID in the query results it will need to be done like this:

SELECT NotesID, JobRef, Notes, NotesDate FROM TableName INNER JOIN (SELECT NotesID, Min(NotesDate) FROM TableName GROUP BY NotesID) As A On TableName.NotesID = A.NotesID



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[HarleyQuinn]

Start with a sub query along the lines of:

SELECT JobRef, MIN(NotesDate) EarliestNote
FROM Notes
GROUP BY JobRef

Then around that query select all of the values from Notes where the JobRef AND NotesDate equal the subquery (Join on those fields).

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Leslie - If you use the Notes or NotesID fields won't you return all of the records as the NotesId and Notes fields are different for each record?

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

SELECT A.NotesID, A.JobRef, A.Notes, A.NotesDate
FROM TableName AS A INNER JOIN (
SELECT NotesID, Min(NotesDate) AS MinDate FROM TableName GROUP BY NotesID
) AS M ON A.NotesID = M.NotesID AND A.NotesDate = M.MinDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gavjb]

Thanks for all your help, I got it working with a few changes from your suggestions.

SELECT A.NotesID, A.JobRef, A.Notes, A.NotesDate
FROM Notes AS A INNER JOIN (
SELECT JobRef, Min(NotesDate) AS MinDate FROM Notes GROUP BY JobRef
) As M ON A.JobRef = M.JobRef AND A.NotesDate = M.MinDate
ORDER BY NotesID

 

[HarleyQuinn]

PHV - wouldn't that return all of the records also as the NotesID is unique?

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Sorry didn't mean to carry on a finished thread, hadn't realised that the OP had got the query working

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.