Getting Top 3 distinct Records

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

Getting Top 3 distinct Records

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2009-10-12 : 03:02:16

Hi Friends

I had a table like this

PostId BlogId UserId
1 1 1
2 2 2
3 1 1
4 4 8
5 6 3
6 4 8

What i need is ,I need the top 3 last added distinct Blogid order by the PostId desc

Out Put :
PostId BlogId userId
6 4 8
5 6 3
3 1 1

I need the Out put Like above result.

Please help me or guide me to do this.

Thanks in Advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-12 : 03:47:33

[code]DECLARE @Sample TABLE
(
PostID INT,
BlogID INT,
UserID INT
)

INSERT @Sample
SELECT 1, 1, 1 UNION ALL
SELECT 2, 2, 2 UNION ALL
SELECT 3, 1, 1 UNION ALL
SELECT 4, 4, 8 UNION ALL
SELECT 5, 6, 3 UNION ALL
SELECT 6, 4, 8

SELECT PostID,
BlogID,
UserID
FROM (
SELECT PostID,
BlogID,
UserID,
ROW_NUMBER() OVER (PARTITION BY BlogID ORDER BY PostID DESC) AS recID
FROM @Sample
) AS d
WHERE recID = 1
ORDER BY PostID DESC[/code]

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

jason97m
Starting Member

4 Posts

Posted - 2009-10-12 : 03:49:40

Hello, In order to accomplish what you desire, I would recommend the following SQL query:

SELECT postid, blogid FROM yourtablename WHERE EXISTS (SELECT DISTINCT PostId FROM yourtablename) ORDER BY PostId DESC LIMIT 3

Hope this recursive relationship query helps out.

Jason Morris
BRIXTON.US

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-12 : 03:52:20

quote:
Originally posted by jason97m

SELECT postid, blogid FROM yourtablename WHERE EXISTS (SELECT DISTINCT PostId FROM yourtablename) ORDER BY PostId DESC LIMIT 3

And that is a Microsoft SQL Server query?

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

Kotti
Posting Yak Master

129 Posts

Posted - 2009-10-12 : 05:24:44

Hello Peso and Jason.

Thanks for your reply.

Peso,
I am getting error when i execute your query as
'ROW_NUMBER' is not a recognized function name.

Jason.
I am getting error on LIMIT when i execute your query.

Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-10-12 : 07:19:57

see this link
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspx

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-10-12 : 07:59:15

Are you using Sql 2000? On the same lines as Peso's reply:

SELECT PostID,
BlogID,
UserID
FROM @SAMPLE S
WHERE S.PostID NOT IN
(
SELECT S1.PostID
FROM @Sample S1 INNER JOIN @Sample S2
ON S1.BlogID = S2.BlogID
WHERE S1.PostID < S2.PostID
)
ORDER BY PostID DESC

--------------------
Rock n Roll with SQL

Kotti
Posting Yak Master

129 Posts

Posted - 2009-10-12 : 10:44:18

quote:
Originally posted by rocknpop

Are you using Sql 2000? On the same lines as Peso's reply:

SELECT PostID,
BlogID,
UserID
FROM @SAMPLE S
WHERE S.PostID NOT IN
(
SELECT S1.PostID
FROM @Sample S1 INNER JOIN @Sample S2
ON S1.BlogID = S2.BlogID
WHERE S1.PostID < S2.PostID
)
ORDER BY PostID DESC

--------------------
Rock n Roll with SQL

Hi

Yes i am using sql server 2000 and Thanks for your help.I am getting answer.

Thanks for all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources