Problem in query - 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 2000 Forums

Transact-SQL (2000)

Problem in query

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-02-05 : 04:26:54

hi,

I have table photo as below: and SQL 2000

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 for SQL 2000 server

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..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 04:38:12

If there are three or more records for same ID, how do you decide which two should be returned?

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

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-02-05 : 04:51:37

that is the result i want first 2 record for each photo id.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 04:59:27

Please define FIRST.

There are no such things as "FIRST" in a relational database.
If you mean "any two" or "random 2" I can help you.

Otherwise you have to provide information how you decide which records that are considered to be "first".

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 05:01:21

If you have a DateAdded column which is a datetime column, you can rephrase your question to
"I want two oldest/newest records per group, according to DateAdded column".

Or if you have an IDENTITY column, you can rephrase your question to
"I want two first/last records per group, according to IDENTITY column".

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

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-02-05 : 11:09:44

thanks for the reply...
what i want in sql server is i have lots of photos and its comments and one photo has multiple comments..and
i want last two comments for each 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

and output says..
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 for SQL 2000 server

------------------------------------
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

two rows can be lastly added so we have a column date of comments in table which is not here...

so coupon of rows 2 for all photos will be an output.

I have tried lots of ways but still problem occur in SQL 2000

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-05 : 11:23:33

try this

DECLARE @TEMP TABLE ( ID INT, photoname VARCHAR(30), comments VARCHAR(30))
INSERT INTO @temp
SELECT 1, '1.jpg', 'good one...' UNION ALL
SELECT 1, '2good.jpg', 'test' UNION ALL
SELECT 1, '3img.jpg', 'test comments..' UNION ALL
SELECT 2, 'i.jpg', 'testest' UNION ALL
SELECT 2, 'ii.jpg', 'testtesttest' UNION ALL
SELECT 3, 'i.good.jpg', 'test' UNION ALL
SELECT 3, '1td.jpg', 'test' UNION ALL
SELECT 3, '3td.jpg', 'test' UNION ALL
SELECT 3, '4td.jpg', 'test' UNION ALL
SELECT 4, 'tt.jpg', 'good'

SELECT
a.id,b.photoname,b.comments
FROM
(SELECT DISTINCT id FROM @temp t) a
CROSS APPLY
(SELECT TOP 2 * FROM @TEMP WHERE id = a.id) b

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 11:31:40

This doesn't work in SQL 2000.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 11:32:53

I have already given query for SQL 2000.

You need to determine what you need:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=119256

Padmaja
Starting Member

6 Posts

Posted - 2009-02-06 : 04:33:56

DECLARE @temp TABLE ( ID INT, photoname VARCHAR(30), comments VARCHAR(30))
INSERT INTO @temp
SELECT 1, '1.jpg', 'good one...' UNION ALL
SELECT 1, '2good.jpg', 'test' UNION ALL
SELECT 1, '3img.jpg', 'test comments..' UNION ALL
SELECT 2, 'i.jpg', 'testest' UNION ALL
SELECT 2, 'ii.jpg', 'testtesttest' UNION ALL
SELECT 3, 'i.good.jpg', 'test' UNION ALL
SELECT 3, '1td.jpg', 'test' UNION ALL
SELECT 3, '3td.jpg', 'test' UNION ALL
SELECT 3, '4td.jpg', 'test' UNION ALL
SELECT 4, 'tt.jpg', 'good'

DECLARE @temp1 TABLE ( ID INT, photoname VARCHAR(30), comments VARCHAR(30))
DECLARe @var INT
SELECT @var = (SELECT MIN(id) FROM @temp)
WHILE(@var IS NOT NULL)
BEGIN
INSERT INTO @temp1
SELECT TOP 2 * FROM @temp
WHERE id = @var
SELECT @var = (SELECT MIN(id) FROM @temp WHERE id > @var)
END
SELECT * FROM @temp1

padmaja

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-02-06 : 08:00:45

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

Thanks again you all for your precious time :)
Go to Top of Page

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-09 : 11:36:45

Hi,

Try like this

select * from @temp
where photoname like '%[0-2]%'
or photoname like 'i%' or photoname like 't%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources