Selecting By Type of 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)

Selecting By Type of Item.

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-06 : 15:37:20

I think (hope) this will be my last question for a while:
Say you have a database that picks books for people. The person can tell you the total number of books (varTotalBooks) as well as the max type of each book they want, e.g.:
varTotalBooks=10
varFiction=5
varAutoBiography=2
varWarHistory=2
varSciFi=1
varRomance=2
How could one make a select statement that would select a total of 10 books with a max of a certain num. each as demonstrated above?
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-06 : 15:43:02

It would help to know the table structure

Follow the instruction in the link below...but your gonna need to use TOP

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-06 : 15:51:02

Example Table:
1. CREATE TABLE Books (Title Text, Author Text, Type Int)
2. Command I am trying to create would look something like this:
SELECT TOP 7 * FROM BOOKS WHERE Type=1=varNumofFiction, Type=2=varNumofAutobiography, Type=3=varNumofSciFi
The type is an integer, each number represents a category of book (1=Fiction, 2=Autobiography, 3=Sci-Fi). Thus it sections from the table say 3 books of type 1, 1 books of type 2, and 1 book of type 3.
3. Results would look like:
- Monster, Frank Peretti, 1.
- Visitation, Frank Peretti, 1.
- The Hangman's Curse, Frank Peretti, 1.
- The Autobiography of Benjamin Frank, Benjamin Franklin, 2.
David.
- Twenty Thousand Leagues Under the Sea, Jules Verne, 3.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-06 : 16:11:16

This is the best that I can come up with


USE pubs
GO

DECLARE @business int, @psychology int, @popular_comp int, @sql varchar(8000)
SELECT @business = 1, @popular_comp = 2, @psychology = 3

SELECT @sql = 'SELECT * FROM ('+ CHAR(13) + CHAR(10)
SELECT @sql = @sql + 'SELECT * FROM (SELECT TOP ' 
		   + CONVERT(varchar(15),@business) 
		   + ' * FROM Titles WHERE type = '
		   + '''' + 'business' + '''' + ' ORDER BY NewID()) AS A'
SELECT @sql = @sql + CHAR(13) + CHAR(10) + ' UNION ALL '+ CHAR(13) + CHAR(10)
SELECT @sql = @sql + 'SELECT * FROM (SELECT TOP ' 
		   + CONVERT(varchar(15),@popular_comp) 
		   + ' * FROM Titles WHERE type = '
		   + '''' + 'popular_comp' + '''' + ' ORDER BY NewID()) AS B'
SELECT @sql = @sql + CHAR(13) + CHAR(10) + ' UNION ALL '+ CHAR(13) + CHAR(10)
SELECT @sql = @sql + 'SELECT * FROM (SELECT TOP ' 
		   + CONVERT(varchar(15),@psychology) 
		   + ' * FROM Titles WHERE type = '
		   + '''' + 'psychology' + '''' + ' ORDER BY NewID()) AS C'
SELECT @sql = @sql + CHAR(13) + CHAR(10) + ') AS XXX'

SELECT @sql

EXEC(@sql)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

davidshq
Posting Yak Master

119 Posts

Posted - 2005-06-07 : 11:44:07

Thanks. That works great.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources