Results 1 to 7 of 7
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: Efficient SQL for retreiving latest row

    Hi,

    I am looking for the best way to get the last record from multiple tables.

    For instance 4 tables.

    Customer indexed on customer_id
    Accounts has customer_id and indexed on account_id
    Orders has account_id and indexed on order_id
    Items has order_id and indexed on item_id

    The way the database is set up there are no fields that are common across all the tables. I cannot edit the database structure. What I want to do is get a customer based on the customer id and then the latest account, latest order and latest item from the tables.

    Currently I am doing this by using with statments and getting a max from each table based on the preceding table. Is there a more efficient way of doing this?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    rows in a table are like balls in a basket.
    How do you identify the last ball in the basket?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2012
    Posts
    3
    Well each table has a unique index of it's own so for each of these tables I am finding the max unique index where another field matches.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by BrianMH View Post
    The way the database is set up there are no fields that are common across all the tables.
    Quote Originally Posted by BrianMH View Post
    Well each table has a unique index of it's own so for each of these tables I am finding the max unique index where another field matches.
    These two statements are in contradiction - I see no way how to "match another field" when there are "no fields common across all the tables".
    By the way, tables contain columns, not "fields".

    Maybe it would be useful, if you posted what exactly you have - CREATE TABLE statements for all involved tables including primary/foreign key constraints (if tables are too "wide", you may omit unimportant columns), INSERT statements for getting some sample data, expected result set for that data and rules leading to it.

    By the way, how would you (manually) get that information efficiently from those tables?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Well each table has a unique index of it's own so for each of these tables I am finding the max unique index where another field matches.

    Sounds OK to me.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    May 2012
    Posts
    3
    To clarify there is no 1 column that is common across all tables but they can be linked one to another through each other.

    For instance you have 4 tables.

    Table1, Table2, Table3, Table4.

    Table1 has a primary key and table2 contains this primary key and can have multiple entries, table2 also has a primary key and table3 contains this pk and can have multiple entries. Table4 is the same with Table3.

    Each table contains a reference to the previous table and can have multiple instances. Each table also contains a PK. However there is no direct reference between table1 and table4.

    I am wondering how most efficiently in general one would get the latest entry based on the pk on Table1 in Table4.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post SQL that returns "last" row for any specific PK value
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •