Correlated and aggregate sql query

I have a table similar to the following:

ID | Name ID | Period From | Period To | Percentage |

-----------------------

Important - Each person can have more than one entry.

What I am trying to do is get the last percentage that each person obtained.

The only way I have been able to do this is by the following:

SELECT * FROM myTable

LEFT OUTER JOIN ( SELECT NameID, MAX(PeriodTo) as PeriodTo FROM myTable GROUP BY NameID) t1

ON myTable.NameID = t1.NameID

WHERE myTable.PeriodTo = t1.PeriodTo

I was wondering if there was another way of doing this, or whether this is an efficient method of doing this kind of query. Jagdip

SELECT * FROM myTable where PeriodTo in (select Max(PeriodTo) from myTable group by NameID)

|||This does not work. It finds the rows where the periodTo is equivalent to the maximum periodTo. I need to find the maximum periodTofor each nameID. Its is equivalent toSELECT ID, NameID, ..., MAX(PeriodTo)FROM myTableGROUP BY ID, NameID, ...|||

You can use ROW_Number function if you use SQL Server 2005:

SELECT NameID, PeriodFrom, PeriodTo, PercentageFROM(SELECT NameID, PeriodFrom, PeriodTo, Percentage,ROW_Number()OVER(PARTITIONBY NameIDORDERBY PeriodToDESC)as RowNum

FROM mytable)AS t

WHERE RowNum=1

|||

Can you post some sample data from the table and expected output? This makes it easier for us to see what should be achieved.

|||

if PeriodTo unique then it works

(

SELECT * FROM myTable where PeriodTo in (select Max(PeriodTo) from myTable group by NameID)

in the inner query it selects all the periodto which is max to the nameid >>>then it matches the PeriodTo from the outer query.
select Max(PeriodTo) from myTable group by NameIDthis portion returns the Max PeriodTo for each NameID which is unique

)

|||

Sorry Kamrul, but that is not what I am looking for. Maybe I have not explained the problem properly, so here is some sample data (obviously simplified).

An example of the table is (note that this is british dates - i.e. dd/mm/yy):

 ID | NameID | PeriodTo | Comments |---------------------- 1 | Mark | 01/01/01 | ComMark1 | 2 | Mark | 01/06/01 | ComMark2 | 3 | Mark | 01/11/01 | ComMark3 | 4 | Ken | 01/01/01 | ComKen1 | 5 | Ken | 01/06/01 | ComKen2 | 6 | John | 01/01/01 | ComJohn1 |---------------------

And the result I am trying to get is :

 ID | NameID | PeriodTo | Comments |---------------------- 3 | Mark | 01/11/01 | ComMark3 | 5 | Ken | 01/06/01 | ComKen2 | 6 | John | 01/01/01 | ComJohn |----------------------

Kamrul's code doesn't work because it will return every row.

The code I wrote does return this, but I was wondering whether there was a nice way of doing this kind of correlated aggregated query. I'm looking for the most efficient method. I should also point out that I am using sql server 2000.

Jagdip

|||

Hi,

Please try this.

SELECT *FROM MyTable OutMyTable
where PeriodTo = (select Max(PeriodTo)from MyTable InMyTableWHERE InMyTable.NameID=OutMyTable.NameID group by NameID)

Regards,

Gaurang Majithiya

|||

For SQL Server 2000:

SELECT NameID, PeriodFrom, PeriodTo, PercentageFROM(

SELECT NameID, PeriodFrom, PeriodTo, Percentage,(SELECTCOUNT(*)FROM mytable aWHERE a.NameID=b.NameIDAND a.PeriodTo>=b.PeriodTo)as RowNum

FROM mytable b)AS t

WHERE RowNum=1