filter by first occurence of a distinct value

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

filter by first occurence of a distinct value

Author

Topic

maheshrblr
Starting Member

9 Posts

Posted - 2008-10-31 : 10:24:36

Greetings everybody,

i have a set of data retrieved from the database which is as follows--

SID LoginDateTime
------------------- -------------------
sqlsrv\IUSR_sqlsrv 2008-10-31 06:25:19
sqlsrv\IUSR_sqlsrv 2008-10-31 07:19:57
sqlsrv\prakash 2008-10-31 07:45:36
sqlsrv\netravathib 2008-10-31 07:48:17
sqlsrv\prasadsn 2008-10-31 07:57:31
sqlsrv\prasadsn 2008-10-31 07:58:55
sqlsrv\Vasudham 2008-10-31 08:00:55
sqlsrv\SwethaR 2008-10-31 08:02:26
sqlsrv\KavithaKN 2008-10-31 08:04:59
sqlsrv\kannank 2008-10-31 08:05:22
sqlsrv\kannank 2008-10-31 08:07:15
sqlsrv\RekhaR 2008-10-31 08:07:59
sqlsrv\netravathib 2008-10-31 08:08:46

Here i would like to sort the data by unique values of SID column

Next i would like to retain only the first occurence of date and time for the unique SID name and also remove the standard IUSR,IWAM,Administrator accounts..as below

SID LoginDateTime
------------------- -------------------
sqlsrv\prakash 2008-10-31 07:45:36
sqlsrv\netravathib 2008-10-31 07:48:17
sqlsrv\prasadsn 2008-10-31 07:57:31
sqlsrv\Vasudham 2008-10-31 08:00:55
sqlsrv\SwethaR 2008-10-31 08:02:26
sqlsrv\KavithaKN 2008-10-31 08:04:59
sqlsrv\kannank 2008-10-31 08:05:22
sqlsrv\RekhaR 2008-10-31 08:07:59

Kindly let me know the sql code to get the result--i have tried distinct on sid but it does not do the filtering part of sid column and returns all the rows..

Best Regards,
Mahesh

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-31 : 10:31:58

select SID, LogInDatetime from ( select SID, LoginDatetime, ROW_NUMBER() Over (partition by SID order by LoginDateTime asc) r from
<tab> ) where r = 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 10:40:12

If you dont use SQL Server 2005,

SELECT SID, MIN(LoginDateTime) as LoginDateTime FROM your_table
GROUP BY SID

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 10:43:43

quote:
Originally posted by madhivanan

If you dont use SQL Server 2005,

SELECT SID, MIN(LoginDateTime) as LoginDateTime FROM your_table
WHERE SID NOT LIKE '%\IUSR_%'
OR SID NOT LIKE '%\IWM_%'
OR SID NOT LIKE '%Administrator%'
GROUP BY SID

Madhivanan

Failing to plan is Planning to fail

maheshrblr
Starting Member

9 Posts

Posted - 2008-10-31 : 10:44:19

Hi hanbingl,

This is the actual command which generates the above data--

SELECT SID as LoginName, TimeWritten as LoginDateTime FROM seclist where timegenerated >= '2008-10-31 06:00:01' and timegenerated <='2008-10-31 23:59:59'"

i am not able to incorporate your code..please suggest / alter the actual command..

Best Regards,
Mahesh

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 10:45:06

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

If you dont use SQL Server 2005,

SELECT SID, MIN(LoginDateTime) as LoginDateTime FROM your_table
WHERE SID NOT LIKE '%\IUSR_%'
OR SID NOT LIKE '%\IWM_%'
OR SID NOT LIKE '%Administrator%'
GROUP BY SID

Madhivanan

Failing to plan is Planning to fail

Thanks

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 10:47:46

quote:
Originally posted by maheshrblr

Hi hanbingl,

This is the actual command which generates the above data--

SELECT SID as LoginName, MIN(TimeWritten) as LoginDateTime FROM seclist where timegenerated >= '2008-10-31 06:00:01' and timegenerated <='2008-10-31 23:59:59'
and (SID NOT LIKE '%\IUSR_%'
OR SID NOT LIKE '%\IWM_%'
OR SID NOT LIKE '%Administrator%')
group by SID

i am not able to incorporate your code..please suggest / alter the actual command..

Best Regards,
Mahesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 10:50:55

quote:
Originally posted by madhivanan

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

If you dont use SQL Server 2005,

SELECT SID, MIN(LoginDateTime) as LoginDateTime FROM your_table
WHERE SID NOT LIKE '%\IUSR_%'
OR SID NOT LIKE '%\IWM_%'
OR SID NOT LIKE '%Administrator%'
GROUP BY SID

Madhivanan

Failing to plan is Planning to fail

Thanks

Madhivanan

Failing to plan is Planning to fail

No problem

maheshrblr
Starting Member

9 Posts

Posted - 2008-10-31 : 10:59:38

Hi Everybody,

the script works and sorts the data based on the first occurence of the sid value--but the IUSR,Administrator account SID are still shown..anything wrong?? not able to figure it out..

Best Regards,
Mahesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 11:01:33

quote:
Originally posted by maheshrblr

Hi Everybody,

