Returning previous 4, plus all subsequent 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

SQL Server 2000 Forums

SQL Server Development (2000)

Returning previous 4, plus all subsequent records

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-03-21 : 19:24:21

Ok, I've got a weird one. I have a stored procedure that needs to return the 4 most recent items, plus all future items, based on a table with a smalldatetime field. The table also has an identity column, but the identity and datetime fields are not guaranteed to be in the same order.

The best solution I've come up with so far is stepping back 4 times, then returning all results from there:


DECLARE @dTemp smalldatetime
DECLARE @iLoop tinyint

set @iLoop=4
set @dTemp=getdate()
while @iLoop>0
  BEGIN
    select @dTemp=max(datetime) from table where datetime<@dTemp
    set @iLoop=@iLoop-1
  END

select * from table where datetime>=@dTemp

Is there a better way?

Thanks
-b

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-03-21 : 19:29:48

What about a UNION ?

Select the top 4 most recent ones, then do a union and select all future ones.

That's what I would do

Damian

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-03-21 : 19:40:13

Aiken,

Use a union...

eg.


Select TOP 4 * from Table 
where datetime<[Your Time] 
order by datetime desc
union all
Select * from Table 
where datetime >=[Your Time]

Just add ordering etc...

HTH
HTH

DavidM

Tomorrow is the same day as Today was the day before.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-21 : 20:05:11

pan-Australian consensus

- now how can you argue with that?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-03-21 : 20:08:25

Yep, we love our unions here.

oh...i'm sorry, that was terrible...... i'm full of bad jokes today..... trying to stop...

Damian

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-21 : 20:32:22

perhaps I should have said pun-Australian consensus ?

say cheese

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-21 : 21:15:37

The Wizards of Oz have spoken!

groooooooooooooooooooaaaaaaaaaaaaaaaannnnnnnnnnnnnnnnnnnnnnn....

sorry, had to do it

Edited by - robvolk on 03/21/2002 21:16:21

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-21 : 21:17:19

oh no you didn't

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources