How we could fine the last row in a record

Question

How we could fine the last row in a record

sheetalsh

SSC-Addicted

Points: 459
More actions
June 23, 2008 at 1:32 am

#208733

Hi,
How to write query in which I need to identify the last the row from the records?
Thanks,
SS

Viewing 14 posts - 1 through 13 (of 13 total)

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

sheetalsh (6/23/2008)
Hi,
How to write query in which I need to identify the last the row from the records?
Thanks,
SS

Rows are not ordered. You can introduce ordering by adding a column that keeps track of the order. You could base it on a timestamp, date (e.g. datetime column with get_date() default), sequence number (see identity columns). Once you have set this up you can get the last entry by using e.g. ORDER BY and TOP.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

SqlDeveloper2246 SSC Eights! Points: 886 More actions · Answer 2

I think you can use ranking functions and assign a row number for each record, and then you can get the last row.

SELECTTOP(1) QuestionId,

ROW_NUMBER() OVER (ORDER BY QuestionId DESC) as RowNumber

FROMtblQuestions

or for a more simple situation you can sort the values DESC, and get the TOP (1), so that you get the last record.

SELECTTOP(1) QuestionId

FROMtblQuestions

ORDER BY QuestionId DESC

sheetalsh SSC-Addicted Points: 459 More actions · Answer 3

Hi,

Many thanks for your reply.

Thats great.

Even we can do it as follows:

delete from BTBLPRODUCT_MST where COMP_ID IS NULL AND PRODUCT_ID = (SELECT MAX(PRODUCT_ID) FROM BTBLPRODUCT_MST)

Am I right??

SqlDeveloper2246 SSC Eights! Points: 886 More actions · Answer 4

yes, and i think for the delete operation your solution is better.

sheetalsh SSC-Addicted Points: 459 More actions · Answer 5

sheetalsh

SSC-Addicted

Points: 459

June 23, 2008 at 2:11 am

#832502

Hi,

Thanks buddy.

amirtharajvijay SSC Veteran Points: 208 More actions · Answer 6

Hi,

use this Query...

with numbered as(SELECT rowno=row_number()

over (partition by rand() order by rand())from )

Delete from numbered where rowno= (select max(rowno) from numbered)

amirtharajvijay SSC Veteran Points: 208 More actions · Answer 7

with numbered as(SELECT rowno=row_number()

over (partition by rand() order by rand())from )

select * from numbered where rowno= (select max(rowno) from numbered)

amirtharajvijay SSC Veteran Points: 208 More actions · Answer 8

sorry syntax is missed.

with numbered as(SELECT rowno=row_number()

over (partition by rand() order by rand())from Tablename)

Delete from numbered where rowno= (select max(rowno) from numbered)

sheetalsh SSC-Addicted Points: 459 More actions · Answer 9

sheetalsh

SSC-Addicted

Points: 459

June 23, 2008 at 7:02 am

#832627

Hi,

Thanks a lot:)

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 10

The major point has been missed... Andras explained it. You need a date column or an IDENTITY column to figure out what the last row actually is. Adding an artificial Row_Number() column is a crap shoot at best. There's no guarantee that the last row is the last row you're thinking of...

--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)

Anam Verma SSCertifiable Points: 5365 More actions · Answer 11

Anam Verma

SSCertifiable

Points: 5365

June 24, 2008 at 5:39 pm

#833648

Even i am of same opinion as of Jeff's

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 12

Jeff Moden (6/24/2008)
The major point has been missed... Andras explained it. You need a date column or an IDENTITY column to figure out what the last row actually is.

And I would be unwilling to trust an IDENTITY column. (I have been bit by it.) The DB engines are willing, when it meets their needs, to populate your table in an order that does not match the order of your input. It doesn't happen that often, but when it does, the explanation is simply that a table is an unordered set of rows. To be safe, I think you really need some data in your input that can be used to distinguish your definition of "last" row.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Jim Russell (6/25/2008)
Jeff Moden (6/24/2008)
The major point has been missed... Andras explained it. You need a date column or an IDENTITY column to figure out what the last row actually is.
And I would be unwilling to trust an IDENTITY column. (I have been bit by it.) The DB engines are willing, when it meets their needs, to populate your table in an order that does not match the order of your input. It doesn't happen that often, but when it does, the explanation is simply that a table is an unordered set of rows. To be safe, I think you really need some data in your input that can be used to distinguish your definition of "last" row.

You're right during bulk loading ops. BCP and SELECT INTO can under certain rules run inserts on a data file as a PARALLEL data load, in which case you can't guarantee your order unless you already have an inbuilt IDENTITY and you use the KEEPIDENTITY flag.

If you have the luxury - you could use an ORDER BY in the insert command (which WILL guarantee that the identity column values will be assigned in that order), but that can be a big IF, and not something available in every scenario.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?