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
