finding right row - 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

General SQL Server Forums

New to SQL Server Programming

finding right row

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-03-11 : 09:20:58

i have


create table test

(id_text int
,text_name varchar(10)
,id_author int)

insert into test (id_text, text_name, id_author) values (205, 'name1', 123)
insert into test (id_text, text_name, id_author) values (205, 'name2', 124)
insert into test (id_text, text_name, id_author) values (205, 'name2', 125)
insert into test (id_text, text_name, id_author) values (206, 'name2', 124)
insert into test (id_text, text_name, id_author) values (207, 'name2', 122)
insert into test (id_text, text_name, id_author) values (207, 'name2', 128)

select id_text, id_author from test

and i need to get out only id_text with the first author.
any solutions for sql server 2000?

thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 09:21:47

What is "first"?

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-11 : 09:22:21

from your sample data, what would you expect the result to be?

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 09:22:48

See, read and understand this blog post
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 09:27:51

SELECT TOP 1 * FROM Test ORDER BY ID_Author

E 12°55'05.25"
N 56°04'39.16"

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-03-11 : 09:28:38

i should get out:
205,name1,123
206,name2,124
207,name2,122

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 09:32:36

select t.id_text, t.text_name, t.id_author
from test as t
inner join (select id_text, min(id_author) AS id from test group by id_text) AS y
on y.id_text = t.id_text and y.id = t.id_author

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources