Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    2

    Question Unanswered: Newb question regarding join using an alias

    A Pier has basically told me to get acquainted with similar query's and has given me the basics on that the following is a join that uses an alias generated by a sub-query.

    delete ab
    from address ab
    join
    (select max(address_id) as address_id, min(date_entered) as date_entered
    from address
    group by individual_id
    having count(individual_id)> 1) dupes
    on ab.address_id = dupes.address_id

    I have a common understanding of the join, but could use any help understanding what the delete statement is doing, and any possible reasons why I need to apply the min() function to the date_entered column

    Thank you in advance for all that can help me become better acquainted to query's like this.
    Jpetey

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    This Delete is deleting the table row with the biggest Address_id, for individual_id's that have more than one row in the table (HAVING).
    I believe that the result of the min function is not being used and that this function can be removed.

    Hope this helps.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The min(date_entered) could be removed pretty safely. You could replace the "delete ab" with "select *", and see what it gives you.

  4. #4
    Join Date
    May 2012
    Posts
    2
    Thanks so much all. That helped me understand the delete (basically getting rid of repetitive data). However I was told there were a few reasons why I might need to have the min() function for the (date_entered), and was told to try to understand why. I appreciate any thoughts on few reasons why. only one I can think of is to return the oldest date or dates entered. Any thoughts our welcome and greatly appreciated.

    hope I am not being to persistent with this min() question

    Thanks, jpetey

Posting Permissions

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