March 17, 2010 at 9:14 am
Hello All,
My envinronment is SQL 2005. The data in my table is as follow:
ID Test
1 Tax Exempt
1 Tax Composition
I would like to pull only one Test for this ID. It does not matter which test. I don't have a date field or any other unique key to differentiate between the two test for this ID
select *
from student s
left join Test t
on s.ID = t.ID
result:
1 John Doe Tax Exempt
1 John Doe Tax Composition
desire result:
1 John Doe Tax Exempt
or
1 John Doe Tax Composition
Any input/suggestion is greatly appreciated.
Thanks
March 17, 2010 at 9:24 am
Hi,
just use GROUP BY in combination with MIN or MAX:
declare @myTable table (ID int, Test varchar(200))
insert into @myTable
select 1, 'Tax Exempt' union all
select 1, 'Tax Composition'
SELECT ID, MAX(Test) -- or use MIN
FROM @myTable
GROUP BY ID
/Markus
March 17, 2010 at 12:30 pm
I was about to go the long way by using identity(smallint,1,1) as RowNmbr and then pick the min or max by RowNmbr. I didn't realize I can use min and max function on text field. Thank you for your help. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply