Query General
in [Server Configuration]
|
Prev: SQL2008 SSMS loses custom serttings
Next: ANN: New version of Database Workbench, the multi-DBMS IDE now available!
From: Muhammad Bilal on 22 Apr 2010 05:47 Hi. I have two tables Table1 Name Serial Date John 01-01-2010 Tony 11-02-2010 Table2 Name Serial Date John 05-04-2009 John 06-08-2009 John 01-01-2010 Tony 11-02-2010 Tony 11-02-2010 I want is to assign the serial against each name starting from 1 in table2 according to date in ascending order and the max serial should be assigned against each name in Table1 i.e.. Table1 Name Serial Date John 3 01-01-2010 Tony 2 11-02-2010 Table2 Name Serial Date John 1 5-4-2009 John 2 6-8-2009 John 3 1-1-2010 Tony 1 11-2-2010 Tony 2 11-02-2010 Regards, Muhammad Bilal
From: Uri Dimant on 22 Apr 2010 06:28 SELECTName ,[Serial Date],ROW_NUMBER () OVER (PARTITION BY name ORDER BY [Serial Date] DESC) number FROM tbl "Muhammad Bilal" <MuhammadBilal(a)discussions.microsoft.com> wrote in message news:BF2A852C-1E8F-4008-A647-BA1E20C63184(a)microsoft.com... > Hi. > > I have two tables > Table1 > Name Serial Date > John 01-01-2010 > Tony 11-02-2010 > > Table2 > Name Serial Date > John 05-04-2009 > John 06-08-2009 > John 01-01-2010 > Tony 11-02-2010 > Tony 11-02-2010 > > I want is to assign the serial against each name starting from 1 in > table2 > according to date in ascending order and the max serial should be assigned > against each name in Table1 i.e.. > > Table1 > Name Serial Date > John 3 01-01-2010 > Tony 2 11-02-2010 > > Table2 > Name Serial Date > John 1 5-4-2009 > John 2 6-8-2009 > John 3 1-1-2010 > Tony 1 11-2-2010 > Tony 2 11-02-2010 > > > > Regards, > Muhammad Bilal >
From: Muhammad Bilal on 22 Apr 2010 15:50 thankx but i want not only to show but update the serial column in the tables as shown in the example below using sql server 2000. Regards, Muhammad Bilal "Uri Dimant" wrote: > SELECTName ,[Serial Date],ROW_NUMBER () OVER (PARTITION BY name ORDER BY > [Serial Date] DESC) number > FROM tbl > > > > "Muhammad Bilal" <MuhammadBilal(a)discussions.microsoft.com> wrote in message > news:BF2A852C-1E8F-4008-A647-BA1E20C63184(a)microsoft.com... > > Hi. > > > > I have two tables > > Table1 > > Name Serial Date > > John 01-01-2010 > > Tony 11-02-2010 > > > > Table2 > > Name Serial Date > > John 05-04-2009 > > John 06-08-2009 > > John 01-01-2010 > > Tony 11-02-2010 > > Tony 11-02-2010 > > > > I want is to assign the serial against each name starting from 1 in > > table2 > > according to date in ascending order and the max serial should be assigned > > against each name in Table1 i.e.. > > > > Table1 > > Name Serial Date > > John 3 01-01-2010 > > Tony 2 11-02-2010 > > > > Table2 > > Name Serial Date > > John 1 5-4-2009 > > John 2 6-8-2009 > > John 3 1-1-2010 > > Tony 1 11-2-2010 > > Tony 2 11-02-2010 > > > > > > > > Regards, > > Muhammad Bilal > > > > > . >
From: Erland Sommarskog on 22 Apr 2010 17:48 Muhammad Bilal (MuhammadBilal(a)discussions.microsoft.com) writes: > I have two tables > Table1 > Name Serial Date > John 01-01-2010 > Tony 11-02-2010 > > Table2 > Name Serial Date > John 05-04-2009 > John 06-08-2009 > John 01-01-2010 > Tony 11-02-2010 > Tony 11-02-2010 > > I want is to assign the serial against each name starting from 1 in > table2 according to date in ascending order and the max serial should > be assigned against each name in Table1 i.e.. WITH numbering (rowno, Serial) AS ( SELECT row_number() OVER(PARTITION BY name ORDER BY Date), Serial FROM Table2 ) UPDATE numbering SET Serial = rowno The update of Table1 should be simple once you have this. The WITH clause sets up a Common Table Expression (CTE), which is a view that only has scope within the query. Then we update the view. A somewhat obscure syntax maybe, but it's efficient. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Uri Dimant on 22 Apr 2010 23:16
Ok, then please see Erland's post "Muhammad Bilal" <MuhammadBilal(a)discussions.microsoft.com> wrote in message news:F35D4938-F176-4484-9608-FA5A75677866(a)microsoft.com... > thankx > but i want not only to show but update the serial column in the tables as > shown in the example below using sql server 2000. > > > Regards, > Muhammad Bilal > > "Uri Dimant" wrote: > >> SELECTName ,[Serial Date],ROW_NUMBER () OVER (PARTITION BY name ORDER BY >> [Serial Date] DESC) number >> FROM tbl >> >> >> >> "Muhammad Bilal" <MuhammadBilal(a)discussions.microsoft.com> wrote in >> message >> news:BF2A852C-1E8F-4008-A647-BA1E20C63184(a)microsoft.com... >> > Hi. >> > >> > I have two tables >> > Table1 >> > Name Serial Date >> > John 01-01-2010 >> > Tony 11-02-2010 >> > >> > Table2 >> > Name Serial Date >> > John 05-04-2009 >> > John 06-08-2009 >> > John 01-01-2010 >> > Tony 11-02-2010 >> > Tony 11-02-2010 >> > >> > I want is to assign the serial against each name starting from 1 in >> > table2 >> > according to date in ascending order and the max serial should be >> > assigned >> > against each name in Table1 i.e.. >> > >> > Table1 >> > Name Serial Date >> > John 3 01-01-2010 >> > Tony 2 11-02-2010 >> > >> > Table2 >> > Name Serial Date >> > John 1 5-4-2009 >> > John 2 6-8-2009 >> > John 3 1-1-2010 >> > Tony 1 11-2-2010 >> > Tony 2 11-02-2010 >> > >> > >> > >> > Regards, >> > Muhammad Bilal >> > >> >> >> . >> |