problem with selecting top 5

• abhas

  SSCarpal Tunnel

  Points: 4681

  More actions

  August 13, 2014 at 11:01 am

  #289714

  Hi,

  I am having temp table where i am inserting records from to queries.

  eg.

  insert into #temp 1

  select distinct ft.Teacher,ft.Namefrom #Freqteacher1 ft

  insert into #temp 1

  select distinct ft.Teacher,ft.Namefrom #Freqteacher2 ft

  Once i have inserted data into #temp1, I want to display 5 records from #temp but i want to display all records inserted from Freqteacher1 i.e. if i have inserted 3 records from this table then i want to display all 3 records from this table and remaining two records from Freqteacher2 .

  What should i need to follow, Please guide

  Thanks

  Abhas.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  August 13, 2014 at 11:04 am

  #1737712

  abhas (8/13/2014)

  ---

  Hi,

  I am having temp table where i am inserting records from to queries.

  eg.

  insert into #temp 1

  select distinct ft.Teacher,ft.Namefrom #Freqteacher1 ft

  insert into #temp 1

  select distinct ft.Teacher,ft.Namefrom #Freqteacher2 ft

  Once i have inserted data into #temp1, I want to display 5 records from #temp but i want to display all records inserted from Freqteacher1 i.e. if i have inserted 3 records from this table then i want to display all 3 records from this table and remaining two records from Freqteacher2 .

  What should i need to follow, Please guide

  Thanks

  Abhas.

  The easiest way by far would be to add a SortOrder column to #temp. Then in your first insert you put 1 for SortOrder, in your second table you insert a 2. Then your final query would be select top 5 order by SortOrder.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736430

  More actions

  August 13, 2014 at 11:10 am

  #1737715

  Sean is correct in that you need some way of ordering things so that you know what table you are getting data from. Since you're trying to ensure all data (up to 5) comes from one table, you need to note that.

• rxm119528

  SSCrazy

  Points: 2960

  More actions

  August 13, 2014 at 11:35 am

  #1737733

  declare @Freqteacher1 table

  (

  Teacher int,

  name varchar(256))

  declare @Freqteacher2 table

  (

  Teacher int,

  name varchar(256))

  declare @temp table

  (

  rownum int,

  Teacher int,

  name varchar(256))

  insert into @Freqteacher1 values(1,'Freqteacher1'),(2,'Freqteacher2'),(3,'Freqteacher2'),(3,'Freqteacher2')

  insert into @Freqteacher2 values(11,'Freqteacher11'),(12,'Freqteacher12'),(13,'Freqteacher13')

  insert into @temp

  select row_number() over(order by Teacher) ,* from (select distinct ft.Teacher,ft.Name from @Freqteacher1 ft) t1

  insert into @temp

  select distinct 999999999,ft.Teacher,ft.Name from @Freqteacher2 ft

  select top 5 Teacher,Name from @temp order by rownum