--- title: "How do I select random rows from a database — With a twist?" description: "How do I select random rows from a database — With a twist?" author: "Anonymous User" published: 2013-05-07 updated: 2013-05-07 canonical: https://www.mindstick.com/forum/828/how-do-i-select-random-rows-from-a-database-with-a-twist category: "mssql server" tags: ["mssql server"] reading_time: 2 minutes --- # How do I select random rows from a database — With a twist? Hi Everyone!\ Assume (highly anonymized):\ [Create Table](https://www.mindstick.com/articles/443/how-to-create-table-in-sql-server) myTable(ID INT PK,INDEXNUMBER INT,[VERSION](https://www.mindstick.com/articles/12845/things-to-remember-while-migrating-odoo-to-a-better-version) INT,Data [VARCHAR](https://www.mindstick.com/forum/161878/what-is-the-difference-between-nvarchar-and-varchar)(MAX))This table is used to store mutually exclusive data. For example:\ 100 1 1 BOB217 1 2 JOHN319 1 3 GEORGE420 7 1 MARY415 7 2 SUSANIn this case, I need to randomly pick ONE of BOB, JOHN or GEORGE and ONE of MARY or SUSAN.\ I'm happy with either the ID or the INDEXNUMBER/VERSION pair.\ If it helps to think about it, it's like picking a single shift of a [hockey team](https://answers.mindstick.com/qa/100752/how-many-players-on-a-hockey-team) from a table containing a [roster](https://answers.mindstick.com/qa/37339/how-does-a-player-in-the-forty-man-roster-pay-vary-before-he-becomes-a-free-agent):\ Pick 1 Center from 3 available, Pick 1 Left Wing from 5 available, etc.\ I've been playing with NEWID() and MAX/MIN (Cast NEWID to varchar first) but I keep getting hung up on the GROUP BY. If I GROUP BY ID, then max is operating on a \ [single row](https://www.mindstick.com/forum/12960/how-do-i-get-a-single-row-from-a-linq-expression-in-c-sharp) at a time, yielding the entire table.\ If I GROUP BY INDEXNUMBER, VERSION I get a similar [result](https://www.mindstick.com/blog/12011/advantages-of-getting-result-oriented-seo-from-an-agency) (The pair being [unique](https://www.mindstick.com/blog/12870/how-to-be-unique-in-business)).\ What I need to do is GROUP BY INDEXNUMBER (excluding ID from the query entirely) yet somehow [retrieve](https://www.mindstick.com/forum/34400/how-to-retrieve-form-values-in-controller-action) the VERSION.\ Thanks in [advance](https://www.mindstick.com/blog/33258/jee-mains-and-jee-advance-exams)! ## Replies ### Reply by AVADHESH PATEL Hi Ankit!\ Partition by the INDEXNUMBER (I'm assuming that you need one from each though it's not specifically stated) and order by NEWID()\ SELECT IDFROM ( SELECT Row_Number() OVER (PARTITION BY INDEXNUMBER ORDER BY NEWID()) Sort, ID, Data FROM myTable) sWHERE Sort = 1 --- Original Source: https://www.mindstick.com/forum/828/how-do-i-select-random-rows-from-a-database-with-a-twist Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.