Simple select distinct question

[markj11]

if I have
TableA
A B C
1A 1B 1C
2A 2B 2C
2A 3B 2C

How do I get results:
1A 1B 1C
2A 2B 2C

Thanks,
Mark

 

[gmmastros]

This isn't a simple select distinct question because your data isn't distinct. Column A has 2 distinct values and column c has 2 distinct values. In your results, how do you determine the column B value you return, I mean, why 2B and not 3B? After answering this question, we should be able to help better.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

If you want distinct A's with Min B's, and whatever C, then look at this query:

[green]-- Dummy data[/green]
Declare @TableA Table(A VarChar(10), B VarChar(10), C VarChar(10))

Insert Into @TableA Values('1A','1B','1C')
Insert Into @TableA Values('2A','2B','2C')
Insert Into @TableA Values('2A','3B','2C')

[green]-- Teh query[/green]
Select T.A, T.B, T.C
From   @TableA As T
       Inner join (
         Select A, Min(b) As B
         From   @TableA
         Group By A
         ) As A On T.A = A.A And T.B = A.B

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vbkris]

hi,

i noticed from ur data that u r looking for rows where the numbers match.

try this:

select * from Table1
where
substring(a,0,patindex('%[^0-9]%',a))=substring(b,0,patindex('%[^0-9]%',b))
and substring(a,0,patindex('%[^0-9]%',a))=substring(c,0,patindex('%[^0-9]%',c))

Known is handfull, Unknown is worldfull