How to Not Return a Record with Matching Field? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to Not Return a Record with Matching Field?

Author

Topic

abnc
Starting Member

3 Posts

Posted - 2006-10-16 : 22:54:55

I am at a mental block and need to fine tune this select statement. In addition to the filters set, I need to also make sure that the 10 records that are returned do not include more than one record with a matching album field.

The songlist table has an album field, and I do not want the 10 records to include more than one record of a matching album field. Right now the query sometimes returns records with the same album...sometimes 3 or more matching records.

Any suggestions on how to accomplish this?

SELECT     TOP 10 songlist.*
FROM         songlist INNER JOIN
                      categorylist ON songlist.id = categorylist.songid JOIN
                      category ON categorylist.categoryid = category.id

-- do not choose any songs that have been played within specified times
WHERE     (date_played < DATEADD(MINUTE, - 360, GETDATE()))
AND       (date_title_played < DATEADD(MINUTE, - 120, GETDATE()))
AND       (date_artist_played < DATEADD(MINUTE, - 10, GETDATE()))
AND       (date_album_played < DATEADD(MINUTE, - 10, GETDATE()))

-- only grab records from specified category
AND       (category.name = 'Tracks')

--select random records
ORDER BY   NEWID()

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-17 : 01:44:22

does it matter which record it picks from the album filter?
if yes, try to group the records according to album name then

but I guess your list should not be album biased noh, the top ten may be ten records from the same album?

--------------------
keeping it simple...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 02:42:31

It seems to me you need the TOP 1 within each grouping for album...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 02:53:52

Do you have some sample data?

Peter Larsson
Helsingborg, Sweden

abnc
Starting Member

3 Posts

Posted - 2006-10-17 : 08:53:47

Here is a result set from running the query:

title                                   artist                                            album
Joyful, Joyful, We Adore Thee           Choir of St. Marks Church North Audley Street     Be Still My Soul
Crown Him with Many Crowns              Choir of King's College                           Be Still My Soul
O God, Our Help in Ages Past            Choir of King's College                           Holy, Holy, Holy
Tell It Again                           Gordon and Jean Greer                             My Saviour First of All
Brethren, We Have Met To Worship        Christopher Parkening                             Simple Gifts
Praise to the Holiest in the Height     Choir of King's College                           Holy, Holy, Holy
Praise to the Lord                      St. Olaf Choir                                    Great Hymns of Faith
Michael, Row the Boat Ashore            FCA Children's Chorus                             Keep Me, Lord
Onward Christian Soldiers               Huddersfield Choral Societ                        Be Still My Soul
Into My Heart                           FCA Children's Chorus                             Keep Me, Lord

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-10-17 : 13:03:52

Try following the first link in my signature, and reposting your question.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

abnc
Starting Member

3 Posts

Posted - 2006-10-17 : 15:20:33

How do I return unique album and artist for each record?

The DDL for the tables are:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[categorylist]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[categorylist](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[songID] [int] NOT NULL DEFAULT ((0)),
	[categoryID] [int] NOT NULL DEFAULT ((0)),
	[sortID] [real] NOT NULL DEFAULT ((0))
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[categorylist]') AND name = N'IX_categorylist_categoryID')
CREATE NONCLUSTERED INDEX [IX_categorylist_categoryID] ON [dbo].[categorylist] 
(
	[categoryID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[categorylist]') AND name = N'IX_categorylist_songID')
CREATE NONCLUSTERED INDEX [IX_categorylist_songID] ON [dbo].[categorylist] 
(
	[songID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[songlist]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[songlist](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[filename] [varchar](255) NOT NULL DEFAULT (''),
	[diskID] [int] NOT NULL DEFAULT ((0)),
	[flags] [varchar](10) NOT NULL DEFAULT ('YYYYYY'),
	[songtype] [varchar](1) NOT NULL DEFAULT ('S'),
	[status] [smallint] NOT NULL DEFAULT ((0)),
	[weight] [real] NOT NULL DEFAULT ((0)),
	[balance] [real] NOT NULL DEFAULT ((0)),
	[date_added] [datetime] NULL,
	[date_played] [datetime] NULL,
	[date_artist_played] [datetime] NULL,
	[date_album_played] [datetime] NULL,
	[date_title_played] [datetime] NULL,
	[duration] [int] NOT NULL,
	[artist] [varchar](255) NOT NULL DEFAULT (''),
	[title] [varchar](255) NOT NULL DEFAULT (''),
	[album] [varchar](255) NOT NULL DEFAULT (''),
	[label] [varchar](255) NOT NULL DEFAULT (''),
	[pline] [varchar](50) NOT NULL DEFAULT (''),
	[trackno] [smallint] NOT NULL DEFAULT ((0)),
	[composer] [varchar](100) NOT NULL DEFAULT (''),
	[ISRC] [varchar](50) NOT NULL DEFAULT (''),
	[catalog] [varchar](50) NOT NULL DEFAULT (''),
	[UPC] [varchar](50) NOT NULL DEFAULT (''),
	[feeagency] [varchar](20) NOT NULL DEFAULT (''),
	[albumyear] [varchar](4) NOT NULL DEFAULT (''),
	[genre] [varchar](20) NOT NULL DEFAULT (''),
	[website] [varchar](255) NOT NULL DEFAULT (''),
	[buycd] [varchar](255) NOT NULL DEFAULT (''),
	[info] [text] NULL,
	[lyrics] [text] NULL,
	[picture] [varchar](255) NOT NULL DEFAULT (''),
	[count_played] [int] NOT NULL DEFAULT ((0)),
	[count_requested] [int] NOT NULL DEFAULT ((0)),
	[last_requested] [datetime] NULL,
	[count_performances] [int] NOT NULL DEFAULT ((0)),
	[xfade] [varchar](50) NOT NULL DEFAULT (''),
	[bpm] [int] NOT NULL DEFAULT ((0)),
	[mood] [varchar](50) NOT NULL DEFAULT (''),
	[rating] [int] NOT NULL DEFAULT ((0)),
	[overlay] [varchar](3) NOT NULL DEFAULT ('no'),
	[playlimit_count] [int] NOT NULL DEFAULT ((0)),
	[playlimit_action] [varchar](6) NOT NULL DEFAULT ('none'),
	[songrights] [varchar](40) NOT NULL DEFAULT ('broadcast'),
 CONSTRAINT [IX_songlist_filename] UNIQUE NONCLUSTERED 
(
	[filename] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[songlist]') AND name = N'IX_songlist_album')
CREATE NONCLUSTERED INDEX [IX_songlist_album] ON [dbo].[songlist] 
(
	[album] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[songlist]') AND name = N'IX_songlist_artist')
CREATE NONCLUSTERED INDEX [IX_songlist_artist] ON [dbo].[songlist] 
(
	[artist] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[songlist]') AND name = N'IX_songlist_date_album_played')
CREATE NONCLUSTERED INDEX [IX_songlist_date_album_played] ON [dbo].[songlist] 
(
	[date_album_played] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[songlist]') AND name = N'IX_songlist_date_artist_played')
CREATE NONCLUSTERED INDEX [IX_songlist_date_artist_played] ON [dbo].[songlist] 
(
	[date_artist_played] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[songlist]') AND name = N'IX_songlist_date_played')
CREATE NONCLUSTERED INDEX [IX_songlist_date_played] ON [dbo].[songlist] 
(
	[date_played] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[category]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[category](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[name] [varchar](100) NOT NULL DEFAULT (''),
	[parentID] [int] NOT NULL DEFAULT ((0)),
	[levelindex] [smallint] NOT NULL DEFAULT ((0)),
	[itemindex] [int] NOT NULL DEFAULT ((0))
) ON [PRIMARY]
END

My current DML has been posted above. Right now it gives me 10 records that sometimes includes more than one song with the same album and artist. What I need is ten records that will not return more than one song from any given album or artist. This limitation must be in addition to the filters already specified by the DML I have used in the select statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources