Post reply

sql query

  • December 9, 2006 at 8:57 am

    #175567

    I have a table with 170 columns.important fields are 4

    chart number,case number,last visit date,date created.

    my job is :

    To sort the records on:

    chart number ascending order

    last visit date descending order

    date created descending order

    One chart number may have many records.Now i want to take records with maximum of last visit date for each chart number which has multiple records

    Eg:

    chart number last visit date(dd/mm/yyyy)

    1 01/01/2005

    2 01/01/2006

    2 02/01/2006

    2 04/05/2006 (this record should be selected)

    2 03/04/2006

    output should be(with all the 170 columns)

    1 01/01/2005

    2 04/05/2006

    hint: select chartnumber,max(lastvisitdate) from mwcas group by chartnumber

    how to proceed now?help me

  • December 10, 2006 at 8:04 am

    #677137

    You're right on track.  Here's the missing step you need :

    Select * FROM mwcas INNER JOIN

    (Select chartnumber, max(lastvisitdate) AS mLastVisiteDate from mwcas group by chartnumber) dtMax ON mwcas.ChartNumber = dtMax.ChartNumber AND mwcas.LastVisiteDate = dtMax.mLastVisiteDate

Viewing 2 posts - 1 through 1 (of 1 total)

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