How to get N rows from a table in Sql Server database? it is difficult to get random Ids of the tables by using any logic, we will need to loop to create N ids but the technique we will explore here will not need any looping or any Id creation in temporary table but the very simple ways to achieve our goal
So let's take the very first one
We have function in Sql Server "NEWID()", do you know what it return, let's check it
Select top 10 NEWID() from products
I used to get the 10 rows otherwise we will need to loop, here is the output:
A582542D-44AE-43D4-B34A-0BDFFC9E99B8
B1C4DA4A-1C12-4D2E-B714-255AB3A81B35
40C66BCB-F775-4886-BD20-85B30914C030
FDF74E8D-4234-4C42-887D-DFB18E5D2F4C
A6AEEA9C-5A38-4BBF-9196-D889FC9A780D
8EC5B2AA-28A1-4A64-9FB4-E3BC9E1F340C
6D156522-C3A7-450E-AAB0-36CFC9446E3B
BD5848FB-6D3E-4546-976A-973A04FF726F
E0A6D374-41EC-4A4F-BA88-2C6B26887CEB
843A7209-8348-4E06-92AE-AD1C7BE33EC4
If we can order our records by using this new id will solve our problem, so why we are waiting, let's try to get 10 products Ids by using this logic
Select top (10) [ProductId]
From [Products]
order by NewId()
ProductId
143
108
47
26
121
53
71
205
196
34
So if we can use the above query as sub-query our goal is achieved
Select *
From Products
Where ProductId IN
(Select top (@Rows) [ProductId] from [Products]
where CategoryId = @CategoryId
order by NewId())
Every time it will give 10 random product list, if you can notice we user where clause to get the records of a particular category only.
Enjoy it
|
Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
|
| By Ali Adravi On 03 May, 14 Viewed: 897 |
We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi On 02 May 2013 Viewed: 1,084
There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi On 31 Mar 2013 Viewed: 924
In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty On 26 Mar 2013 Viewed: 3,311
To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D On 25 Feb 2013 Viewed: 666
When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi On 13 Feb 2013 Viewed: 12,556