SQL FIRST & LAST functions in SQL 2K

Question

SQL FIRST & LAST functions in SQL 2K

james00harper

SSC Enthusiast

Points: 171
More actions
December 25, 2006 at 5:35 pm

#92060

Hi,
The great FIRST and LAST functions in SQL 2005 are a great assett, but they weren't implemented in 2K. Is there a way to produce similar functionality in 2K by using some coding method?
Any help appreciated.
Thanks!

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I'm sure we can figure something out. Can you tell us exactly what you are working with (DDL and sample data) and what you need (sample output) and we'll see if we can fix you up.

Jeff Moden SSC Guru Points: 1004635 More actions · Answer 2

James,

I did a search in BOL 2005 and on google... couldn't find reference to a FIRST or LAST function. Probably my problem since I don't use 2005, yet, but do you have a reference that describes what these two functions do so we can figure something out in 2k? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

corey lawson Hall of Fame Points: 3773 More actions · Answer 3

You'd want to do something like this:

select top 1 t.*

from mytable t join (select min(some_field) first from mytable) f on t.some_field = f.first

or

select top 1 t.*

from mytable t join (select max(some_field) last from mytable) f on t.some_field = f.last

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

last=

select top 1 * from mytable order by myfield desc

first=

select top 1 * from mytable order by myfield asc

??

MVDBA

james00harper SSC Enthusiast Points: 171 More actions · Answer 5

That works brilliantly. I'm starting a new job on 2nd January and have been trying to tone up my SQL skills before then.

I'm surprised that I had so many responses!

Haven't you all got better things to do on Christmas Day??!

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797

December 26, 2006 at 4:54 am

#679953

i wish - i'm in the office

MVDBA

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I would like to but I'm a little sort this year... and I can cam up with a few Ks this week alone so I'm working this year.