DISTINCT, GROUP BY?? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

DISTINCT, GROUP BY??

Author

Topic

mortolee
Starting Member

1 Post

Posted - 2007-11-22 : 12:15:19

Howdy,

Im trying to retrieve data from a couple of tables using the following...

SELECT 
SBM.ExternalBatchID,
(SELECT MAX(EndDateTime) FROM dbo.StatsModuleLaunch AS SML WHERE (SML.ModuleLaunchID = SBM.ModuleLaunchID) AND (SML.ModuleUniqueID = 'scan.exe'))
FROM dbo.StatsBatchModule AS SBM

ORDER BY SBM.ExternalBatchID ASC

Works fine but for one thing, it brings back 1 row for each ExternalBatchID with the latest scan date and time (which i want), but it also brings back some Null rows (which i dont want)

Here is an example of the retrieved data...
89 19/11/2007 16:33:11
89 NULL
90 19/11/2007 16:32:56
90 NULL
91 19/11/2007 16:34:26
91 NULL
91 NULL
91 NULL
92 NULL
92 22/11/2007 10:00:10
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
92 NULL
93 22/11/2007 12:32:57
93 NULL
93 NULL
93 NULL
93 NULL
93 NULL
93 NULL
93 NULL
93 NULL
93 NULL
93 NULL
94 NULL
94 NULL
94 22/11/2007 12:53:03
94 NULL
95 NULL
95 NULL
95 22/11/2007 12:57:26
95 NULL
95 NULL
95 NULL
95 NULL
96 NULL
96 22/11/2007 12:58:58
97 NULL
97 NULL
97 22/11/2007 13:01:18
97 NULL
97 NULL
98 NULL
98 NULL
98 NULL
98 NULL
98 NULL
98 NULL
98 NULL
98 NULL
98 NULL
98 NULL
98 22/11/2007 13:04:52
98 NULL
98 NULL
99 NULL
99 22/11/2007 13:57:30
99 NULL
99 NULL
99 NULL
100 22/11/2007 14:04:57
100 NULL
100 NULL
100 NULL
100 NULL
101 NULL
101 22/11/2007 14:09:24
101 NULL
101 NULL
102 NULL
102 NULL
103 NULL
103 22/11/2007 14:19:31
103 NULL
103 NULL
104 NULL
104 NULL
104 NULL
104 22/11/2007 15:01:32
105 22/11/2007 15:24:11
105 NULL
105 NULL
105 NULL
105 NULL
106 22/11/2007 15:27:55
106 NULL
106 NULL
106 NULL
107 NULL
107 22/11/2007 15:31:06
107 NULL
107 NULL
107 NULL

There are multiple entries in StatsBatchModule for 107 above, but i want to retrieve only the latest date/time with the 'scan.exe' value from StatsModuleLaunch, which doesnt have ExternalBatchID by the way.

So i want to be getting:
89 19/11/2007 16:33:11
90 19/11/2007 16:32:56
91 19/11/2007 16:34:26
92 22/11/2007 10:00:10
93 22/11/2007 12:32:57
94 22/11/2007 12:53:03
95 22/11/2007 12:57:26
96 22/11/2007 12:58:58
97 22/11/2007 13:01:18
98 22/11/2007 13:04:52
99 22/11/2007 13:57:30
100 22/11/2007 14:04:57
101 22/11/2007 14:09:24
103 22/11/2007 14:19:31
104 22/11/2007 15:01:32
105 22/11/2007 15:24:11
106 22/11/2007 15:27:55
107 22/11/2007 15:31:06

I've tried quite a lot including disctinct options and group by. but no luck.

Cheers,
Lee

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-11-22 : 12:51:06

[code]SELECT SBM.ExternalBatchID, MAX(SML.EndDateTime) AS EndDateTime
FROM dbo.StatsBatchModule SBM
JOIN dbo.StatsModuleLaunch SML
ON SMB.ModuleLaunchID = SBL.ModuleLaunchID
WHERE SML.ModuleUniqueID = 'scan.exe'
GROUP BY SBM.ExternalBatchID[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources