Easy code for selecting Top N - wont work, WHY???? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Easy code for selecting Top N - wont work, WHY????

Author

Topic

ddikeht1
Starting Member

1 Post

Posted - 2011-01-29 : 23:05:11

This should be easy, but for some reason it will not work. Trying to pull in the top 3 rows by data for each subscriber. Here is what I have.

SELECT t1.Crt_dt
FROM c_attribute t1
WHERE t1.Crt_dt IN
( SELECT TOP 3 t2.Crt_dt
FROM c_attribute t2
WHERE t2.Crt_dt = t1.Crt_dt
AND t2.c_id= t1.c_id
ORDER BY t1.Crt_dt DESC
)
AND C_Id= '62413036'
ORDER BY t1.Crt_dt DESC

For some reason this pulls back all rows (about 5) The C_Id is just used for testing, but this will be eventually linked back to a table with numerous c_id's, and I am just trying to bull the most recent 3 by date. Banging my head on this one. Someone assist if possible?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-30 : 01:27:44

Check the data in the underlaying tables individually. :) at first look it seems that there are duplicate rows in the c_attribute table for the ones coming through Select top 3 statment

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-07 : 09:25:00

are you using sqlserver 2005 or later?

try this:


SELECT
	dets.[Crt_dt]
	, dets.[c_id]
FROM
	(
	SELECT
		t1.[Crt_dt]
		, t1.[c_id]
		, ROW_NUMBER () OVER (
			PARTITION BY t1.[c_id]
			ORDER BY t1.[Crt_dt] DESC
			)
			AS [RowRank]
	FROM
		c_attribute AS t1
	)
	AS dets
WHERE
	dets.[RowRank] <= 3
	-- AND dets.[c_id] = '62413036'
ORDER BY
	dets.[c_Id]
	, dets.[RowRank] DESC

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources