Select Top 3 rows of Column1

• Ashwin M N

  Hall of Fame

  Points: 3668

  More actions

  March 3, 2010 at 6:52 am

  #219563

  CREATE TABLE Temp (ID1 INT,ID2 INT,ID3 INT)

  INSERT INTO Temp (id1,id2,id3)

  select 1,1,61

  union all

  select 1,2,62

  union all

  select 1,3,63

  union all

  select 1,4,64

  union all

  select 1,5,65

  union all

  select 2,6,66

  union all

  select 2,7,67

  union all

  select 2,8,68

  union all

  select 2,9,69

  union all

  select 3,10,71

  union all

  select 3,11,72

  union all

  select 3,12,73

  union all

  select 3,13,74

  union all

  select 4,14,75

  union all

  select 4,15,76

  union all

  select 4,16,77

  union all

  select 5,17,78

  union all

  select 5,18,79

  union all

  select 5,19,80

  I have a table with id2,id3 running number and id1 a fixed number for couple of rows and it changes for every couple of

  rows.I want a query to display top 3 records for very id1 based on id2 column desc.The output of the query should display

  as follows.

  ID1 ID2 ID3

  1161

  1262

  1363

  2666

  2767

  2868

  31071

  31172

  31273

  41475

  41576

  41677

  51778

  51879

  51980

  --Drop table temp

• WayneS

  SSC Guru

  Points: 95460

  More actions

  March 3, 2010 at 7:13 am

  #1127752

  Check out Books Online (BOL) for the ROW_NUMBER() function... with this, you can easily accomplish what you are trying to do.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• Paul White

  SSC Guru

  Points: 150467

  More actions

  March 3, 2010 at 9:17 am

  #1127872

  Your output data example is wrong :doze:

  Either of the following alternatives will work:

  SELECT InputSet.ID1,

  iTVF.ID2,

  iTVF.ID3

  FROM (

  SELECT DISTINCT T.ID1

  FROM dbo.Temp T

  )

  InputSet

  CROSS

  APPLY (

  SELECT TOP (3)

  *

  FROM dbo.Temp T2

  WHERE T2.ID1 = InputSet.ID1

  ORDER BY

  T2.ID2 DESC

  ) iTVF

  ORDER BY

  InputSet.ID1,

  iTVF.ID2 DESC;

  SELECT Numbered.ID1,

  Numbered.ID2,

  Numbered.ID3

  FROM (

  SELECT *,

  rn = ROW_NUMBER() OVER (

  PARTITION BY T.ID1

  ORDER BY T.ID2 DESC)

  FROM dbo.Temp T

  ) Numbered

  WHERE Numbered.rn BETWEEN 1 AND 3

  ORDER BY

  Numbered.ID1,

  Numbered.rn;

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  March 4, 2010 at 4:33 am

  #1128389

  SELECT t.id1,t.id2,t.id3 FROM

  ( SELECT id1,id2,id3 ,ROW_NUMBER() OVER (PARTITION BY ID1

  ORDER BY ID2 DESC) AS RANK FROM temp ) t

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• Ashwin M N

  Hall of Fame

  Points: 3668

  More actions

  March 4, 2010 at 5:17 am

  #1128411

  Ok thanks for replies I got want I wanted.

• Paul White

  SSC Guru

  Points: 150467

  More actions

  March 4, 2010 at 5:54 am

  #1128431

  Bhuvnesh (3/4/2010)

  ---

  ...code...

  Hey! That's the same code I posted but without the TOP 3 or the final ORDER BY! :laugh:

  Paul White
  SQLPerformance.com
  SQLkiwi blog
  @SQL_Kiwi