[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 797
  • Last Modified:

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)
0
mmsi
Asked:
mmsi
  • 3
1 Solution
 
jogosCommented:
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

0
 
Barry CunneyCommented:
SELECT * FROM  TBLSCANNEDJOBS s
JOIN
(
SELECT
JOBID,
MAX(Idx) IdxOfMostRecentScan
FROM TBLSCANNEDJOBS
GROUP BY JOBID
) r
ON r.Idx = s.Idx
0
 
jogosCommented:
In my inner select you must add the filter to receive only the ma(idx) from  records that match your filter

 where status = 8
0
 
jogosCommented:
And now I changed the max(idx) to max(scanDT) while that's what you asked

inner join (select max((ScanDT) as ScanDT,jobid 
                 from tblscannedjobs 
                 where status = 8
                 group by jobid) as m on m.jobid=s.jobid and m.ScanDT=s.ScanDT

Open in new window

0
 
mmsiAuthor Commented:
Thanks for the solution and quick response.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now