Generating Record number for a result set?

Hi gurus;
any body would like to tell how to generate row numbers with a qurery?
thanks in advance
Ansari
Hi,
see the below link:-
http://www.sqlteam.com/item.asp?ItemID=1491
Thanks
Hari
MCDBA
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:#zj5Kp5cEHA.1000@.TK2MSFTNGP12.phx.gbl...
> Hi gurus;
> any body would like to tell how to generate row numbers with a qurery?
> thanks in advance
> Ansari
>
|||... and along the same lines:
http://www.databasejournal.com/featu...0894_3373861_1
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Hari Prasad wrote:
> Hi,
> see the below link:-
> http://www.sqlteam.com/item.asp?ItemID=1491
>
> Thanks
> Hari
> MCDBA
> "M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
> news:#zj5Kp5cEHA.1000@.TK2MSFTNGP12.phx.gbl...
>
>
sql

Generating Record number for a result set?

Hi gurus;
any body would like to tell how to generate row numbers with a qurery?
thanks in advance
AnsariHi,
see the below link:-
http://www.sqlteam.com/item.asp?ItemID=1491
Thanks
Hari
MCDBA
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:#zj5Kp5cEHA.1000@.TK2MSFTNGP12.phx.gbl...
> Hi gurus;
> any body would like to tell how to generate row numbers with a qurery?
> thanks in advance
> Ansari
>|||... and along the same lines:
http://www.databasejournal.com/features/mssql/article.php/10894_3373861_1
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Hari Prasad wrote:
> Hi,
> see the below link:-
> http://www.sqlteam.com/item.asp?ItemID=1491
>
> Thanks
> Hari
> MCDBA
> "M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
> news:#zj5Kp5cEHA.1000@.TK2MSFTNGP12.phx.gbl...
>>Hi gurus;
>>any body would like to tell how to generate row numbers with a qurery?
>>thanks in advance
>>Ansari
>>
>
>

Generating Record number for a result set?

Hi gurus;
any body would like to tell how to generate row numbers with a qurery?
thanks in advance
AnsariHi,
see the below link:-
http://www.sqlteam.com/item.asp?ItemID=1491
Thanks
Hari
MCDBA
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:#zj5Kp5cEHA.1000@.TK2MSFTNGP12.phx.gbl...
> Hi gurus;
> any body would like to tell how to generate row numbers with a qurery?
> thanks in advance
> Ansari
>|||... and along the same lines:
http://www.databasejournal.com/feat...10894_3373861_1
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Hari Prasad wrote:
> Hi,
> see the below link:-
> http://www.sqlteam.com/item.asp?ItemID=1491
>
> Thanks
> Hari
> MCDBA
> "M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
> news:#zj5Kp5cEHA.1000@.TK2MSFTNGP12.phx.gbl...
>
>
>

Generate Report Query

Hi every body
the image below describe entity relation diagram of my database i need to Write Query the generate report of licensed buildings (that has license saved in BuildingLicense Table ) and not licensed buildings in section or street

i need to gerate report like table below

select
B.streat,
Licensed = sum(case when BL.LicNo is not null then 1 else 0 end),
NotLicensed = sum(case when BL.LicNo is null then 1 else 0 end)
from
Building B (nolock)
left outer join BuildingLicensce BL (nolock)
on BL.Building_NO = B.BuildingNo
group by
B.streat

if you also what the Serial Number column, create a temp table with identity columns and dump above results intot the temp table.

|||Thanks MR

Lakshmana Kumar K for your interest
i tried you query
in my databast i stored 5 records in buildings table and when i run query i have this result

in result the sum of licensed and not licensed buildings give the total number of buildings
in your query it gives 8 Why?

I understoond your Query
in your Query u get the count of Licensecs for buildings in the street
but i just need the number of buildings which have license Record not the Count of Licenses
|||Finally i find the Answer

-- =============================================
-- Create Report of Licensed And Notlicensed Buildings in each Street
-- Auther Mahmoud Abd El Hakeem 09/02/2007
-- =============================================
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'LicBuilPerStreet'
AND type = 'U')
DROP TABLE [LicBuilPerStreet]
GO
SELECT Street.id AS [Street],
[Count Of Licensed Buildings]=
(
SELECT count(BuildingNO)
FROM Buildings
WHERE
Buildings.Street = Street.id
AND
buildingNo IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
,
[Count Of Not Licensed Buildings] =
(
SELECT count(buildingNO)
FROM Buildings
WHERE
(
Buildings.street =Street.id
AND
buildingNo NOT IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
) INTO [LicBuilPerStreet]
FROM
Street
LEFT OUTER JOIN Buildings
ON Buildings.street = Street.id

GROUP BY Street.id

-- IF U NEED TO HIDE NULL VALUES MAKE IT RIGHT OUTER JOIN

SELECT [street].[name],[LicBuilPerStreet].[Count Of Licensed Buildings],
[LicBuilPerStreet]. [Count Of Not Licensed Buildings]
FROM [LicBuilPerStreet]
INNER JOIN Street ON
[LicBuilPerStreet].street=Street.id
--WHERE street.secid= 15
ORDER BY Street.[Name]

and this is the result

i create temp table [LicBuilPerStreet]|||Finally i find the Answer

-- =============================================
-- Create Report of Licensed And Notlicensed Buildings in each Street
-- Auther Mahmoud Abd El Hakeem 09/02/2007
-- =============================================
IF EXISTS (SELECT name FROM sysobjects
WHERE name = N'LicBuilPerStreet'
AND type = 'U')
DROP TABLE [LicBuilPerStreet]
GO
SELECT Street.id AS [Street],
[Count Of Licensed Buildings]=
(
SELECT count(BuildingNO)
FROM Buildings
WHERE
Buildings.Street = Street.id
AND
buildingNo IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
,
[Count Of Not Licensed Buildings] =
(
SELECT count(buildingNO)
FROM Buildings
WHERE
(
Buildings.street =Street.id
AND
buildingNo NOT IN (SELECT DISTINCT Building_no FROM BuildingLicence)
)
) INTO [LicBuilPerStreet]
FROM
Street
LEFT OUTER JOIN Buildings
ON Buildings.street = Street.id

GROUP BY Street.id

-- IF U NEED TO HIDE NULL VALUES MAKE IT RIGHT OUTER JOIN

SELECT [street].[name],[LicBuilPerStreet].[Count Of Licensed Buildings],
[LicBuilPerStreet]. [Count Of Not Licensed Buildings]
FROM [LicBuilPerStreet]
INNER JOIN Street ON
[LicBuilPerStreet].street=Street.id
--WHERE street.secid= 15
ORDER BY Street.[Name]

and this is the result

i create temp table [LicBuilPerStreet]
Thanks MR

Lakshmana Kumar K 4 your Interest