Return 2 of each item - 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 2000 Forums

Transact-SQL (2000)

Return 2 of each item

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2004-08-15 : 00:20:39

Hello,

Say I have a table with many watches and we only carry 4 brands. I would like to create a query to return 8 records, 2 of each brand. I would like to query:

Seiko
Seiko
Timex
Timex
Citizen
Citizen
Rolex
Rolex

Always returning two of each record. How can I accomplish this?

Thanks!

gpl
Posting Yak Master

195 Posts

Posted - 2004-08-15 : 05:23:29

It would help if we could see your table structures, can you post the DDL ?

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-15 : 05:51:59

I am wondering why you would need something like this. Just for fun, this works:


SELECT WatchBrand FROM Watches 
CROSS JOIN
(SELECT 1 AS b UNION ALL SELECT 2) a

dcarva
Posting Yak Master

140 Posts

Posted - 2004-08-15 : 10:37:28

Hi,

Thanks for your help. The reason I need this is because a certain customer wants me to display 2 of each watch on his home page in a random fashion. I am able to get them randomly, but not sure what is the best way to get exactly 2 of each. The table schema has a lot of columns. But for the sake of this post, something like this:

id, brand, model
--, -----, -----
1, seiko, S2
2, invicta, I2
3, seiko, SA
4, seiko, S1
5, timex, T5
6, timex, T3
7, timex, T2
8, rolex, r1
8, rolex, r3
8, rolex, r5

I want to return:

seiko SA
seiko S1
timex T5
timex T3
etc...

SqlFriend
Starting Member

26 Posts

Posted - 2004-08-15 : 13:10:19

Are you using ASP or C# or another language for querying the database?

If I were doing it, you want 2 of each type, randomly picked,right?

First, build a recordset containing one of each type, so you have them in a list. For each watchband type:

I would find out how many items are in the entire list next (hopefully the primary key is an integer starting at 1). Pick a random number based on the range of the primary key. Now do a lookup in your list for the first instance with a higher number than this. If it doesn't exist, start at the front of the list.

Now do the same thing- if you find the same record, take the next item, if it exists.

Wouldn't that do the trick?

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-15 : 21:46:51

Hmm, I need to do something like this as well. I was thinking of building a 'while' clause in the sproc and pick two of each item into a temp table something like:


declare x int, MaxX int

set MaxX = 2 --Max to pick from each
set x = 0 --Initialize x

while x < MaxX --This will loop thru till you get the max amount you choose
begin
	--Do the random picking of one item here and checking it it hasn't been picked yet by brand.

	--I haven't worked on this part yet, but maybe it can give you a boost.

	--when you get a good one
	set x = x + 1
end

I currently have my ASP doing the trick, but I rather do it at the SQL level and leave the application to display, not do calculations

- RoLY roLLs

dcarva
Posting Yak Master

140 Posts

Posted - 2004-08-16 : 16:08:25

Thanks to all for your replies. SQLFriend, I am currently doing it the way you suggest, but I was hoping that I could get my results straight from a SQL query. (just wanted to better my query skills) I accomplished my goal with something similar to this:

select top 2 from table1 where brand = 'sekio'
union all
select top 2 from table1 where brand = 'citizen'

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-16 : 16:42:26

dcarva, I don't thinl that gives you random records each time you run the query, or do you have othe code hidden in there?

- RoLY roLLs

dcarva
Posting Yak Master

140 Posts

Posted - 2004-08-16 : 16:45:54

Sorry, I am not near my work machine, so I didn't post the whole code. But I do something like:

ORDER BY NEWID()

...to get a random record.

THANKS!!!

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-16 : 18:47:29

[code]
--I hesitate to suggest this but I suspect it is faster than a loop, so here it is for your consideration.

--Some sample data

SET NOCOUNT ON
create table watches (id int, brand varchar(20), model char(2))
insert watches select 1, 'seiko', 'S2'
insert watches select 2, 'invicta', 'I2'
insert watches select 3, 'seiko', 'SA'
insert watches select 4, 'seiko', 'S1'
insert watches select 5, 'timex', 'T5'
insert watches select 6, 'timex', 'T3'
insert watches select 7, 'timex', 'T2'
insert watches select 8, 'rolex', 'r1'
insert watches select 8, 'rolex', 'r3'
insert watches select 8, 'rolex', 'r5'

--Need a way to randomize in a function. Cant use NEWID() so create a view to workaround that restriction
CREATE VIEW v_newid AS SELECT NEWID() vnewid

--Function returns 2 models in random order for selected watch brand.
CREATE FUNCTION f_gettwowatches (@brand varchar(20))
RETURNS varchar(31) --big enough for two models?
AS
BEGIN

DECLARE @model1 char(15), @model2 char(15)

SELECT TOP 1 @model1 = model,
@model2 = (SELECT TOP 1 model
FROM watches, v_newid
WHERE brand = o.brand and model <> o.model
ORDER BY vnewid)
FROM watches o, v_newid
WHERE brand = @brand
ORDER BY vnewid

--Return two models seperated by |
RETURN Rtrim(ISNull(@model1,''))+'|'+Rtrim(ISNull(@model2,''))
END

-- Select models and brand pairs using function above. Extract models seperated by |
-- NOTE: NEWID() below is there for a reason. Can anyone guess what it is? I suspect I have broken several SQL Laws here.

SELECT *
FROM (
SELECT brand,
Case bid when 2 THEN
substring ( models , charindex('|',models) + 1, 100 ) ELSE
substring ( models , 1, charindex('|',models) - 1)
END model
FROM ( SELECT 1 bid UNION SELECT 2 ) y,
(
SELECT brand, dbo.f_gettwowatches (brand) + Left(NEWID(),0) models
FROM watches
GROUP BY brand
) x
) z
WHERE model <> ''
ORDER BY brand

brand model
-------------------- -----------
invicta I2
rolex r1
rolex r3
seiko S2
seiko SA
timex T5
timex T3
[/code]

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-16 : 19:47:49

dcarva, i was going to suggest that. that's what i use to select random rows

Ken: you're too smart for me to know what that NEWID() is there for, I just use ORDER BY NEWID()

- RoLY roLLs

dcarva
Posting Yak Master

140 Posts

Posted - 2004-08-17 : 00:00:22

All thanks for your help!!! I'll try it.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-17 : 05:42:50

I had a stab at this too. Seems to be returning the right results, but you need to tell us how it works with real data. Just replace the @watches bit with your real table. You'll need the other table variable though.


declare @watches table (id int, brand varchar(20), model char(2))
insert @watches select 1, 'seiko', 'S2'
insert @watches select 2, 'invicta', 'I2'
insert @watches select 3, 'seiko', 'SA'
insert @watches select 4, 'seiko', 'S1'
insert @watches select 5, 'timex', 'T5'
insert @watches select 6, 'timex', 'T3'
insert @watches select 7, 'timex', 'T2'
insert @watches select 8, 'rolex', 'r1'
insert @watches select 9, 'rolex', 'r3'
insert @watches select 10, 'rolex', 'r5'

declare @random table (randid int identity(1,1), id int, brand varchar(20), model char(2))
insert into @random (id, brand, model)
	select * from @watches order by newid()

select brand, model from 
(
	select *,
		(select count(*) from @random b where a.brand = b.brand and a.randid >= b.randid) as counter  
		from @random a
) subq 
where counter < 3
order by brand, counter

Not sure if this faster or better than Ken's solution, but that one seemed a bit scary to me!

OS

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-08-17 : 13:08:25

That is a better solution OS :) I started down that track but stopped when I realized dcarva had duplicated id 8 3 times. I suspected that was a typo but I wanted to do it in 1 select. I was going to explain why adding '+ Left(NEWID(),0)', (which appears to do nothing), to my SELECT made the thing work, but after a few more tests, I can't explain why it makes it work, but it does. NEWID() is one of the few non-deterministic built-in functions and it seemed that SQL recognized that, and avoided executing the f_gettwowatches() function multiple times for a row because it was tied to NEWID(). Trying to prove that turned out to be difficult because that behaviour is not consistent. Adding it probably just changed the cost of the subquery and I just got lucky.

--Ken
"Knowledge is a process of piling up facts; wisdom lies in their simplification."

dcarva
Posting Yak Master

140 Posts

Posted - 2004-08-17 : 23:45:21

mohdowais,

your example works great! Thanks to all for your replies. I have learned a lot from this post. Hopefully, I can pass on the knowledge.

Danny

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-18 : 01:13:27

I can't even pretend I knew what the NEWID() was doing there in the first place. So that's the secret

. And yes, it is strange that it works. Guess you could have used another derived table or a table variable to get DISTINCT brands, that might have been more readable than this. I like your spirit, I certainly wasn't going through so much effort for what looked like a typo!

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources