Max function without group by

Question

Max function without group by

asene

Old Hand

Points: 310
More actions
January 30, 2013 at 12:05 pm

#268039

Hi guys
I have two tables: tbEtapeProjet and tpEtape
tbEtapeProjet(idetape,idprojet,debut,fin) contains
1 1 2011-07-01 00:00:00 2011-09-01 00:00:00
1 2 2012-05-01 00:00:00 2012-05-10 00:00:00
1 3 2012-11-01 00:00:00 2012-01-20 00:00:00
2 1 2011-09-02 00:00:00 2011-11-30 00:00:00
2 2 2012-05-11 00:00:00 2012-06-01 00:00:00
2 3 2012-01-21 00:00:00 2012-04-01 00:00:00
3 1 2011-12-01 00:00:00 2012-07-07 00:00:00
3 2 2012-06-02 00:00:00 2012-07-01 00:00:00
3 3 2012-04-02 00:00:00 NULL
4 1 2012-07-08 00:00:00 NULL
4 2 2012-07-01 00:00:00 2012-07-21 00:00:00
5 2 2012-07-22 00:00:00 2012-07-23 00:00:00
tbEtape(idEtape,NomEtape) contains
1 démarrage
2 prévision
3 réalisation
4 surveillance
5 cloture
My Query
select MAX(pg.idetape)as Dernier,Idprojet from
tbetapeprojet pg join tbetape h on
pg.idetape=h.idetape group by pg.Idprojet
the result i wnated. My question ius is there any way to produce the same result without using group by
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

asene Old Hand Points: 310 More actions · Answer 2

Hi

Just trying to know if there a way to do it without.

Thanks

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Using ROW_NUMBER() with PARTITION .See http://msdn.microsoft.com/en-us/library/ms186734.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Bhuvnesh (1/31/2013)
Using ROW_NUMBER() with PARTITION .See http://msdn.microsoft.com/en-us/library/ms186734.aspx

MAX() OVER (PARTITION BY...) might be more appropriate.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

ChrisM@Work (1/31/2013)
MAX() OVER (PARTITION BY...) might be more appropriate.

New to me 🙂

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

asene (1/30/2013)
Just trying to know if there a way to do it without.

Sure, there's several ways. The group by's the simplest.

Max over partition, row number over partition with a where clause, max with a cross apply

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Bhuvnesh (1/31/2013)
ChrisM@Work (1/31/2013)
MAX() OVER (PARTITION BY...) might be more appropriate.
New to me 🙂

From BOL [Aggregate Functions (Transact-SQL)]: "The OVER clause may follow all aggregate functions except CHECKSUM."

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

asene Old Hand Points: 310 More actions · Answer 8

asene

Old Hand

Points: 310

January 31, 2013 at 9:11 am

#1583062

Hi Guys

Thanks to all of you.

Regards