Max function without group by

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Max function without group by

Max function without group by

Rate Topic

Display Mode

Topic Options

Author

Message

asene

Posted Wednesday, January 30, 2013 12:05 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:34 AM
Points: 35, Visits: 63

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

Post #1413736

GilaMonster

Posted Wednesday, January 30, 2013 12:44 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:16 AM
Points: 37,688, Visits: 29,947

Why?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1413745

asene

Posted Wednesday, January 30, 2013 1:07 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:34 AM
Points: 35, Visits: 63

Hi

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

Thanks

Post #1413750

Bhuvnesh

Posted Thursday, January 31, 2013 12:24 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

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

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1413862

ChrisM@Work

Posted Thursday, January 31, 2013 1:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:00 AM
Points: 5,609, Visits: 10,971

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1413874

Bhuvnesh

Posted Thursday, January 31, 2013 1:12 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

ChrisM@Work (1/31/2013)

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

New to me

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1413880

GilaMonster

Posted Thursday, January 31, 2013 1:52 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:16 AM
Points: 37,688, Visits: 29,947

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 2008, MVP
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

Post #1413900

ChrisM@Work

Posted Thursday, January 31, 2013 1:54 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:00 AM
Points: 5,609, Visits: 10,971

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}

Post #1413904

asene

Posted Thursday, January 31, 2013 9:11 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:34 AM
Points: 35, Visits: 63

Hi Guys

Thanks to all of you.

Regards

Post #1414198

« Prev Topic | Next Topic »

Permissions