tricky max query

[wanderingaround]

I can't figure this one out...

My table includes [Client], [Dept], [Date], (plus other)fields.

I want to see just the record (all fields) relating to the most recent [Date] per [Client].

Any tips greatly appreciated! Mike

 

[bborissov]

SELECT TableName.*
FROM TableName
INNER JOIN (SELECT Client, MAX(Date) AS Date
            FROM TableName
            GROUP BY Client) Tbl1
       ON TableName.Client = Tbl1.Client AND
          TableName.Date   = Tbl1.Date

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[r937]

SELECT t.Client
     , t.Dept
     , t.Date
     , t.plusotherfields
  FROM ( SELECT Client
              , MAX(Date) AS latest
           FROM daTable
         GROUP
             BY Client ) AS m
INNER
  JOIN daTable AS t
    ON t.Client = m.Client
   AND t.Date = m.latest

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Rhys666]

;WITH RankedClients_CTE
(
    Client
    ,Department
    ,Date
    // ANY OTHER FIELDS...
    ,Ranking
)
AS
(
    SELECT
        Client
        ,Department
        ,Date
        // ANY OTHER FIELDS...
        ,RANK() OVER
        (
            PARTITION BY
                PK_Meeting
            ORDER BY
                Date DESC
        )
        FROM
            MY_TABLE
)
SELECT
    Client
    ,Department
    ,Date
    // ANY OTHER FIELDS...
FROM
    RankedClients_CTE
WHERE
    Ranking = 1;

As you can see, there is a number of ways to do this...

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[wanderingaround]

Thanks very much for those suggestions...

I've set this up but I've noticed that a client can have more than one entry per date. How could I amend these suggestions so the results shows the latest record but just one record per client. (Where there is more than one record per client which equals the max date I have no preference which record is returned)...

Thanks in advance... Mike

 

[r937]

did you try the windowing solution (the one with RANK() and PARTITION BY)?



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

What make a record "last"?
How do you know which record is "last"?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[r937]

boris, what makes a record "last" is this --

ORDER BY Date DESC

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

Nope, if you have nothing that you surely know that THIS is the "LAST" record
Some DateTime field, IDENTITY field?

That is because SQL Server reuse space leaved from deleted records and the "last" added record can occur before "older" ones in next query

(I didn't explain this to you Rudy, sorry if it sounds that way

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[r937]

boris, when in doubt, check the original post ...

most recent [Date] per [Client]




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[wanderingaround]

Thanks r937/Rhys666!

I didn't try the rank / partition option at first as I didn't understand it... I found this website which explains it perfectly...

http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx

I didn't use a CTE, instead I used a dervied table...

Also I had duplicate dates per client and per client/Dept so I included Dept in the order by and then used Distinct to exclude the 'exact' matches...

<sorry don't know how to put code in a code window>

SELECT DISTINCT ClientNo, Date, Dept

FROM (SELECT ClientNo, Date, Dept, RANK()
OVER (partition BY ClientNo
ORDER BY Date DESC, Dept ASC) AS Rank
FROM MyTable) tmp

WHERE Rank = 1



Works wonderfully! - and so simple!!! Thanks, Mike