the script works and sorts the data based on the first occurence of the sid value--but the IUSR,Administrator account SID are still shown..anything wrong?? not able to figure it out..

Best Regards,
Mahesh

what's the administrator account?

try like below and see if you're still getting them

SELECT SID as LoginName, MIN(TimeWritten) as LoginDateTime FROM seclist where timegenerated >= '2008-10-31 06:00:01' and timegenerated <='2008-10-31 23:59:59'
and (SID NOT LIKE '%\IUSR%'
OR SID NOT LIKE '%\IWM%'
OR SID NOT LIKE '%Admin%')
group by SID

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-31 : 11:02:24

use AND

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 11:09:26

quote:
Originally posted by hanbingl

use AND

where? it should be OR as OP want exclude records contianing any one pattern.

maheshrblr
Starting Member

9 Posts

Posted - 2008-11-01 : 10:47:51

Greetings!!

The code of yesterday works fine for a date range of a day. The results produced are erroneus when the date range is changed for the entire month.

Example: where timegenerated >= '2008-10-01 00:00:01' and timegenerated <='2008-10-31 23:59:59'

WHen the above is executed the result consists mostly of oct 01 and only very few results are of other dates. PLease advise what changes has to be done to generate the report for an entire month.

Best Regards,
Mahesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-01 : 12:09:44

your where condition looks fine as long as timegenerated is a datetime field

maheshrblr
Starting Member

9 Posts

Posted - 2008-11-03 : 04:28:43

Greetings,

Looks like my question was not explained in detail-- well the code above works fine for a date range of one day

i.e SELECT SID as LoginName, MIN(TimeWritten) as LoginDateTime FROM seclist where timegenerated >= '2008-10-31 00:00:01' and timegenerated <='2008-10-31 23:59:59'
and (SID NOT LIKE '%\IUSR%' and SID NOT LIKE '%\IWM%' and SID NOT LIKE '%Admin%')group by SID

When the date range is changed to an entire month as below the results are corrupted meaning that it generates only for the first day and few results are randomly of other days.

SELECT SID as LoginName, MIN(TimeWritten) as LoginDateTime FROM seclist where timegenerated >= '2008-10-01 00:00:01' and timegenerated <='2008-10-31 23:59:59'
and (SID NOT LIKE '%\IUSR%' and SID NOT LIKE '%\IWM%' and SID NOT LIKE '%Admin%')group by SID

Generates result as below--

LoginName LoginDateTime
------------------- -------------------
sqlsrv\user1 2008-11-01 04:06:32
sqlsrv\user2 2008-11-01 07:15:00
sqlsrv\user3 2008-11-01 07:53:28
sqlsrv\user4 2008-11-01 08:00:32
sqlsrv\user5 2008-11-01 08:06:46
sqlsrv\user6 2008-11-01 08:07:11
sqlsrv\user7 2008-11-01 08:12:09
sqlsrv\user8 2008-11-01 08:14:02
sqlsrv\user9 2008-11-01 08:18:13
sqlsrv\user10 2008-11-01 08:19:12
sqlsrv\user11 2008-11-01 08:24:57
sqlsrv\user12 2008-11-01 08:25:36
sqlsrv\user13 2008-11-01 08:42:40
sqlsrv\user14 2008-11-01 08:54:13
sqlsrv\user15 2008-11-01 09:02:59
sqlsrv\user16 2008-11-01 09:23:47
sqlsrv\user17 2008-11-01 09:28:25
sqlsrv\user18 2008-11-01 09:36:26
sqlsrv\user19 2008-11-01 09:40:05
sqlsrv\user20 2008-11-01 10:17:33
sqlsrv\user21 2008-11-01 10:34:02
sqlsrv\user22 2008-11-01 13:33:51
sqlsrv\user23 2008-11-01 16:01:11
sqlsrv\user24 2008-11-03 08:10:39
sqlsrv\user25 2008-11-03 08:52:17
sqlsrv\user26 2008-11-03 08:53:02
sqlsrv\user27 2008-11-03 09:00:54
sqlsrv\user28 2008-11-03 09:50:45
sqlsrv\user29 2008-11-03 10:23:29

Typically the result should be like as below--

sqlsrv\user1 2008-10-01
sqlsrv\user2 2008-10-01
sqlsrv\user3 2008-10-01
sqlsrv\user4 2008-10-01
sqlsrv\user5 2008-10-01
sqlsrv\user6 2008-10-01
sqlsrv\user7 2008-10-01
sqlsrv\user8 2008-10-01

sqlsrv\user1 2008-10-02
sqlsrv\user2 2008-10-02
sqlsrv\user3 2008-10-02
sqlsrv\user4 2008-10-02
sqlsrv\user5 2008-10-02
sqlsrv\user6 2008-10-02
sqlsrv\user7 2008-10-02
sqlsrv\user8 2008-10-02

and so on till the last date of the month--
sqlsrv\user1 2008-10-31
sqlsrv\user2 2008-10-31
sqlsrv\user3 2008-10-31
sqlsrv\user4 2008-10-31
sqlsrv\user5 2008-10-31
sqlsrv\user6 2008-10-31
sqlsrv\user7 2008-10-31
sqlsrv\user8 2008-10-31

Best Regards,
Mahesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources