Select some rows and more if necesary...

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

Select some rows and more if necesary...

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2011-11-22 : 08:50:53

I need to retrieve randomly 4 products from a table, let's call it tblProducts, where I am storing products some are "Special Offer" and some are not. The trick is if I do not have 4 products with I need to retrieve the difference from products not in offer.
I tried to go with Union but it didn't work.

What I am execting is to retrieve something like shown below, I should ALWAY retrieve "Product 1", "Product 6" and "Product 7" as they are the only ones in offer and one other product to complete the 4 products I need.

"Product 6"
"Product 2"
"Product 1"
"Product 7"

The next run
"Product 7"
"Product 1"
"Product 3"
"Product 6"

Or the next
"Product 10"
"Product 7"
"Product 6"
"Product 1"

Or the next
"Product 1"
"Product 11"
"Product 6"
"Product 7"

Etc...


CREATE TABLE [dbo].[tblProducts](
	[P_Id] [int] IDENTITY(1,1) NOT NULL,
	[P_Special] [int] NOT NULL,
	[P_Text] [nvarchar](50) NOT NULL
) ON [PRIMARY]

INSERT INTO [tblProducts]([P_Special],[P_Text]) VALUES  (1, 'Product 1')
INSERT INTO [tblProducts]([P_Special],[P_Text]) VALUES  (0, 'Product 2')
INSERT INTO [tblProducts]([P_Special],[P_Text]) VALUES  (0, 'Product 3')
INSERT INTO [tblProducts]([P_Special],[P_Text]) VALUES  (0, 'Product 4')
INSERT INTO [tblProducts]([P_Special],[P_Text]) VALUES  (0, 'Product 5')
INSERT INTO [tblProducts]([P_Special],[P_Text]) VALUES  (1, 'Product 6')
INSERT INTO [tblProducts]([P_Special],[P_Text]) VALUES  (1, 'Product 7')
INSERT INTO [tblProducts]([P_Special],[P_Text]) VALUES  (0, 'Product 8')
INSERT INTO [tblProducts]([P_Special],[P_Text]) VALUES  (0, 'Product 9')
INSERT INTO [tblProducts]([P_Special],[P_Text]) VALUES  (0, 'Product 10')
INSERT INTO [tblProducts]([P_Special],[P_Text]) VALUES  (0, 'Product 11')

jean-luc
www.corobori.com

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-22 : 09:00:32

You want to retrieve different extra products for each run? That means either a run number which you update or keep a record of what has been retrieved previousl - in any case it means keeping information about the previous run

select top 4 *
tblProducts
order by P_Special desc

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Corobori
Posting Yak Master

105 Posts

Posted - 2011-11-22 : 10:48:11

Well, no I don't want to retrieve different products between runs I just want them to be on a random order

I could go this way


SELECT     TOP (4) P_Id, P_Special, P_Text
FROM         dbo.tblProducts
ORDER BY P_Special DESC, NEWID()

But by doing so I'll always will show first the special offer then other products whereas I want them to be mixed.

Running the statement shown above I am getting:


1	Product 1
1	Product 6
1	Product 7
0	Product 8

or 

1	Product 7
1	Product 1
1	Product 6
0	Product 10

But it will never give me this


1	Product 1
0	Product 8
1	Product 6
1	Product 7

jean-luc
www.corobori.com

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-22 : 10:51:29

select *
from
(
select top 4 *
tblProducts
order by P_Special desc, newid()
) a
order by newid()

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources