SELECT TOP 10 J.jobID, J.jobName, 
                MAX(D.entryTime) AS latest_entryTime
  FROM JOB_jobs J
  JOIN JOB_jobLog ON JOB_jobs.jobID = JOB_jobLog.jobID
  GROUP BY J.jobID, J.jobName
  ORDER BY MAX(D.entryTime) DESC

quote:

---

This is the crux of the problem and one of the limitations I've never really understood about DISTINCT - all the fields you can search by have to be in the select list, vastly limiting its power. Am I missing something obvious? I'm self taught and it happens a lot!

---

   SELECT DISTINCT X FROM TABLE_A

   SELECT X FROM TABLE_A GROUP BY X

quote:

---

Originally posted by tony7671

Not sure the previous example does exactly what you need.

You maybe need an aggregate query, since what you're really doing is and implicit "MAX(entryTime)":

  SELECT TOP 10 J.jobID, J.jobName, 
                MAX(D.entryTime) AS latest_entryTime
  FROM JOB_jobs J
  JOIN JOB_jobLog ON JOB_jobs.jobID = JOB_jobLog.jobID
  GROUP BY J.jobID, J.jobName
  ORDER BY MAX(D.entryTime) DESC

quote:

---

This is the crux of the problem and one of the limitations I've never really understood about DISTINCT - all the fields you can search by have to be in the select list, vastly limiting its power. Am I missing something obvious? I'm self taught and it happens a lot!

---

You're not really searching by the field - you can have WHERE conditions that use fields that are not in the select list (or in this case the "GROUP BY"). You are trying to order by a field not in the select list though, so effectively it's telling you that it doesn't know what you mean since it doesn't know which of the potentially different entry times for each job ID you want it to order by. The aggregate query spells this out.

In a way, "SELECT DISTINCT" is a subset of what you can do with an aggregate query - e.g.

   SELECT DISTINCT X FROM TABLE_A

is pretty much functionally equivalent to:

   SELECT X FROM TABLE_A GROUP BY X

but you can go a lot further with the latter!

---

SELECT   idtable.id 
         , idtable.modifyuser 
         , datetable.modifydate 
FROM     
(SELECT   TOP 10 
		gbr.modifyuser 
    ,	Max(gbr.modifydate) AS modifydate 
 FROM     
	tblgebruikers gbr 
 WHERE    
	gbr.modifyuser IS NOT NULL 
 GROUP BY gbr.modifyuser
 ) AS datetable 
 INNER JOIN 
 (SELECT   TOP 10 
		gbr.modifyuser 
     ,	Max(gbr.gebruikerid) AS id 
  FROM     
	tblgebruikers gbr 
  WHERE    gbr.modifyuser IS NOT NULL 
  GROUP BY modifyuser
  ) idtable 
	  ON idtable.modifyuser = datetable.modifyuser 
ORDER BY datetable.modifydate DESC