Query Help

Question

Hi ,

I have a table which contains :id, emailaddress, importflag, updatedatetime

I need to retrieve all the emailaddress from the table which have importflag='N' for most recent updatedatetime

Sample Table and Data is below :

CREATE TABLE #test1  (id INT IDENTITY(1,1), 
                      emailaddress VARCHAR(128), 
                      importflag CHAR(1),
                      updatedatetime DATETIME )
                      
INSERT INTO #test1 VALUES ( '[e-mail]', 'Y','2012-08-17')
INSERT INTO #test1 VALUES ( '[e-mail]', 'N','2012-08-16')
INSERT INTO #test1 VALUES ( '[e-mail]', 'N','2012-08-14')
INSERT INTO #test1 VALUES ( '[e-mail]', 'Y','2012-08-13')

The table data looks like this :

The desired output:

Please guide ...

Thanks,

---

EVA05

Answer 1

Use the ROW_NUMBER() function for this; perhaps something like this:

;with cte as
( select
    emailAddress,
    importFlag,
    row_Number() over
    ( partition by emailAddress
      order by updateDatetime desc
    ) as Rn
  from yourTable
)
  select emailAddress
  from cte
  where rn = 1
    and importFlag = 'N'
;

Answer 2

WITH CTE AS

(SELECT EMAILADDRESS, IMPORTFLAG, UPDATEDATETIME FROM #TABLE WHERE IPORTFLAG='N')

select EMAILADDRESS FROM CTE WHERE UPDATEDATETIME=(SELECT MAX(UPDATEDATETIME FROM CTE)

---

ANK HIT - if reply helps, please mark it as ANSWER or helpful post