row_number not sequential - 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)

row_number not sequential

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2009-06-04 : 08:29:51

Hi All,

What fool thing am I missing here?

SELECT DISTINCT * FROM (
	SELECT Row_Number() OVER (ORDER BY ID) AS RowID, count(*) over() AS TotalCount, ID, Date, Month, Product, A.Rank FROM Subscriptions, CONTAINSTABLE(Subscriptions, *, 'SUPERBLU') AS A WHERE A.[KEY] = ID) X WHERE RowID >= 0 AND RowID <= 50 AND (Month = 'June' OR 'June' = 'June') AND (Reseller = '1' OR '1' = '1') ORDER BY Rank DESC, Date

It gives me odd row id's e.g. 14, 16, 19, 34, 49 missing the others. Surely it is supposed to give me sequential?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 08:37:42

No!

You are filtering on Month OUTSIDE the row_number calculation...

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 08:38:23

Filter the Month thingies INSIDE the row_number() calculation, like this


SELECT DISTINCT	*
FROM		(
			SELECT	Row_Number() OVER (ORDER BY ID) AS RowID,
				COUNT(*) over() AS TotalCount,
				ID,
				Date,
				Month,
				Product,
				A.Rank
			FROM	Subscriptions,
				CONTAINSTABLE(Subscriptions, *, 'SUPERBLU') AS A
			WHERE	A.[KEY] = ID
				AND (Month = 'June' OR 'June' = 'June')
				AND (Reseller = '1' OR '1' = '1')
		) X
WHERE		RowID >= 0
		AND RowID <= 50
ORDER BY	Rank DESC, Date

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 08:55:14

quote:
WHERE RowID >= 0 AND RowID <= 50 AND (Month = 'June' OR 'June' = 'June') AND (Reseller = '1' OR '1' = '1')

what's the condition in red and blue for ? ?

KH
[spoiler]Time is always against us[/spoiler]

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2009-06-04 : 08:57:38

Hey Khtan,

Sorry that's for all or specific i.e. (Month = @Month OR @Month = 1)

Thanks for the help guys!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 09:02:53

You don't have to use dynamic SQL for this!
Try this

SELECT DISTINCT	x.RowID,
		x.TotalCount,
		x.ID,
		x.[Date],
		x.[Month],
		x.Product,
		x.[Rank]
FROM		(
			SELECT		ROW_NUMBER() OVER (ORDER BY s.ID) AS RowID,
					COUNT(*) OVER () AS TotalCount,
					s.ID,
					s.[Date],
					s.[Month],
					s.Product,
					a.[Rank]
			FROM		Subscriptions AS s
			INNER JOIN	CONTAINSTABLE(s, *, 'SUPERBLU') AS a ON a.[Key] = s.ID
			WHERE		(s.[Month] = @Month OR @Month IS NULL)
					AND (s.Reseller = @Reseller OR @Reseller IS NULL)
		) AS x
WHERE		x.RowID >= 0
		AND x.RowID <= 50
ORDER BY	x.[Rank] DESC,
		x.[Date]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 09:06:28

am i missing something here ? what dynamic SQL ?

KH
[spoiler]Time is always against us[/spoiler]

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2009-06-04 : 09:10:03

Well worked out Peso! I was using dynamic SQL.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 09:12:49

Wow ! He can really read mind

KH
[spoiler]Time is always against us[/spoiler]

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2009-06-04 : 09:32:27

Lol khtan, it was some good deductive thinking.

OOI, how do you pass an empty predicate to it to get all the results? Or do you have to go dynamic and leave out the predicate?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 09:43:21

Peso already provided you the query to do that

KH
[spoiler]Time is always against us[/spoiler]

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2009-06-04 : 09:50:39

Sorry khtan,

Think you misunderstood me. I mean how do you pass an empty search term to it, to get all results? Is there a generic wildcard?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 09:56:28

pass in as NULL

select @Month = NULL

KH
[spoiler]Time is always against us[/spoiler]

obiron
Starting Member

23 Posts

Posted - 2009-06-04 : 11:55:53

or you could change

WHERE		(s.[Month] = @Month OR @Month IS NULL)

WHERE		(s.[Month] = coalesce(@Month,s.[Month])

Then it you don't have to explicitly set @month to NULL. If it is not passed as a parameter to the stored procedure it will be assumed NULL.

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2009-06-04 : 13:24:34

Thanks Guys,

The question is actually aimed at the search predicate. How do I pass null to the term i.e. where 'SUPERBLU' is in the example or is there a wildcard?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-04 : 13:36:55

CONSTAINABLE doesn't support variable parameters?

INNER JOIN	CONTAINSTABLE(s, *, @SearchParameter)

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-05 : 02:31:51

quote:
Originally posted by khtan

Wow ! He can really read mind

KH
[spoiler]Time is always against us[/spoiler]

I think he guessed it from this part

'June' = 'June'
'1'='1'

which can happen sometimes when you print dynamically constructed sql

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources