??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

???

Author

Topic

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-09-09 : 06:16:45

TABLE DATA

1 GTL 2001
2 PATNI 2002
3 MBT 2001
4 MBT 2002
5 MBT 2005
6 TCS 2000
7 INDUS 2002
8 INDUS 2003
9 INDUS 2004
10 ZENSAR 2001
11 EMPHASYS 2003
12 GTL 2002
13 EMPHASYS 2004
14 GLOBAL 2005
15 L AND T 2006
16 L AND T 2005
17 HDFC 2004
18 TEST CO 2000
19 TEST CO 2001
20 TEST CO 2002
21 EMPHASYS 2005
22 INFOSYS 2003
23 INFOSYS 2004
24 INFOSYS 2005
25 MASTEK 2006
26 MASTEK 2007
27 MASTEK 2006
28 PATNI 2003

REQD O/P

1 GTL
2 PATNI
3 MBT
4 TCS
5 INDUS
6 ZENSAR
7 EMPHASYS
8 GLOBAL
9 L AND T
10 HDFC
11 TEST CO
12 INFOSYS
13 MASTEK
14 PATNI

thanks in advance,
Mahesh

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-09 : 06:22:52

Select * From <YourTAble> tbl1 Where <ID> =
(Select Max(ID) From <YourTable> tbl2 Where
tbl1.Companyname = Tbl2.Coompanyname)

Chirag

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-09-09 : 06:28:16

thnks chirag,

but i have mentioned the id as serial no. its returning the concern id of the record. i want to get distinct company name with serial no.

please check reqd o/p again.

thanks,

Mahesh

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-09 : 06:37:14

Select Distinct (Select Count(Distinct CompanyName) From YourTable v1 Where v1.ID<=V2.ID), CompanyName
From YourTable v2
order by 1

Buts its always good get ID and everything in front end... this query will take much longer time to excute for more records.

So the second option to insert the distinct records in the Temp Table.. with the identity column..
Chirag

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-09-09 : 07:33:58

thanks chirag,

but still its not working. its returning correct srno in case of duplicate company names. if company names are more than 2 its returning the same srno.

Mahesh

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-09 : 08:03:17

[code]

Declare @var Table
(
ID int,
CompanyName varchar(100)
)

Insert @var
Select 1,'GTL' Union All
Select 2,'PATNI' Union All
Select 3,'MBT' Union All
Select 4,'MBT' Union All
Select 5,'MBT' Union All
Select 6,'TCS' Union All
Select 7,'INDUS' Union All
Select 8,'INDUS' Union All
Select 9,'INDUS' Union All
Select 10,'ZENSAR' Union All
Select 11,'EMPHASYS' Union All
Select 12,'GTL' Union All
Select 13,'EMPHASYS' Union All
Select 14,'GLOBAL' Union All
Select 15,'L AND T' Union All
Select 16,'L AND T' Union All
Select 17,'HDFC' Union All
Select 18,'TEST CO' Union All
Select 19,'TEST CO' Union All
Select 20,'TEST CO' Union All
Select 21,'EMPHASYS' Union All
Select 22,'INFOSYS' Union All
Select 23,'INFOSYS' Union All
Select 24,'INFOSYS' Union All
Select 25,'MASTEK' Union All
Select 26,'MASTEK' Union All
Select 27,'MASTEK' Union All
Select 28,'PATNI'

Select (
Select Count(1) From
(
Select ID As ID From @var v3
where ID = (
Select Max(ID) from @var v2 Where v3.CompanyName = v2.CompanyName
)
) As v4
Where V4.ID <=v1.ID
) as RowNum,
CompanyName From @var v1 Where
ID = (Select Max(ID) from @var v2 Where v1.CompanyName = v2.CompanyName)

--OR

Select Identity(Int,1,1) As RowNum,CompanyName Into #Temp From
(Select Distinct CompanyName From @var) as f

Select * From #Temp

Drop Table #Temp
[/code]

Chirag

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-09-11 : 08:41:38

thanks chirag,

it also works as well as ...

SELECT MAX(ID) AS ID , CompanyName FROM TEMP
GROUP BY CompanyName ORDER BY ID

thanks for ur efforts,

Mahesh

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-11 : 08:46:53

But this query will not give you the ID in the sequential order, where as in your required output in the first post,
stated that you want to get the detail in sequential order.

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-11 : 09:02:33

And what about

2 - Patni
14 - Patni

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources