none
SQL syntax best practice

    Question

  • If I can find the Most recent address using:

    SELECT DISTINCT A.ClientKey, A.AdmissionKey, A.Address, A.CITY, A.State, A.ZIP, A.PhoneH, A.PhoneW, A.PhoneC
    FROM         (SELECT     ClientKey, AdmissionKey, CONVERT(varchar, ISNULL(Date_Change, 1), 120) + CAST(DOCid AS varchar) AS DateKey, Address, CITY, State, ZIP,
                                                  PhoneH, PhoneW, PhoneC
                           FROM          ClientAddressTable) AS A INNER JOIN
                              (SELECT     ClientKey, AdmissionKey, MAX(CONVERT(varchar, ISNULL(Date_Change, 1), 120) + CAST(DOCid AS varchar)) AS MaxDateKey
                                FROM          ClientAddressTable AS ClientAddressTable_1
                                GROUP BY ClientKey, AdmissionKey) AS B ON A.AdmissionKey = B.AdmissionKey AND A.DateKey = B.MaxDateKey

    What is the best way to find all Addresses EXCEPT the most recent?


    • Edited by TryingHarder Wednesday, July 17, 2013 10:09 PM clarity
    Wednesday, July 17, 2013 10:07 PM

Answers

  • Select ClientKey, AdmissionKey, Address, CITY, State, ZIP, PhoneH, PhoneW, PhoneC 
    FROM (
    SELECT   A.ClientKey, A.AdmissionKey, A.Address, A.CITY, A.State, A.ZIP, A.PhoneH, A.PhoneW, A.PhoneC
    , ROW_NUMBER() OVER(Partition By  A.ClientKey, A.AdmissionKey, A.Address, A.CITY, A.State, A.ZIP, A.PhoneH, A.PhoneW, A.PhoneC
     Order BY Date_Change  DESC  ) rn 
     , count(*) Over(Partition By  A.ClientKey, A.AdmissionKey, A.Address, A.CITY, A.State, A.ZIP, A.PhoneH, A.PhoneW, A.PhoneC) cnt
    FROM  ClientAddressTable A ) t
    WHERE rn>1 Or cnt=1

    Thursday, July 18, 2013 2:57 PM

All replies

  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (not even close! You even tibbled). 

    Temporal data should use ISO-8601 formats (you failed! I think dates are strings!). Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. What little you did post is wrong. One of the many error is using “-key” as an attribute property. This is how the attribute is used in one place, not what it is by its nature. 

    >> What is the best way to find all Addresses EXCEPT the most recent? <<

    Gee, how do we answer this without any data or DDL? Would you like to read the basic netiquette section at the front of the forum, try again and be polite? We can then throw out this mess you have and get you a valid schema. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, July 18, 2013 12:26 AM
  • You're correct.  The question was poorly conceived.  Thanks for your time.
    Thursday, July 18, 2013 1:36 PM
  • Select ClientKey, AdmissionKey, Address, CITY, State, ZIP, PhoneH, PhoneW, PhoneC 
    FROM (
    SELECT   A.ClientKey, A.AdmissionKey, A.Address, A.CITY, A.State, A.ZIP, A.PhoneH, A.PhoneW, A.PhoneC
    , ROW_NUMBER() OVER(Partition By  A.ClientKey, A.AdmissionKey, A.Address, A.CITY, A.State, A.ZIP, A.PhoneH, A.PhoneW, A.PhoneC
     Order BY Date_Change  DESC  ) rn 
     , count(*) Over(Partition By  A.ClientKey, A.AdmissionKey, A.Address, A.CITY, A.State, A.ZIP, A.PhoneH, A.PhoneW, A.PhoneC) cnt
    FROM  ClientAddressTable A ) t
    WHERE rn>1 Or cnt=1

    Thursday, July 18, 2013 2:57 PM