Select distinct ... order by newid()

• Ashwin M N

  Hall of Fame

  Points: 3668

  More actions

  February 4, 2008 at 7:30 am

  #378335

  I had posted this in SQL 2005 forum.

  First thanks Peso I got the answer in SQL 2005 but my client is using SQL 2000.

  create table test (A1 int identity(1000,1),A2 int,A3 varchar(100))

  INSERT INTO Test (A2,A3) SELECT 1,'Shop Clearance at Bay Clothing Company'

  INSERT INTO Test (A2,A3) SELECT 2,'SAVE 10% on all orders at CurrentLabels'

  INSERT INTO Test (A2,A3) SELECT 1,'Ultra White paper makes images and text'

  INSERT INTO Test (A2,A3) SELECT 3,'$5 off $30 at Best Deal Magazines.'

  INSERT INTO Test (A2,A3) SELECT 4,'Deal of the month'

  INSERT INTO Test (A2,A3) SELECT 2,'Heavy discount.'

  INSERT INTO Test (A2,A3) SELECT 5,'Factory outlet sale.'

  INSERT INTO Test (A2,A3) SELECT 6,'50% of on select items.'

  INSERT INTO Test (A2,A3) SELECT 10,'Clearance sale'

  INSERT INTO Test (A2,A3) SELECT 11,'Dusk to dawn sale'

  INSERT INTO Test (A2,A3) SELECT 4,'The white goods sale'

  INSERT INTO Test (A2,A3) SELECT 12,'Buy one get two.'

  INSERT INTO Test (A2,A3) SELECT 13,'Take An Additional 15% Off.'

  INSERT INTO Test (A2,A3) SELECT 12,'Hot Price! $32.99 After $10 Instant Savings.'

  select * from test

  select top 4 * from test order by newid()

  Using the above data I have to display 4 rows in the random order for every refresh.This I can do using newid().But the other clause is that we should have a unique A2 in displayed output like SELECT DISTINCT .I can to do this using in any way like stored procedure or creating a temp table.

  How can it be solved?

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  February 4, 2008 at 8:14 am

  #774754

  Try this

  SELECTTOP 4

  t1.A1,

  t1.A2,

  t1.A3

  FROMTest AS t1

  WHEREt1.A1 = (SELECT TOP 1 t2.A1 FROM Test AS t2 WHERE t2.A2 = t1.A2 ORDER BY NEWID())

  ORDER BYNEWID()

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• ZeyNep-583656

  Valued Member

  Points: 71

  More actions

  February 4, 2008 at 8:41 am

  #774766

  This is my solution:

  declare @A1 int

  declare @i int

  set @i=0

  create table #result (A1 int ,A2 int,A3 varchar(100))

  DECLARE cursorrr CURSOR FOR

  select a1 from test order by newId()

  OPEN cursorrr

  FETCH NEXT FROM cursorrr into @A1

  WHILE @@FETCH_STATUS = 0 and @i<4

  begin

  INSERT INTO #result (A1,A2,A3) SELECT top 1 @A1,a2,a3 from test t1 where not exists(select 1 from #result r where r.a2=t1.a2 )and t1.a1=@a1 order by newid()

  if @@rowcount=1

  set @i=@i+1

  FETCH NEXT FROM cursorrr into @A1

  end

  close cursorrr

  deallocate cursorrr

  SELECT * from #result

  DROP TABLE #result

  http://transactsql.blogspot.com/