HELP FIND THE LAST 3 RECORDS - 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)

HELP FIND THE LAST 3 RECORDS

Author

Topic

dockies
Starting Member

25 Posts

Posted - 2009-01-08 : 06:44:52

I have 2 tables

Table 1: tbl_subscriber
PKID
lastname
firstname

Table 2: tbl_recipient
PKID
FKsubscriber id
newsletter id
date sent

I need to get the last 3 newsletter_id of each subscriber.
since it is per subscriber the last 3 newsletter will depend in their actual record because some subscriber have recieved fewer newsletter than the others. so i guess one of the keys in determining the last 3 newsletter is by date sent.

here is my query so far:

SELECT
TBL_RECIPIENT.newsletter_id,
dbo.tbl_subscriber.ID,
dbo.tbl_subscriber.first_name,
dbo.tbl_subscriber.last_name
FROM
tbl_subscriber
INNER JOIN
(
SELECT TOP(3) subscriber_id, views, newsletter_id
FROM dbo.tbl_recipient
WHERE (status = 'SENT')
ORDER BY date_sent DESC
) AS TBL_RECIPIENT ON TBL_RECIPIENT.subscriber_id = dbo.tbl_subscriber.ID
WHERE TBL_RECIPIENT.views > 0

the problem with my query is it only retrieves the last 3 record of the tbl_recipient not the last 3 newsletter_id of the subscriber.

can somebody help me with this?

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-08 : 07:07:22

try this

SELECT
TBL_RECIPIENT.newsletter_id,
dbo.tbl_subscriber.ID,
dbo.tbl_subscriber.first_name,
dbo.tbl_subscriber.last_name
FROM
tbl_subscriber
INNER JOIN
(
SELECT TOP(3) subscriber_id, views, newsletter_id
FROM dbo.tbl_recipient
WHERE (status = 'SENT')
group by subscriber_id,views, newsletter_id
ORDER BY date_sent DESC
) AS TBL_RECIPIENT ON TBL_RECIPIENT.subscriber_id = dbo.tbl_subscriber.ID
WHERE TBL_RECIPIENT.views > 0

splosh
Starting Member

8 Posts

Posted - 2009-01-08 : 07:09:27

Try joining to this (air code, may need a tweak):

select subscriber_id, newsletter_id
from (select subscriber_id, newsletter_id, rank () over (partition by subscriber_id order by date_sent desc) as rank
where status = 'sent'
and views > 0) a
where rank in (1,2,3)

You'll need to be on 2005 for this btw.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-08 : 07:10:53

If u are using sqlserver 2005

try this

SELECT
TBL_RECIPIENT.newsletter_id,
dbo.tbl_subscriber.ID,
dbo.tbl_subscriber.first_name,
dbo.tbl_subscriber.last_name
FROM
tbl_subscriber
INNER JOIN
(
SELECT row_number() ( over partition by subscriber_id order by date_sent DESC ) as sno , subscriber_id, views, newsletter_id
FROM dbo.tbl_recipient
WHERE (status = 'SENT')
) AS TBL_RECIPIENT ON TBL_RECIPIENT.subscriber_id = dbo.tbl_subscriber.ID
WHERE TBL_RECIPIENT.views > 0 and TBL_RECIPIENT.sno < 4

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-08 : 07:12:59

quote:
Originally posted by splosh

Try joining to this (air code, may need a tweak):

select subscriber_id, newsletter_id
from (select subscriber_id, newsletter_id, rank () over (partition by subscriber_id order by date_sent desc) as rank
where status = 'sent'
and views > 0) a
where rank in (1,2,3)

You'll need to be on 2005 for this btw.

From clause is missing in derived table 'a'

dockies
Starting Member

25 Posts

Posted - 2009-01-08 : 07:18:50

quote:
Originally posted by raky

If u are using sqlserver 2005

try this

SELECT
TBL_RECIPIENT.newsletter_id,
dbo.tbl_subscriber.ID,
dbo.tbl_subscriber.first_name,
dbo.tbl_subscriber.last_name
FROM
tbl_subscriber
INNER JOIN
(
SELECT row_numeber ( over partition by subscriber_id order by date_sent DESC ) as sno , subscriber_id, views, newsletter_id
FROM dbo.tbl_recipient
WHERE (status = 'SENT')
) AS TBL_RECIPIENT ON TBL_RECIPIENT.subscriber_id = dbo.tbl_subscriber.ID
WHERE TBL_RECIPIENT.views > 0 and TBL_RECIPIENT.sno < 4

I tried this but i got this error:

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'over'.

I also correct the row_numeber to row_number.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-08 : 07:21:46

quote:
Originally posted by dockies

quote:
Originally posted by raky

If u are using sqlserver 2005

try this

SELECT
TBL_RECIPIENT.newsletter_id,
dbo.tbl_subscriber.ID,
dbo.tbl_subscriber.first_name,
dbo.tbl_subscriber.last_name
FROM
tbl_subscriber
INNER JOIN
(
SELECT row_numeber ( over partition by subscriber_id order by date_sent DESC ) as sno , subscriber_id, views, newsletter_id
FROM dbo.tbl_recipient
WHERE (status = 'SENT')
) AS TBL_RECIPIENT ON TBL_RECIPIENT.subscriber_id = dbo.tbl_subscriber.ID
WHERE TBL_RECIPIENT.views > 0 and TBL_RECIPIENT.sno < 4

I tried this but i got this error:

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'over'.

I also correct the row_numeber to row_number.

try this

try this

SELECT
TBL_RECIPIENT.newsletter_id,
dbo.tbl_subscriber.ID,
dbo.tbl_subscriber.first_name,
dbo.tbl_subscriber.last_name
FROM
tbl_subscriber
INNER JOIN
(
SELECT row_number() ( over partition by subscriber_id order by date_sent DESC ) as sno , subscriber_id, views, newsletter_id
FROM dbo.tbl_recipient
WHERE (status = 'SENT')
) AS TBL_RECIPIENT ON TBL_RECIPIENT.subscriber_id = dbo.tbl_subscriber.ID
WHERE TBL_RECIPIENT.views > 0 and TBL_RECIPIENT.sno < 4

dockies
Starting Member

25 Posts

Posted - 2009-01-08 : 07:25:46

quote:
Originally posted by raky

quote:
Originally posted by dockies

quote:
Originally posted by raky

If u are using sqlserver 2005

try this

SELECT
TBL_RECIPIENT.newsletter_id,
dbo.tbl_subscriber.ID,
dbo.tbl_subscriber.first_name,
dbo.tbl_subscriber.last_name
FROM
tbl_subscriber
INNER JOIN
(
SELECT row_numeber ( over partition by subscriber_id order by date_sent DESC ) as sno , subscriber_id, views, newsletter_id
FROM dbo.tbl_recipient
WHERE (status = 'SENT')
) AS TBL_RECIPIENT ON TBL_RECIPIENT.subscriber_id = dbo.tbl_subscriber.ID
WHERE TBL_RECIPIENT.views > 0 and TBL_RECIPIENT.sno < 4

I tried this but i got this error:

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'over'.

I also correct the row_numeber to row_number.

try this

try this

SELECT
TBL_RECIPIENT.newsletter_id,
dbo.tbl_subscriber.ID,
dbo.tbl_subscriber.first_name,
dbo.tbl_subscriber.last_name
FROM
tbl_subscriber
INNER JOIN
(
SELECT row_number() ( over partition by subscriber_id order by date_sent DESC ) as sno , subscriber_id, views, newsletter_id
FROM dbo.tbl_recipient
WHERE (status = 'SENT')
) AS TBL_RECIPIENT ON TBL_RECIPIENT.subscriber_id = dbo.tbl_subscriber.ID
WHERE TBL_RECIPIENT.views > 0 and TBL_RECIPIENT.sno < 4

Its not working either.
I change it to:
SELECT
TBL_RECIPIENT.newsletter_id,
dbo.tbl_subscriber.ID,
dbo.tbl_subscriber.first_name,
dbo.tbl_subscriber.last_name
FROM
tbl_subscriber
INNER JOIN
(
SELECT row_number() over( partition by subscriber_id order by date_sent DESC ) as sno , subscriber_id, views, newsletter_id
FROM dbo.tbl_recipient
WHERE (status = 'SENT')
) AS TBL_RECIPIENT ON TBL_RECIPIENT.subscriber_id = dbo.tbl_subscriber.ID
WHERE TBL_RECIPIENT.views > 0 and TBL_RECIPIENT.sno < 4

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 07:32:34

[code]SELECT ID,
FirstName,
LastName,
NewsLetter_ID,
Date_Sent
FROM (
SELECT s.ID,
s.FirstName,
s.LastName,
ROW_NUMBER() OVER (PARTITION BY s.ID ORDER BY r.Date_Sent DESC) AS recID,
r.ID,
r.NewsLetter_ID,
r.Date_Sent
FROM tbl_Subscriber AS s
INNER JOIN tbl_Recipient AS r ON r.Subscriber_ID = s.ID
) AS d
WHERE recID < 4
ORDER BY ID,
Date_Sent DESC[/code]

E 12°55'05.63"
N 56°04'39.26"

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-08 : 07:32:56

ok then u please send some data and expected o/p

dockies
Starting Member

25 Posts

Posted - 2009-01-08 : 07:34:16

Hi thanks for all the replies.

I forgot to add a very important detail in my post.

I need to make sure that all the last 3 newsletter_id of each recipient has a view > 0 if only 2 of the last 3 newsletter_id has a view > 0 then the subscriber will not be in the list.

to explain it more.

subscriber_ID---newsletter_id----views
1--------------------1-------------1
1--------------------3-------------1
1--------------------7-------------1

the subscriber id 1 should be in the list

subscriber_ID---newsletter_id----views
2--------------------1-------------1
2--------------------3-------------0
2--------------------7-------------1

subscriber id 2 will show in the list.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-08 : 07:39:43

hi,

small modification to peso suggestion

try this

SELECT ID,
FirstName,
LastName,
NewsLetter_ID,
Date_Sent
FROM (
SELECT s.ID,
s.FirstName,
s.LastName,
ROW_NUMBER() OVER (PARTITION BY s.ID ORDER BY r.Date_Sent DESC) AS recID,
r.ID,
r.NewsLetter_ID,
r.Date_Sent,
r.views
FROM tbl_Subscriber AS s
INNER JOIN tbl_Recipient AS r ON r.Subscriber_ID = s.ID
) AS d
WHERE recID < 4 and d.views > 0
ORDER BY ID,
Date_Sent DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 07:42:40

raky, that will NOT help if at least one of the view records have a view > 0.
OP states ALL three latest will have to have a view > 0.

SUM(SIGN(views)) OVER (PARTITON BY ID ORDER BY DateSent) = 3

E 12°55'05.63"
N 56°04'39.26"

dockies
Starting Member

25 Posts

Posted - 2009-01-08 : 07:45:45

quote:
Originally posted by Peso

raky, that will NOT help if at least one of the view records have a view > 0.
OP states ALL three latest will have to have a view > 0.

SUM(SIGN(views)) OVER (PARTITON BY ID ORDER BY DateSent) = 3

E 12°55'05.63"
N 56°04'39.26"

Yes thats true but i need to correct that its the last 3 not the latest 3:) thanks

dockies
Starting Member

25 Posts

Posted - 2009-01-08 : 07:58:00

SUM(SIGN(views)) OVER (PARTITON BY ID ORDER BY DateSent) = 3

hi peso are you telling me to use this?

could you tell me where exactly should i put this code?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 08:08:06

Something similar to this...

SELECT		s.ID,
		s.FirstName,
		s.LastName
FROM		tbl_Subscriber AS s
CROSS APPLY	(
			SELECT TOP 3	f.NewsLetter_ID,
					f.Date_Sent,
					SUM(SIGN(f.views)) OVER (PARTITION BY f.Subscriber_ID) AS numview
			FROM		tbl_Recipient AS f
			WHERE		f.Subscriber_ID = s.ID
			ORDER BY	f.Date_Sent DESC
		) AS r
WHERE		r.numview = 3

E 12°55'05.63"
N 56°04'39.26"

dockies
Starting Member

25 Posts

Posted - 2009-01-08 : 08:34:38

quote:

Originally posted by Peso

Something similar to this...

SELECT		s.ID,
		s.FirstName,
		s.LastName
FROM		tbl_Subscriber AS s
CROSS APPLY	(
			SELECT TOP 3	f.NewsLetter_ID,
					f.Date_Sent,
					SUM(SIGN(f.views)) OVER (PARTITION BY f.Subscriber_ID) AS numview
			FROM		tbl_Recipient AS f
			WHERE		f.Subscriber_ID = s.ID
			ORDER BY	f.Date_Sent DESC
		) AS r
WHERE		r.numview = 3

E 12°55'05.63"
N 56°04'39.26"

Thanks i think it's working
although r.numview = 3 is not returning any rows
i changed it to >=3 and its returning now, i just hope that its returning the correct data :)

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 08:39:49

Yes. It should read >= 3, because SUM(SIGN(.. thingy calculates all views for any Subscriber_ID, not just the top 3.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 08:42:31

quote:
Originally posted by Peso

Yes. It should read >= 3, because SUM(SIGN(.. thingy calculates all views for any Subscriber_ID, not just the top 3.

E 12°55'05.63"
N 56°04'39.26"

But will this work in case if any Subscriber_ID has more than 3 views with atleast 3 views >0 but not the last three. in such cases, shouldnt they be excluded?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 08:45:28

[code]
SELECT *
FROM
(
SELECT *,SUM(SIGN(views)) OVER (PARTITION BY s.ID) AS Tot
FROM
(
SELECT ID,
FirstName,
LastName,
NewsLetter_ID,
Date_Sent
FROM (
SELECT s.ID,
s.FirstName,
s.LastName,
ROW_NUMBER() OVER (PARTITION BY s.ID ORDER BY r.Date_Sent DESC) AS recID,
r.ID,
r.NewsLetter_ID,
r.Date_Sent,
r.views
FROM tbl_Subscriber AS s
INNER JOIN tbl_Recipient AS r ON r.Subscriber_ID = s.ID
) AS d
WHERE recID < 4
)r
)q
WHERE Tot=3
[/code]

dockies
Starting Member

25 Posts

Posted - 2009-01-08 : 12:34:49

quote:

SELECT s.ID,
s.FirstName,
s.LastName
FROM tbl_Subscriber AS s
CROSS APPLY (
SELECT TOP 3 f.NewsLetter_ID,
f.Date_Sent,
SUM(SIGN(f.views)) OVER (PARTITION BY f.Subscriber_ID) AS numview
FROM tbl_Recipient AS f
WHERE f.Subscriber_ID = s.ID
ORDER BY f.Date_Sent DESC
) AS r
WHERE r.numview = 3

quote:
Originally posted by visakh16

quote:
Originally posted by Peso

Yes. It should read >= 3, because SUM(SIGN(.. thingy calculates all views for any Subscriber_ID, not just the top 3.

E 12°55'05.63"
N 56°04'39.26"

But will this work in case if any Subscriber_ID has more than 3 views with atleast 3 views >0 but not the last three. in such cases, shouldnt they be excluded?

hmmm yeah i think your right. i never tried running it with a subscriber with views > 3 for just one newsletter_id. and based on the sql script it doesnt have restriction or group by newsletter_id or atleast thats what i thought :p im not really that good when it comes with t/sql or p/sql.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources