Using the Distinct and Max togehter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using the Distinct and Max togehter

Author

Topic

cxmoore
Starting Member

9 Posts

Posted - 2009-05-19 : 13:56:47

I have a barcode sortation DB that I'm trying to query and having a little trouble. I have a table called "Mission" and it is a one-to-many. At any rate it sends a new record to a 'COMPLETE'table anytime the barcode is scanned meaning I have multiple records for the same barcode. What make each of the different is a "TimeSequence" that is populated in each record. In short what I want to do is grab the newest record for each barcode (only one record for each barcode. ANy suggestion?

TRYING SOMETHING LIKE THIS
SELECT Distinct (SortD.Mission.Mission),SortD.MissionDetail.LogicalDestinationID, MAX (SortD.MissionComplete.TimeSequence)AS LASTRECORD

FROM SortD.Mission INNER JOIN
SortD.MissionDetail ON SortD.Mission.Mission = SortD.MissionDetail.Mission INNER JOIN
SortD.MissionComplete ON SortD.Mission.Mission = SortD.MissionComplete.Mission
GROUP BY SortD.Mission.Mission,SortD.MissionDetail.LogicalDestinationID;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 14:02:15

[code]
SELECT SortD.Mission.Mission,SortD.MissionDetail.LogicalDestinationID, mcom.*
FROM SortD.Mission
INNER JOIN SortD.MissionDetail
ON SortD.Mission.Mission = SortD.MissionDetail.Mission
INNER JOIN
(SELECT mc.*
FROM
SortD.MissionComplete mc
INNER JOIN (SELECT Mission,MAX(TimeSequence)AS LASTRECORD
FROM SortD.MissionComplete
GROUP BY Mission)mc1
ON mc1.Mission =mc.Mission
AND mc1.LASTRECORD=mc.TimeSequence
)mcom
ON SortD.Mission.Mission = mcom.Mission
[/code]

cxmoore
Starting Member

9 Posts

Posted - 2009-05-19 : 15:17:46

Thanks for the information but I have to say I don't understand what the mc is? I wanted to add a WHERE clause so that I grab only the records related to the the current date. Example

(DateDiff(Day,SortD.MissionComplete.Timestamp,GETDATE())=0) but wanted to make sure I put it in the correct place any help would be of GREAT help. Thanks

cxmoore
Starting Member

9 Posts

Posted - 2009-05-19 : 15:47:17

THis did not seem to work as I returned the same barcode several times in the query? It looked like it would work but did not. Thanks

cxmoore
Starting Member

9 Posts

Posted - 2009-05-19 : 16:01:05

I really feel like a du@#$%&. I don't know what i was thinking? I just looked at the query a little more and noticed that it was because of the join and it appears to be a virtual table? Alias

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 12:41:32

yup..it was ...did it work for you?

cxmoore
Starting Member

9 Posts

Posted - 2009-05-21 : 10:46:52

It did not. The problem that I run into is that when I write the sub-query I can return what would appear to be the correct number of rows, meaing that I get only (1) row returned for every barcode along with the "LastTimeInducted" (Timestamp). If I add any other colums to the query so that I can view them and use them in the next part of the whole query I have to use the the additional colums in the "GROUP BY". This makes for a big problem because it then goes out and returns a DISTINCT Barcode but it will also use the scanner ID , etc. as a part of the process returning the same barcode multiple times. The query that does return the correct number of rows looks like this but again it is on the first part of the whole query.

SELECT DISTINCT(SortD.MissionComplete.Mission),MAX(SortD.MissionComplete.LastTimeInducted)
FROM SortD.MissionComplete
WHERE (DATEDIFF(DAY, SortD.MissionComplete.Timestamp, GETDATE()) = 0)AND NOT SortD.MissionComplete.Mission LIKE '[9999]%'
GROUP BY Mission

The next step is to use that (EXISTS) information in the next query to return the rows that meeting the conditions of the WHERE that I have put togehter. When I do this I have a problem with creating a "NESTED LOOP" because of putting the same table name in the FROm of the uppermost/innermost part of the whole query (Step 2) of the overall execution plan. The screwed-up query looks like this.
SELECT SortD.MissionComplete.Mission, SortD.MissionComplete.ScannerID, SortD.MissionComplete.DispositionStatus
FROM SortD.MissionComplete
WHERE SortD.MissionComplete.ScannerID = '23' AND SortD.MissionComplete.DispositionStatus = 'SUCCESS' AND Exists
(SELECT DISTINCT(SortD.MissionComplete.Mission),MAX(SortD.MissionComplete.LastTimeInducted)
FROM SortD.MissionComplete
WHERE (DATEDIFF(DAY, SortD.MissionComplete.Timestamp, GETDATE()) = 0)AND NOT SortD.MissionComplete.Mission LIKE '[9999]%'
GROUP BY Mission)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 03:28:06

are you looking for this?


SELECT mc.Mission, SortD.MissionComplete.ScannerID, mc.DispositionStatus
FROM SortD.MissionComplete mc
INNER JOIN 
(SELECT SortD.MissionComplete.Mission,MAX(SortD.MissionComplete.LastTimeInducted)  AS LatestTime
FROM SortD.MissionComplete
WHERE (DATEDIFF(DAY, SortD.MissionComplete.Timestamp, GETDATE()) = 0)AND SortD.MissionComplete.Mission NOT LIKE '[9999]%'
GROUP BY Mission)mc1
ON mc1.Mission=mc.Mission
AND mc1.LatestTime=mc.LastTimeInducted
WHERE mc.ScannerID = '23' AND mc.DispositionStatus = 'SUCCESS'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources