need to avoid in clause - 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 2005 Forums

Transact-SQL (2005)

need to avoid in clause

Author

Topic

snomad
Starting Member

22 Posts

Posted - 2009-08-05 : 11:07:16

Hi all!

I'm soooo stuck with this.

Let's say I have a table like this:
id data
1 ab
2 ac
3 bb

I need to
a) select min(id) from tbl group by substring(data,1,1)
b) select data from tbl where id in
(
select min(id) from tbl group by substring(data,1,1)
)

But, it's a very big tbl & field in real life. I don't want to use an in clause. Also need to create a view with it so can't cr table var etc.

Is there a better way?

Thank you VERY much in advance.

Thank you!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-05 : 11:38:15

select data from tbl where id =
(
select min(id) from tbl group by substring(data,1,1)
)

or this way

SELECT TOP 1 WITH TIES Data
FROM Tbl
ORDER BY SUBSTRING(Data, 1, 1)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources