Last 5 rows

Question

Last 5 rows

Viewing 13 posts - 46 through 57 (of 57 total)

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

dave hants Default port Points: 1485 More actions · Answer 1

i have two tables....1st one has columns name RlseId and a datetime column..
2nd table has TrackID(primary key,automatic generated) , RlseId(foreign key of 1st table) and RlsTrackId...but no date time column...
whenever the RlsId is inserted into the 2nd table...TrackId is generated automatically...i want to get the TrackId for recent updates but the problem is RlsId got duplicates...
able to get RlsID on particular date & time from 1st table..but for the same Id i am not able to get TrackID from the 2nd table as RlsId got duplicates and no datetime column...

Is it acceptable to have duplicate RlseId values in table1 ?

Charmer SSChampion Points: 14903 More actions · Answer 2

yes...Duplicate is allowed for RlsID in both the tables...the table structure they created was very worst...

the table 2 does not have any datatime column...so i am not able to get the exact TrackID for the time what i expect from Table 2 due to duplicate rows in RlsId...

Thanks,
Charmer

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

anthony.green (11/17/2011)
please post the DDL and sample data of the objects in the problem

Charmer...pls post as asked above

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Steve Malley Ten Centuries Points: 1204 More actions · Answer 4

I thought this was it:

OPENROWSET

( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'

| 'provider_string' }

, { [ catalog. ] [ schema. ] object

| 'query'

}

| BULK 'data_file' ,

{ FORMATFILE = 'format_file_path' [ <bulk_options> ]

| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }

} )

<bulk_options> ::=

[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]

[ , ERRORFILE = 'file_name' ]

[ , FIRSTROW = first_row ]

[ , LASTROW = last_row ]

[ , MAXERRORS = maximum_errors ]

[ , ROWS_PER_BATCH = rows_per_batch ]

Jared SSC Guru Points: 61793 More actions · Answer 5

Steve Malley (11/18/2011)
I thought this was it:
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
<bulk_options> ::=
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]

That is not DDL. DDL is data definition language and is the structure of YOUR table or other object. Should look something like this:

CREATE TABLE tableName (column1 INT ID, column2 VARCHAR(25))

Jared

Jared
CE - Microsoft

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 6

p-nut (11/18/2011)
Steve Malley (11/18/2011)
I thought this was it:
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ <bulk_options> ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
<bulk_options> ::=
[ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ , ERRORFILE = 'file_name' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , MAXERRORS = maximum_errors ]
[ , ROWS_PER_BATCH = rows_per_batch ]
That is not DDL. DDL is data definition language and is the structure of YOUR table or other object. Should look something like this:
CREATE TABLE tableName (column1 INT ID, column2 VARCHAR(25))
Jared

It looks like copy paste error. He pasted it in wrong window (thread). 😀

Clarence R. Assey SSC Rookie Points: 30 More actions · Answer 7

If table has primary/unique key and SELECT has not ORDER BY clause then

the result will be sorted by key. Otherwise no guarantee of sort order.

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 8

Don't miss the sequel of this thread... 😀

Last 5 rows - Part II aka Insertion order - GETDATE(), IDENTITY_INSERT, and others...

http://www.sqlservercentral.com/Forums/Topic1207526-391-1.aspx

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

Clarence R. Assey (11/19/2011)
If table has primary/unique key and SELECT has not ORDER BY clause then
the result will be sorted by key. Otherwise no guarantee of sort order.

Although the optimizer certainly has gotten better at selecting data ordered by key, there's still no guarantee that a SELECT will return data in order by the PK without an ORDER BY.

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

Charmer SSChampion Points: 14903 More actions · Answer 10

Hi friends,

How to add a file into this?

my DDLs are stored in a notepad...

How can i import into this..?

Thanks,
Charmer

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 11

Charmer (11/21/2011)
Hi friends,
How to add a file into this?
my DDLs are stored in a notepad...
How can i import into this..?

What? Where? How? Didn't get anything...

If it's not related with 'Last 5 Rows' question, please start a new thread with more information.

Charmer SSChampion Points: 14903 More actions · Answer 12

the DDLs are related to the question that i started..

so i would like to know how to import files?

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 13

Dev (11/21/2011)
Charmer (11/21/2011)
Hi friends,
How to add a file into this?
my DDLs are stored in a notepad...
How can i import into this..?
What? Where? How? Didn't get anything...
If it's not related with 'Last 5 Rows' question, please start a new thread with more information.

It is related to the topic which were discussed regarding finding records not having Date time column and having some duplicate rows...

Thanks,
Charmer