Sometimes your project require whenever a page load or user search then each time you need to show a random order or we can say different records or order.
To achieve this we can use following command
SELECT * FROM yourtableName order by NewID()
in below snap I am fetching person table of Adventureworks database
So each time when I press F5 I get different records or order.
The only thing which need to remember here NEWID() make your fetch slow on large collection so the best idea is use it with selected record set.
I hope this will help you somewhere enjoy.
Yesterday was a very good learning day for me. One of my colleagues stuck in a very interesting issue.
He was fetching records from database with an ORDER BY clause on a VARCHAR field.
Couple of records of his table containing “-“hyphen symbol in the field on which he applied Sorting.
So after facing that problem I am sharing how to use collation in ORDER BY CLAUSE with your select statement.
Let’s consider below example
DECLARE @tblSubjects TABLE(
INSERT INTO @tblSubjects VALUES (‘HINDIBALBHARTI’)
INSERT INTO @tblSubjects VALUES (‘HINDI-BALBHARTI’)
SELECT Subjects AS Default_Collation_Sort_Order FROM @tblSubjects ORDER BY Subjects ;
SELECT Subjects AS Explicit_Changed_Collation_Sort_Order FROM @tblSubjects ORDER BY Subjects COLLATE Latin1_General_CI_AI ;
When you run above statement you will get different order in result set.
Generally this problem occurs when you use temp tables and your sort order and it is not as per your expectation.
So if you found this type of ORDER BY issue than you can explicitly use COLLATION