Selecting one row of each item

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

Selecting one row of each item

Author

Topic

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-11-17 : 08:28:05

Hi Guys,

In the customer table each customer is assigned a special code. There are 16 codes in total and over 1000 customers in the database. I would like to extract 100 random customers which must consist of the 16 codes. The codes must appear at least once in the list.
I am using sql server 2000.

If you look below each cardnumber is unique but multiple persons can have the same cycle code.

Any ideas on how to get this done.



cardnumber  cyclecode
2525251      01
2525252      06
2525253      02  
2525254      03 
2525255      11 
2525256      16 
2525257      15
2525258      82
2525259      56
25252510     07
25252511     12
25252512     19
25252513     42
25252514     23
25252515     53
25252516     18
25252517     11
25252518     09
25252519     03
25252520     04

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-17 : 08:40:54

each cardnumber is unique
Is it ? 2525252 seems to appear twice

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-17 : 08:41:25

select cardnumber,max(cyclecode) as cyclecode from your_table
group by cardnumber

Madhivanan

Failing to plan is Planning to fail

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-11-17 : 08:49:17

yes each cardnumber is unique that is just a typo.

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-11-17 : 08:53:41

hi ya,

All 16 cyclecode must appear in the final list. The below code doesn't show the 16 different cycle codes in the final list


select cardnumber,max(cyclecode) as cyclecode from your_table
group by cardnumber

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-17 : 09:04:01

Is this?

select max(cardnumber) as cardnumber,cyclecode from your_table
group by cyclecode

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-17 : 09:12:43


select cardnumber,max(cyclecode) as cyclecode from your_table
group by cardnumber

union

select cardnumber, cyclecode
from
(
    select top 84 cardnumber, cyclecode
    from   your_table
    order by newid()
) a

KH
[spoiler]Time is always against us[/spoiler]

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-11-17 : 09:15:25

hi madhivanan,

I was thinking that every time I ran the query I would get new customers in the list, not the same one every time. Is this possible in this instance?
Thanks for your help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-17 : 09:21:05

Try this


select distinct
	(select top 1 cardnumber from your_table where cyclecode=t.cyclecode order by newid()) as cardnumber,
	cyclecode  
from your_table

Madhivanan

Failing to plan is Planning to fail

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-11-17 : 09:32:01

Try using a temp table. Something like:


-- use NEWID to make pseudo-random
SELECT cardnumber, cyclecode, CAST(NEWID() as varchar(36)) AS RandomId
INTO #temp
FROM Customers

SELECT cardnumber, cyclecode
FROM #temp T
WHERE EXISTS
(
	SELECT cyclecode, MIN(RandomId) AS RandomId
	FROM #temp T1
	WHERE T1.RandomId = T.RandomId
	GROUP BY cyclecode
)
UNION ALL
SELECT TOP 84 cardnumber, cyclecode
FROM #temp T
WHERE NOT EXISTS
(
	SELECT cyclecode, MIN(RandomId) AS RandomId
	FROM #temp T1
	WHERE T1.RandomId = T.RandomId
	GROUP BY cyclecode
)

Edit: changed the second bit.

velvettiger
Posting Yak Master

115 Posts

Posted - 2009-11-17 : 10:33:45

Hi Ifor,

The above code shows all those customers who has a cyclecode. In total they are 16 unique cycle code in the system so the list should contain all 16 unique cyclecode plus another 84.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-11-17 : 11:27:42

The basic idea, that both Madhivanan and I have posted, is to use NEWID to make the result random.

As you have not posted cogent test data, it is up to you to work out the exact solution.
Maybe an extra derived table in the exists test:


SELECT cardnumber, cyclecode, CAST(NEWID() as varchar(36)) AS RandomId
INTO #temp
FROM Customers

SELECT cardnumber, cyclecode
FROM #temp T
WHERE EXISTS
(
	SELECT *
	FROM
	(
		SELECT cyclecode, MIN(RandomId) AS RandomId
		FROM #temp
		GROUP BY cyclecode
	) D1
	WHERE D1.RandomId = T.RandomId
)
UNION ALL
SELECT TOP 84 cardnumber, cyclecode
FROM #temp T
WHERE NOT EXISTS
(
	SELECT *
	FROM
	(
		SELECT cyclecode, MIN(RandomId) AS RandomId
		FROM #temp
		GROUP BY cyclecode
	) D2
	WHERE D2.RandomId = T.RandomId
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources