How to select only one value randamly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to select only one value randamly

Author

Topic

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2010-12-28 : 05:12:30

I want to select a randam value (for a specific field).
for example:
100003 Tedlie, Beth 8-Complete 2009-06-30 17:00:00.000
100003 Tedlie, Beth A 8-Complete 2009-06-30 17:00:00.000
100007 Sankaranarayanan, Vaidy; Shandon 8-Complete 2009-09-16 17:00:00.000
100007 Sankaranarayanan, Vaidyanath 8-Complete 2009-09-16 17:00:00.000

from here i want only 1 record each for 100003 and 100007

kalyan Ashis Dey

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 05:24:07

What do you mean randomly?
What should be the o/p for the data you posted?

PBUH

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2010-12-28 : 05:29:26

O/p will be
100003 Tedlie, Beth 8-Complete 2009-06-30 17:00:00.000
100007 Sankaranarayanan, Vaidy; Shandon 8-Complete 2009-09-16 17:00:00.000

kalyan Ashis Dey

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 05:31:52

quote:
Originally posted by kalyan.cse05

O/p will be
100003 Tedlie, Beth 8-Complete 2009-06-30 17:00:00.000
100007 Sankaranarayanan, Vaidy; Shandon 8-Complete 2009-09-16 17:00:00.000

kalyan Ashis Dey

On what basis ?

PBUH

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2010-12-28 : 05:35:45

i need the first row from each trackingnumber
trackingnumber,managername,status,finishdate
100003 Tedlie, Beth 8-Complete 2009-06-30 17:00:00.000
100007 Sankaranarayanan, Vaidy; Shandon 8-Complete 2009-09-16 17:00:00.000

kalyan Ashis Dey

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 05:41:30

quote:
Originally posted by kalyan.cse05

i need the first row from each trackingnumber
trackingnumber,managername,status,finishdate
100003 Tedlie, Beth 8-Complete 2009-06-30 17:00:00.000
100007 Sankaranarayanan, Vaidy; Shandon 8-Complete 2009-09-16 17:00:00.000

kalyan Ashis Dey

Then how do you define your first row ?

Visually anyone can say
100003 Tedlie, Beth 8-Complete 2009-06-30 17:00:00.000

is the first row but we need to make SQL understand that it is indeed the first row.

PBUH

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2010-12-28 : 05:44:28

i mean to say i need any of the row not first or not second ,just i need no duplicate row coming

kalyan Ashis Dey

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 05:46:20

Then is it ok if on the next execution
100003 Tedlie, Beth A 8-Complete 2009-06-30 17:00:00.000 is shown in the resultset ?

PBUH

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2010-12-28 : 05:48:40

yes:)

kalyan Ashis Dey

Sachin.Nand

2937 Posts

Posted - 2010-12-28 : 05:57:28

select T.* from yourtable t1
cross apply(select top 1 * from yourtable t2 where t1.id=t2.id)T

PBUH

matty
Posting Yak Master

161 Posts

Posted - 2010-12-28 : 06:21:24

[code]
SELECT trackingnumber,managername,status,finishdate
FROM
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY trackingnumber ORDER BY newid()) AS rownum
FROM MyTable
)t
WHERE t.rownum = 1
[/code]

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2010-12-28 : 06:39:15

Thanks a lot.It works perfectly

kalyan Ashis Dey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources