TSQL query help.. - 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)

TSQL query help..

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-02-05 : 00:48:15

hi,

I have table photo as below:

ID photoname comments
1 1.jpg good one...
1 2good.jpg test
1 3img.jpg test comments..
2 i.jpg testest
2 ii.jpg testtesttest
3 i.good.jpg test
3 1td.jpg test
3 3td.jpg test
3 4td.jpg test
4 tt.jpg good

I want first 2 rows from above table ID Wise as below. If only one row for ID then it come once so how can i do this i have tried but confused.... please help

ID photoname comments
1 1.jpg good one...
1 2good.jpg test
2 i.jpg testest
2 ii.jpg testtesttest
3 i.good.jpg test
3 1td.jpg test
4 tt.jpg good

thanks..

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-05 : 00:52:31

select * from
( select *,row_number() over ( partition by ID order by id ) as rn from urtable ) t
where t.rn in (1,2)

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-02-05 : 01:12:43

i have sql 2000 and above query worked in sql 2005.. any other way..

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 07:17:37

[code]Select Identity(int,1,1) as PhotoID,* into #H from photo

Select M.ID,M.photoname,M.Comments
from
(Select ID,photoname,Comments,(Select Count(*) from #H
Where ID = Z.ID and PhotoID <= Z.PhotoID) as Seq
from #H Z)M
Where M.seq in (1,2)[/code]

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-05 : 07:58:13

Which 2 rows ?? You need to have a criteria. Sodeep's query would just randomly give you any 2 rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-05 : 11:57:47

you need to have a column on your table to determine. there's no such thing like first,second ,...records of a table unless you specify sequence with respect to column(s) value by means of an order by.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-05 : 11:59:36

even using IDENTITY(int,1,1) while inserting into a table using a select with ORDER BY does not guarantee records will inserted and id values will be generated in same order

http://support.microsoft.com/kb/273586

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 04:39:31

Duplicate post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119268

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

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-02-06 : 07:51:42

thanks for help i have no criteria just last inserted two rows for each photo comment..

Thanks again you all for your precious time :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-06 : 09:09:33

quote:
Originally posted by keyursoni85

thanks for help i have no criteria just last inserted two rows for each photo comment..

Thanks again you all for your precious time :)

that itself is a criteria