Sample Record Problem - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Sample Record Problem

Author

Topic

IainT
Starting Member

12 Posts

Posted - 2009-09-25 : 07:12:50

Hey guys,

I have a problem which feels like it must be fairly common, but I can't find a solution anywhere. I suspect this may be because I don't know what to search for, so apologies if this has already been answered.

Taking this as a sample table:

ID Value
1 Green
2 Blue
3 Green
4 Red
5 Red
6 Green

I need a query which selects distinct "Value" and a random ID that corresponds to it.

So this would be an acceptable data set:

SampleID Value
6 Green
2 Blue
4 Red

As would this:

SampleID Value
1 Green
2 Blue
5 Red

The only way I can think of to do this, without writing a separate query for each type and doing a union, or using a cursor, or anything similarly complicated, would be:

SELECT MAX(ID), Value FROM SampleTable GROUP BY Value

In reality I'm dealing with extremely large tables though, so finding the maximum ID every time is hugely inefficient. I really just want the first ID that SQL Server comes across matching up to each distinct Value.

I'm working in SQL Server 2005, so I can use the ranking functions if they would be any help, though I can't see a way they would.

Can anyone think of anything more efficient?

Thanks
Iain

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-25 : 07:31:14

Can you post the actual execution plan for the query?

N 56°04'39.26"
E 12°55'05.63"

nalnait
Starting Member

14 Posts

Posted - 2009-09-25 : 07:43:56

CREATE INDEX...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-25 : 08:03:53

Create index for what?
We don't know if there already are present indexes, nor how they are used.
Iain's best choice is to store the actual execution plan somewhere we can download it for investigation.

N 56°04'39.26"
E 12°55'05.63"

IainT
Starting Member

12 Posts

Posted - 2009-09-25 : 10:16:04

Hey guys,

I don't have an actual execution plan sadly - currently the query takes a longtime to run, and unfortunately IT are taking the servers down in a couple of hours for maintenance, so I can't even start it and leave it running.

I was hoping that there was a way to do it in SQL which I was just missing. I guess I was hoping there would be an aggregate function which didn't actually take an aggregate, but rather just selected a random record. Something like:

SELECT RANDOM(ID), Value FROM SampleTable GROUP BY Value

Or maybe a subquery like:

SELECT
st.Value,
(SELECT TOP 1 ID FROM SampleTable WHERE SampleTable.Value = st.Value) SampleID
FROM
(SELECT DISTINCT Value FROM SampleTable) st

Something like that, but which actually worked.

I guess it was a long shot that there would be an aggregate function that didn't actually produce an aggregate :)

Thanks
Iain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources