MAX / GROUP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

MAX / GROUP

Author

Topic

mole999
Starting Member

49 Posts

Posted - 2014-12-06 : 05:05:16

I've tried this 'easy' sql and can't get my brain to identify the correct way.
I need ENDDATE to be the MAX value associated with PERSUNQ, i think its another SELECT wrapping it up, i can't get how to do this that makes sense YET. ANy help much appreciated


Select distinct
  Names.PERSUNQ,
  Names.ID,
  Names.FIRST_NAME,
  Names.SURNAME,
  Names.[Best Name],
  Names.MARKERS,   
  Max(Names.ENDDATE) As Max_ENDDATE
From
  Names
Group By
  Names.PERSUNQ, Names.ID, Names.FIRST_NAME, Names.SURNAME, Names.[Best Name],
  Names.MARKERS
Order By
  1

Mole

mandm
Posting Yak Master

120 Posts

Posted - 2014-12-06 : 06:31:03

Yes you are correct. You need a second select to get the max date for each PERSUNQ then bring that back into your query. Try the following.

Select distinct
Names.PERSUNQ,
Names.ID,
Names.FIRST_NAME,
Names.SURNAME,
Names.[Best Name],
Names.MARKERS,
mxdt.Max_ENDDATE
From Names
JOIN (SELECT PERSUNQ
, Max(ENDDATE) As Max_ENDDATE
FROM Names
Group By PERSUNQ) AS mxdt
ON Names.PERSUNQ = mxdt.PERSUNQ
AND Names.ENDDATE = mxdt.Max_ENDDATE
Order By
1

mole999
Starting Member

49 Posts

Posted - 2014-12-06 : 08:27:24

Thanks for providing a solution, i carried on looking and found another way which i will offer

Yours provides 7936 in 187ms


Select
  Result.PERSUNQ,
  Result.ID,
  Result.FIRST_NAME,
  Result.SURNAME,
  Result.[Best Name],
  Result.MARKERS,
  Result.ENDDATE
From
  (Select
    Names.PERSUNQ,
    Names.ID,
    Names.FIRST_NAME,
    Names.SURNAME,
    Names.[Best Name],
    Names.MARKERS,
    Names.ENDDATE,
    Rank() Over (Partition By Names.PERSUNQ Order By Names.ENDDATE Desc) As
    PERSUNQ_rank
  From
    Names)AS Result
Where
  Result.PERSUNQ_rank = 1

returns 1 more 7937 in 125ms (I have to work out if I'm over-counting or not)

both return visually similar results and whilst quicker and more (from 128953 records)

always learning :)

Mole

mandm
Posting Yak Master

120 Posts

Posted - 2014-12-06 : 08:43:58

Glad you found your answer.

mole999
Starting Member

49 Posts

Posted - 2014-12-06 : 09:00:32

found the reason for the numbers difference

ENDDATE is blank in one record

Mole

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources