Query General [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 23 Apr 2010 01:06

Thankx for ur kind reply

but as i said earlier im my post that i am using SQL Server 2000 and there
is no function of row_number() in SQl Server 2000.

So is there any way to do so in SQL Server 2000.

Regards,
Muhammad Bilal

"Erland Sommarskog" wrote:

> 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: Muhammad Bilal on 23 Apr 2010 01:13

From: Uri Dimant on 23 Apr 2010 01:47

Hi
See if the below works for you
CREATE TABLE #tmp

(name VARCHAR(20),Serial INT,date DATETIME)

INSERT INTO #tmp VALUES ('John' ,NULL,'05-04-2009')

INSERT INTO #tmp VALUES ('John' ,NULL,'06-08-2009')

INSERT INTO #tmp VALUES ('John' ,NULL,'07-04-2009')

INSERT INTO #tmp VALUES ('Tony' ,NULL,'11-02-2010')

INSERT INTO #tmp VALUES ('Tony' ,NULL,'11-03-2010')

UPDATE #tmp SET Serial=

(SELECT COUNT(*) FROM #tmp t

WHERE t.name=#tmp.name

AND t.date>=#tmp.date)

"Muhammad Bilal" <MuhammadBilal(a)discussions.microsoft.com> wrote in message
news:48FFB0F3-51F6-4997-82BF-3B957483B5A4(a)microsoft.com...
>
> Thankx for ur kind reply
>
> but as i said earlier im my post that i am using SQL Server 2000 and there
> is no function of row_number() in SQl Server 2000.
>
> So is there any way to do so in SQL Server 2000.
>
>
> Regards,
> Muhammad Bilal
>
>
>
> "Erland Sommarskog" wrote:
>
>> 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: Muhammad Bilal on 23 Apr 2010 03:15

No its not working it gives the same serial where dates are same . I used
row_number() in sql server 2008 it works 100% right but as i said i am using
sql server 2000 so is there any other way to do so.

Regards,
Muhammad Bilal

"Uri Dimant" wrote:

> Hi
> See if the below works for you
> CREATE TABLE #tmp
>
> (name VARCHAR(20),Serial INT,date DATETIME)
>
> INSERT INTO #tmp VALUES ('John' ,NULL,'05-04-2009')
>
> INSERT INTO #tmp VALUES ('John' ,NULL,'06-08-2009')
>
> INSERT INTO #tmp VALUES ('John' ,NULL,'07-04-2009')
>
> INSERT INTO #tmp VALUES ('Tony' ,NULL,'11-02-2010')
>
> INSERT INTO #tmp VALUES ('Tony' ,NULL,'11-03-2010')
>
>
>
> UPDATE #tmp SET Serial=
>
> (SELECT COUNT(*) FROM #tmp t
>
> WHERE t.name=#tmp.name
>
> AND t.date>=#tmp.date)
>
>
>
>
>
>
>
> "Muhammad Bilal" <MuhammadBilal(a)discussions.microsoft.com> wrote in message
> news:48FFB0F3-51F6-4997-82BF-3B957483B5A4(a)microsoft.com...
> >
> > Thankx for ur kind reply
> >
> > but as i said earlier im my post that i am using SQL Server 2000 and there
> > is no function of row_number() in SQl Server 2000.
> >
> > So is there any way to do so in SQL Server 2000.
> >
> >
> > Regards,
> > Muhammad Bilal
> >
> >
> >
> > "Erland Sommarskog" wrote:
> >
> >> 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: Erland Sommarskog on 23 Apr 2010 04:00

Muhammad Bilal (MuhammadBilal(a)discussions.microsoft.com) writes:
> No its not working it gives the same serial where dates are same . I
> used row_number() in sql server 2008 it works 100% right but as i said i
> am using sql server 2000 so is there any other way to do so.

Are there are columns in the table? In such case you would need to add
this to the SELECT COUNT(*) subquery, so that it runs over a unique
condition. And, yes, the subquery will be messy.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First | Prev | Next | Last
Pages: 1 2 3
Prev: SQL2008 SSMS loses custom serttings
Next: ANN: New version of Database Workbench, the multi-DBMS IDE now available!