Post reply

Need Help Soon..... Last Record in a Huge Table???

  • May 2, 2003 at 10:31 am

    #101460

    I have a Table with 27 Mil records in it, (2 Gig Data, 1.5 Gig Indexing), Totaling to 3.5 Gig size just for this table.

    I needed to retrieve the last record to the business users. Could any one of you let me know how do I get this???

    Thanks for your help....

    .

  • May 2, 2003 at 10:39 am

    #456032

    In wich columns do tou have the indexes?

    You could select top 1 from thatcolumnorder by desc.

    Anyway you do it, you should use the indexes.

  • May 2, 2003 at 10:47 am

    #456034

    Do you mean something like this???

    SELECTTOP 10 *

    FROMdbo.table1

    order by field1code desc

    I just tried this but I am not sure if this is the correct one. field1code is being indexed (cluster). Could you pl. tell me if this would bring the correct last 10 records?!

    Or is there any other way???

    .

  • May 2, 2003 at 10:52 am

    #456036

    Remember that using TOP and ORDER BY cause the entire query to run in the background even after the results have been returned.

    Is it possible to create an index using the column that will denote the last entry? You could select TOP 1 with an index hint that way.

    OR

    SELECT * from table where key = (select max(key) from table)

    Just $.02

    Guarddata-

  • May 2, 2003 at 11:01 am

    #456040

    Can you tell us what the fields are? Do you have a datetime field?

    If you have a datetime field that holds the datetime of the entry, you could do:

    select *

    from mytable

    where mydatetime = (select max(mydatetime) from mytable)

    -SQLBill

  • May 2, 2003 at 2:24 pm

    #456070

    We do not have date fields in the table.

    .

  • May 2, 2003 at 3:23 pm

    #456078

    Do you have an identity field or can you alter the structure to add one? Then you could always query the max(iden_field) when you needed the last record that was entered.

    Michelle



    Michelle

  • May 2, 2003 at 6:31 pm

    #456086

    Thanks for all the replies... especially racosta & guarddata.

    We do not have any primary key, Identity, Date_Entered fields on this table.

    I pulled the required records with this query....

    SELECT *

    from table1

    where field1code = (select max(field1code)

    from table1)

    I have a Clustered Index on field field1code. As racosta and guarddata pointed, I have used the index field and got this...

    guarddata : Thanks for letting me know an important point. I didn't know that the query will be running on the background even after displaying the results. Nice insight.

    Thanks to all again....

    .

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

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