can you do this??????? - 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

can you do this???????

Author

Topic

raky
Aged Yak Warrior

767 Posts

Posted - 2007-12-31 : 23:48:06

Hi! i need 2 solve the following problem
I have a table with following records
name id
a 1
a 2
a 3
a 4
b 1
b 2
b 3
and so on records like this. i need to retrieve the top 2 records for each name
i need output like this
name id
a 1
a 2
b 1
b 2
please let me know how to accomplish this task.i hope u understood my problem.awaiting for ur replies.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-01 : 00:42:28

If its sql 2005 do like this:-

SELECT t.name,t.id 
FROM
( SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS 'RowNo',
name,
id
FROM Table
)t
WHERE t.RowNo<=2
ORDER BY t.name

If its sql 2000 then do like this:-

SELECT tmp.name,tmp.id 
FROM
(
  SELECT t1.name,t1.id,
           ( SELECT COUNT(*)+1 
            FROM Table
            WHERE name=t1.name
            AND id <t1.id) AS 'RowNo'
  FROM Table t1
) tmp 
WHERE tmp.RowNo <=2
ORDER BY tmp.name

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-02 : 00:59:16

quote:

Originally posted by visakh16

If its sql 2005 do like this:-

SELECT t.name,t.id 
FROM
( SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS 'RowNo',
name,
id
FROM Table
)t
WHERE t.RowNo<=2
ORDER BY t.name

If its sql 2000 then do like this:-

SELECT tmp.name,tmp.id 
FROM
(
  SELECT t1.name,t1.id,
           ( SELECT COUNT(*)+1 
            FROM Table
            WHERE name=t1.name
            AND id <t1.id) AS 'RowNo'
  FROM Table t1
) tmp 
WHERE tmp.RowNo <=2
ORDER BY tmp.name

Running subquery in 2000 would take long time if there are thousands of data. There is quicker way to do it
Refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-01-02 : 02:48:09

Hi

I am using like above replies but i get this error

Row_Number is not a recognized function name.

Where I did mistake?

Is any changes in query settings, i am using SQL SERVER 2000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-02 : 03:18:24

quote:
Originally posted by karthickbabu

Hi

I am using like above replies but i get this error

Row_Number is not a recognized function name.

Where I did mistake?

Is any changes in query settings, i am using SQL SERVER 2000

Yes. visakh16 stated that query is for SQL Server 2005 and also suggested solution for SQL 2000.

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-02 : 03:18:26

quote:
Originally posted by karthickbabu

Hi

I am using like above replies but i get this error

Row_Number is not a recognized function name.

Where I did mistake?

Is any changes in query settings, i am using SQL SERVER 2000

Row_Number() function is not available in SQL Server 2000
Refer the link I posted for other approach

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources