Count rows as they're being selected

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 2000 Forums

Transact-SQL (2000)

Count rows as they're being selected

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-05-10 : 11:37:35

Hi,

Is it possible to have a case statement that returns a a column's value if the row is within a the first, say, 3 records and a null otherwise?

Cheers,

XF.

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-10 : 11:41:42

The order of data has no meaning in a database.

What is is that your're trying to do?

Brett

8-)

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-05-10 : 14:13:08

I'm not really interested in the order of the data in the database, I'm interested in the order that its returned from a SELECT query.

I need to display some news stories. The top 3 news stories (based on date created) require more detailed information than those that follow. So, I figure that it would be more efficient to return the extra columns of data for just the first three rows returned by the SELECT and then for the other stories just return null.

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-10 : 14:49:10

Perhaps


SELECT * FROM (
	SELECT TOP 3 ArticleDate, ArticleName, ArticleDesc
	  FROM myTable99
      ORDER BY ArticleDate DESC
	      ) AS XXX
UNION ALL (
	SELECT TOP 5 AtricleDate, ArticleName, Null AS ArticleDesc
	  FROM myTable99 
	 WHERE ArticleDate < (SELECT MIN(ArticleDate) 
				FROM (SELECT TOP 3 ArticleDate 
					FROM myTable99 
				    ORDER BY ArticleDate DESC) AS ZZZ
      ORDER BY ArticleDate DESC
	     ) AS YYY

Brett

8-)

andy8979
Starting Member

36 Posts

Posted - 2005-05-10 : 14:52:36

Try something like this I guess this may be of help
I just created a table variable and have inserted some records in it, then in the first query I fetched the top 3 records, in the second part i.e. after the union keyword we get the remaining records.
Declare @Tab table (I int)

insert into @Tab values (1)
insert into @Tab values (2)
insert into @Tab values (3)
insert into @Tab values (4)
insert into @Tab values (5)
insert into @Tab values (6)

Select top 3 i from @tab
Union
Select t1.i from @tab As t1
Left Join (Select top 3 i from @tab) As t2 ON t1.i = t2.i
Where t2.i is Null.

Anuj

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-10 : 15:01:40

I think you need to worry about ORDER BY

Brett

8-)

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-05-10 : 15:15:01

That's very cool Brett. Thanks.

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-05-12 : 19:24:51

Do you think that the database is capable of caching the results of the first query for use in the sub-query?

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2005-05-12 : 19:35:23

I dont think Brett is using first query's result in sub query?
Look like sub query is executed first then outer query is executed.

Is it Brett?

mk_garg

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-05-14 : 11:28:19

OK, well either way, do you think the db notices that the results from the first half of the union can be used in place of running the sub-query?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-14 : 11:34:26

[code]
select
NewsStories.StoryID, NewsStories.Title, Top3.StoryText
from
NewsStories
left outer join
(select top 3 * from NewsStories order by StoryDate DESC) Top3
on
top3.StoryID = NewsStories.StoryID
order by
NewsStories.StoryDate DESC
[/code]

As always keep it as simple as possible, break it down step by step. Andy almost had it, but it can be done in 1 step.

Pull from all stories, left join to the top 3, return the title and other key coluns from the main table, but return the text itself from the "top 3" only. Simple, clean and efficient.

- Jeff

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-05-14 : 18:44:44

Even cooler.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources