asked on

SQL Distinct or Group by

I have a database table that contains all jobs that are scanned into our database. I want to return record information on the last scan for each job.

example

Idx jobid jobname quantity Staus ScanDT
-----------------------------------------------------------------------------------------------------------------------------------------
1 1234 test 10000 Inprogress 9/20/2012 3:58:48 pm
2 1234 test 10000 Complete 9/20/2012 4:00:65 pm
3 1234 test 10000 On Hold 9/20/2012 4:05:65 pm
4 7334 ......
5 7334 ......

*note above table is for demo only I will be using more columns than what is listed above

What I'm trying to accomplish here is to return one record for each job that was scanned. And the record I want returned needs to be the most recent scan for that job.

If I try and use distinct in my query, it will return rows 1 and 4, but that will not work because those records were not the last scan for each job.

After looking into further, I realize I should not be using distinct and should be using group by. However, I can not get it to return one record for each job.

Below Is my current SQL Statement.

SELECT DISTINCT S.JOBID, J.JOBNAME, J.QUANTITY, J.CSR, Q.ARTISTID,
J.PieceWork, Q.DEPTIDX, S.STATUS, Q.CSRID, D.INITIAL AS CSRINIT,
J.DROPDATE, J.JOBTYPE, R.DESCRIPTION, S.ScanDT
FROM TBLSCANNEDJOBS AS S
INNER JOIN TBLJOBS AS J ON S.JOBID = J.JOBID
INNER JOIN tblQueueLogs AS Q ON J.JobID = Q.JIDX
INNER JOIN tblUserDirectory AS D ON Q.CSRID = D.IDX
INNER JOIN TBLLOGREQUESTTYPES AS R ON S.STATUS = R.REQUESTTYPEIDX
INNER JOIN TBLJOBTYPE AS JT ON J.JOBTYPE = JT.JOBTYPEID
WHERE (S.STATUS = 8) AND (Q.DEPTIDX =7)

jogos

No distinct. Use group by in an inner select to have your max(idx)

inner join (select max((idx),jobid 
                 from tblscannedjobs 
                 group by jobid) as m on m.jobid=s.jobis and m.idx=s.idxx

Open in new window

Barry Cunney

SELECT * FROM TBLSCANNEDJOBS s
JOIN
(
SELECT
JOBID,
MAX(Idx) IdxOfMostRecentScan
FROM TBLSCANNEDJOBS
GROUP BY JOBID
) r
ON r.Idx = s.Idx

jogos

In my inner select you must add the filter to receive only the ma(idx) from records that match your filter

where status = 8

ASKER CERTIFIED SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mmsi

ASKER

Thanks for the solution and quick response.