Post reply

SELECT distinct..... order by newid()

  • February 4, 2008 at 7:02 am

    #186255

    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?

  • February 4, 2008 at 7:09 am

    #774731

    Here is one way to skin this cat

    SELECTTOP 4

    r.A1,

    r.A2,

    r.A3

    FROM(

    SELECTA1,

    A2,

    A3,

    ROW_NUMBER() OVER (PARTITION BY A2 ORDER BY NEWID()) AS RecID

    FROMTest

    ) AS r

    WHEREr.RecID = 1

    ORDER BYNEWID()

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

  • February 4, 2008 at 7:53 am

    #774744

    Since this was reposted in the SQL 2000 forum, please direct replies there.

    Select distinct.. order by newID() (SQL 2000)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 4, 2008 at 8:08 am

    #774750

    Oh man.

    Another cross-posting...

    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"

  • February 4, 2008 at 8:10 am

    #774753

    Not exactly. The OP admits that he posted in the wrong forum initially and reposted in the 2000 forum after you replied.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply