CODEWITHSUNDEEP
sqlsql-servert-sqlselect
yosh
yosh

Reputation: 3305

SQL select with conditions on previous rows

How to write such select:

SELECT filename, username, date_time, field1, field2... FROM... JOIN... WHERE...
UNLESS user_downloaded_this_file_today_already

I would like to ignore files downloaded by the same user on the same day. Data example:

12 | file1.jpg | barney | 2012-03-15 12:50:10 | ...
13 | file1.jpg | roger  | 2012-03-15 13:50:10 | ...
14 | file2.jpg | barney | 2012-03-15 14:50:10 | ...
15 | file1.jpg | barney | 2012-03-15 15:50:10 | ...

How do I write a SELECT that would ignore 4th line? Same filename, same user, day difference < 1. Is this actually possible?

Upvotes: 2

Views: 236

Answers (3)

Kaf
Kaf

Reputation: 33809

SELECT filename, username, date_time, field1, field2
FROM (
  SELECT filename, username, date_time, field1, field2,
  ROW_NUMBER() OVER (ORDER BY username,filename, CONVERT(date,date_time)) AS RN,
  RANK() OVER (ORDER BY username,filename, CONVERT(date,date_time)) AS R

  FROM [table]
) A
WHERE A.RN = A.R

Upvotes: 1

beny23
beny23

Reputation: 35008

In order to allow duplicates in the day, I think the following would suffice:

select filename, username, date_time, field1, field2
  from yourtable t
 where not exists (
    select 1 
      from yourtable
     where t.filename = filename
       and t.username = username
       and trunc(t.date_time) = trunc(date_time)
       and t.date_time > date_time)
 order by t.date_time

this will return the first requests for each file/user combination per day.

Upvotes: 4

James Hill
James Hill

Reputation: 61793

Simply use SELECT DISTINCT (assuming you don't need the numeric column since it's not in your select):

SELECT DISTINCT  FileName,
                 UserName,
                 Date_Time
FROM             [YourTable]

If your Date_Time column contains times and not just 00:00:00, you'll need to strip out the time for DISTINCT to work properly:

SELECT DISTINCT  FileName,
                 UserName,
                 CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, Date_Time))) AS Date_Time
FROM             [YourTable]

Here's an example of the date stripping in action.

Upvotes: 2

Related Questions