Distinct Records

Hi All,
i want to Write the query which will give me the distinct top 1 records.
see i have my table and data in that table is in this way.
ReqNo Name Other
1 chirag
1 xyz
1 ABC
2 ZZZ
2 YYY
2 QQQ
3 NNN
i want the output as following
1 Chirag
2 ZZZ
3 NNN
the first records of each and every Reqno. i am trying this from a long time
but not able to get the same if you guys has any solution then please help m
e
out.Try this
Alter table TableName add sno int identity
go
Select * from TableName where sno in (Select min(sno) from MyTest group
by ReqNo)
go
Alter table TableName drop column sno
Madhivanan|||Hi
It should be
Alter table TableName add sno int identity
go
Select * from TableName where sno in (Select min(sno) from TableName
group
by ReqNo)
go
Alter table TableName drop column sno
Madhivanan|||Hello Chirag
You can do it as Madhivanan said if you have not created any views based on
this table. If you have any views based on this table then alterting the
table may affect the views. Views become invalid. In that case you can do
like this.
Create table #Temp (Slno int identity, ReqNo int, Name Varchar(50))
Insert into #Temp Select * From TableName
Select * from #Temp where Slno in (Select min(Slno) from #Temp
group by ReqNo)
#Temp is a temporary table. So it will be deleted automatically.
Thank you
Baiju
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1109834405.131179.254390@.l41g2000cwc.googlegroups.com...
> Hi
> It should be
> Alter table TableName add sno int identity
> go
> Select * from TableName where sno in (Select min(sno) from TableName
> group
> by ReqNo)
> go
> Alter table TableName drop column sno
>
> Madhivanan
>|||I would suggest you to create a running number and have it permantently.
creating a temp table, inserting data and doing a select on that, deleting
temp table..will be a workable solution..
but if your table has large number of records, this query takes its own
time..
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
"Baiju" <baiju@.indus-systems.com> wrote in message
news:O#r6yd8HFHA.2276@.TK2MSFTNGP15.phx.gbl...
> Hello Chirag
> You can do it as Madhivanan said if you have not created any views based
on
> this table. If you have any views based on this table then alterting the
> table may affect the views. Views become invalid. In that case you can do
> like this.
> Create table #Temp (Slno int identity, ReqNo int, Name Varchar(50))
> Insert into #Temp Select * From TableName
> Select * from #Temp where Slno in (Select min(Slno) from #Temp
> group by ReqNo)
> #Temp is a temporary table. So it will be deleted automatically.
> Thank you
> Baiju
>
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:1109834405.131179.254390@.l41g2000cwc.googlegroups.com...
>|||try
select ReqNo, Max(Name) from ... group by ReqNo
"Chirag" wrote:

> Hi All,
> i want to Write the query which will give me the distinct top 1 records.
> see i have my table and data in that table is in this way.
> ReqNo Name Other
> 1 chirag
> 1 xyz
> 1 ABC
> 2 ZZZ
> 2 YYY
> 2 QQQ
> 3 NNN
> i want the output as following
> 1 Chirag
> 2 ZZZ
> 3 NNN
>
> the first records of each and every Reqno. i am trying this from a long ti
me
> but not able to get the same if you guys has any solution then please help
me
> out.|||If those are the only columns then there's no such thing as the "first"
for each ReqNo. You haven't identified "first" in your table and tables
have no fixed concept of order. The best you can do is probably with an
aggregate function:
SELECT reqno, MIN(name)
FROM YourTable
GROUP BY reqno
David Portas
SQL Server MVP
--