Re: Selecting rows from a distinct selection of one field

From: Adam Smith (private_at_hotmail.com)
Date: 12/09/04 

Date: Thu, 9 Dec 2004 14:10:26 -0000

Worked like a charm. It had a couple of dupes due to the time being only to
the second, but these are easy enough to erase.

pop by some time, I'll buy you a pint :D

Adam Smith

"Jacco Schalkwijk" <jacco.please.reply@to.newsgroups.mvps.org.invalid> wrote
in message news:u0AISle3EHA.536@TK2MSFTNGP10.phx.gbl...
> You can get the first row for each IP with:
> SELECT <column list>
> FROM log_table t1
> WHERE NOT EXISTS(SELECT NULL FROM log_table t2
> WHERE t1.IP = t2.IP AND (t2.date > t1.date OR (t2.date = t1.date AND
> t2.time > t1.time))
>
> This should perform reasonably well if you have an index on (IP, date,
> time)
>
> I see btw that we are almost neighbours ;-)
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
>
> "Adam Smith" <private@hotmail.com> wrote in message
> news:ehDM0bd3EHA.2804@TK2MSFTNGP15.phx.gbl...
>> I'm not that new to SQL, but still I can't figure this out.
>> I'm writing some routines to check through IIS logs.
>> After batch importing the logs I end up with 16 fields. Field 2 is the
>> IP addresses of our users. I can distinct this field and get all IPs
>> that access our website.
>>
>> What I then want to do, is using this list of ip's pull out 1 sample row
>> for each IP.
>> How do I go about doing this in pure sql (if I'd done it in ado I'd be
>> done by now).
>> Idealy I'm looking for a fast hitting sql query, as you can imagine the
>> logs can be quite large at times.
>>
>> Thanks in advance
>>
>> --
>> Regards,
>> Adam Smith
>> Assistant Computer Technician
>> Phone: +44 (0)20-8251-1332
>> E-mail: adam@101cd.com
>>
>> 101cd.com
>> 11 Keeley Road
>> Croydon
>> CR0 1TF
>>
>
>

Relevant Pages

  • Re: Selecting rows from a distinct selection of one field
    ... You can get the first row for each IP with: ... SQL Server MVP ... "Adam Smith" wrote in message ... > After batch importing the logs I end up with 16 fields. ...
    (microsoft.public.sqlserver.programming)
  • Re: Automated analysis of logs?
    ... > into (especially IIS logs) and get not only statistics, ... save output in some various format like XML, IIS log or directly to the SQL ... Is SPAM over-loading your e-mail server, ... SurfControl E-Mail Filter is flexible, ...
    (Security-Basics)
  • Checkboxes in a Datagrid problem
    ... I have a datagrid that retrieves 3 fields per record row from a SQL server ... Violation Notice Date Type of Violation ... show the same exact value from the first row. ... to save the bit value back to the database so records can be updated right ...
    (microsoft.public.dotnet.framework.aspnet.datagridcontrol)
  • Re: Select using short-circuit condition and breaking after first match
    ... The nature of SQL is that it deals with sets, ... there is no such thing as "the first row" in a result. ... I think that you meant to return the shortest prefix. ...
    (microsoft.public.sqlserver.programming)
  • Question on Import from Excel
    ... append it to a SQL 2000 table. ... The first row of the Excel named range ... The SQL table has some fields which are not ... The Excel named range contains the same field names in row 1 but there is no ...
    (microsoft.public.sqlserver.dts)
Quantcast