CODEWITHSUNDEEP
sqlsql-serverdatabase
Miguel Cordero
Miguel Cordero

Reputation: 141

Sample 3 random rows then order them alphabetically by another property

I was just wondering if I can get three random results and then order them by another column alphabetically.

Example:

SELECT TOP(3)[Name] 
FROM Table 
ORDER BY NEWID()
¿ORDER BY [Surname]?

Thanks in advance.

Upvotes: 1

Views: 206

Answers (4)

Mukesh Arora
Mukesh Arora

Reputation: 1813

you can try this - 

declare @name table
(Name varchar(20), SurName varchar(20))

insert into @name
values
('mukesh', 'arora'),
('amit', 'kumar'),
('Vijay', 'gupta'),
('jai', 'poddar'),
('vishal', 'sharma')

select Name from
( 
SELECT TOP(3)[Name] , [Surname]
FROM @name 
ORDER BY NEWID()
) a
ORDER BY [Surname]

Upvotes: 0

KrishnakumarS
KrishnakumarS

Reputation: 124

You can try this.

SELECT T.*
FROM
    (
    SELECT TOP(3)[Name], [Surname]
    FROM Table 
    ORDER BY NEWID()
    ) AS T
ORDER BY [Surname];

Upvotes: 0

jarlh
jarlh

Reputation: 44805

Have a derived table (sub-query) where you select 3 random rows. ORDER BY its result.

select [Name]
from
(
    SELECT TOP(3) [Name], [Surname]
    FROM Table 
    ORDER BY NEWID()
) dt
ORDER BY [Surname]

Upvotes: 1

Arnaud Peralta
Arnaud Peralta

Reputation: 1305

Not sure to understand the problem at 100%, but with a second ORDER BY it will sort all of the differents ID.

I did a CTE for achieving this result.

WITH CTE AS
(
SELECT TOP(3)[Name] 
FROM Table 
ORDER BY NEWID()
)

SELECT *
FROM CTE
ORDER BY [Surname]

Upvotes: 2

Related Questions