Select unique column???
-
I have a table : ID ___ IDUser ___ Year 1 ___ 1 ___ 2005 2___ 1 ___ 2007 3 ___ 1___ 2003 4___ 2___ 2008 5 ___ 2___ 2005 I want to filt : unique IDUser with Max year : ID ___ IDUser ___ Year 2___ 1 ___ 2007 4 ___ 2 ___ 2008 Please help me. Thanks.
-
I have a table : ID ___ IDUser ___ Year 1 ___ 1 ___ 2005 2___ 1 ___ 2007 3 ___ 1___ 2003 4___ 2___ 2008 5 ___ 2___ 2005 I want to filt : unique IDUser with Max year : ID ___ IDUser ___ Year 2___ 1 ___ 2007 4 ___ 2 ___ 2008 Please help me. Thanks.
Something like this:
SELECT o.ID, s.IDUser, s.Year
FROM MyTable AS o
INNER JOIN (SELECT IDUser, MAX(Year)
FROM MyTable
GROUP BY Year) AS s
ON o.IDUser = s.IDUser AND o.Year = s.Year
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... * Reading: Developer Day 5 Ready to Give up - Your help will be much appreciated. My website
-
Something like this:
SELECT o.ID, s.IDUser, s.Year
FROM MyTable AS o
INNER JOIN (SELECT IDUser, MAX(Year)
FROM MyTable
GROUP BY Year) AS s
ON o.IDUser = s.IDUser AND o.Year = s.Year
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... * Reading: Developer Day 5 Ready to Give up - Your help will be much appreciated. My website
Error s: SELECT IDUser, MAX(Year) FROM MyTable GROUP BY Year Exception : "Column IDUser is invalid in the select list because it is not contained in erther an aggregate function or the Group by clause"???
-
Something like this:
SELECT o.ID, s.IDUser, s.Year
FROM MyTable AS o
INNER JOIN (SELECT IDUser, MAX(Year)
FROM MyTable
GROUP BY Year) AS s
ON o.IDUser = s.IDUser AND o.Year = s.Year
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... * Reading: Developer Day 5 Ready to Give up - Your help will be much appreciated. My website
SELECT o.ID, s.IDUser, s.Year FROM MyTable AS o INNER JOIN (SELECT IDUser, MAX(Year) FROM MyTable GROUP BY IDUser) AS s ON o.IDUser = s.IDUser AND o.Year = s.Year It is OK. Thanks you very much !
-
SELECT o.ID, s.IDUser, s.Year FROM MyTable AS o INNER JOIN (SELECT IDUser, MAX(Year) FROM MyTable GROUP BY IDUser) AS s ON o.IDUser = s.IDUser AND o.Year = s.Year It is OK. Thanks you very much !
Ah... Sorry - I mistyped the query. Glad you figured it out.
Upcoming events: * Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ... * Reading: Developer Day 5 Ready to Give up - Your help will be much appreciated. My website