Need help on query

[krishnan s]

Hi, I have a table with username and Registration number. I need to get the Registration number for the distinct usernames from the table.Can it be done using the "DISTINCT" query.?

[Ashfield]

Maybe. Why not try it and see?

Bob Ashfield Consultants Ltd

modified on Monday, April 28, 2008 2:56 AM

[krishnan s]

I tried it , Since i can get only the Username , How to select the registration numbers from it? , Is there any other way to frame the query?

[Ashfield]

1. Post some samples from your table 2. Post your query Then I will be able to help. If you have data like this: Name Reg M. Mouse 1 D Duck 2 then SELECT DISTINCT Name, reg from table will do it.

Bob Ashfield Consultants Ltd

[krishnan s]

My Table entries are Name Reg M. Mouse 1 D Duck 2 L Lion 3 D Duck 4 M. Mouse 5 SELECT DISTINCT Name, reg from table This query gives the distinct combinations of name and reg not the distinct values for name . Since the combinations are unique all the entries will be returned .I need to get only Reg No's: 1,2,3 Any idea on How to frame the query for it?

[Blue_Boy]

here it is: select distinct [name], (select top 1 a1.reg from myTable as a1 where a1.name = myTable.name order by a1.reg asc) from myTable

---

I Love T-SQL

modified on Monday, April 28, 2008 3:57 AM

[krishnan s]

Thanks for ur help, Its working fine now..

[Blue_Boy]

You are welcome.

---

I Love T-SQL

[Marek Grzenkowicz]

If you are using SQL Server 2005, you should avoid subselects, because their performance is poor. I'd rather use:

SELECT
NAME
, MIN(REG)
FROM dbo.MYTABLE
GROUP BY
NAME

[Blue_Boy]

thanks but your reply is not for me.

---

I Love T-SQL