Results 1 to 3 of 3

Thread: Help With query

  1. #1
    Join Date
    Oct 2011

    Unanswered: Help With query


    I have a query to get the latest record in a table as follows:

    SELECT DISTINCT max(lcns_startdt) as lcns_startdt, max(appt_stopdt) as appt_stopdt, max(appt_startdt) as appt_startdt, agent_tax_id, co_id, state_id, lob_type
    FROM license
    GROUP BY agent_tax_id, co_id, state_id, lob_type
    HAVING lcns_startdt = max(lcns_startdt) AND
    appt_stopdt = max(appt_stopdt) AND
    appt_startdt = max(appt_startdt)

    Now the user wants to redefine a latest record. They want to first look at the latest appt_startdt field. If there is only one record for agent_tax_id, co_id, state_id, lob_type with the lastest appt_startdt then use that record. If there are two or more records using the latest appt_start_dt then look at the appt_stopdt. If there are two or more records that have the same appt_startdt and appt_stopdt then get the latest lcns_startdt. I am not sure how to code this in SQL. Can someone help me out?



  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    you're not going to like the ANSI SQL solution (the forum you posted in) because it probably won't work in your database system

    what database system are you using? | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2011
    I am using Sybase 11.9.2

Posting Permissions

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