Select DISTINCT problem - 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)

Select DISTINCT problem

Author

Topic

junto
Starting Member

7 Posts

Posted - 2004-03-23 : 10:27:36

I'm having real problems getting my head round this T_SQL issue, having been forced to use Oracle for the last 6 months!

We have a LeaderBoard with various people scoring points. The same person can enter more than once, but their highest score is the one that counts. I need to limit the list to the top 50 scores, removing duplicate people first.

Table looks as below:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[leaderboard]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[leaderboard]
GO

CREATE TABLE [dbo].[leaderboard] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[date_cr] [smalldatetime] NOT NULL ,
[name] [nvarchar] (50) NOT NULL ,
[handicap] [int] NOT NULL ,
[score] [int] NOT NULL ,
[total] [int] NOT NULL ,
[visible] [bit] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[leaderboard] WITH NOCHECK ADD
CONSTRAINT [PK_leaderboard] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[leaderboard] WITH NOCHECK ADD
CONSTRAINT [DF_leaderboard_date_cr] DEFAULT (getdate()) FOR [date_cr]
GO

INSERT INTO LeaderBoard
([Name], Handicap, Score, Total, Visible)
VALUES
('John Smith', 10, 10, 103, 1)

INSERT INTO LeaderBoard
([Name], Handicap, Score, Total, Visible)
VALUES
('Bob Smith', 10, 10, 106, 1)

INSERT INTO LeaderBoard
([Name], Handicap, Score, Total, Visible)
VALUES
('Paul Jones', 10, 10, 98, 1)

INSERT INTO LeaderBoard
([Name], Handicap, Score, Total, Visible)
VALUES
('Paul Jones', 10, 10, 98, 0)

INSERT INTO LeaderBoard
([Name], Handicap, Score, Total, Visible)
VALUES
('John Smith', 10, 10, 98, 1)

INSERT INTO LeaderBoard
([Name], Handicap, Score, Total, Visible)
VALUES
('Mark Evans', 10, 10, 100, 1)

INSERT INTO LeaderBoard
([Name], Handicap, Score, Total, Visible)
VALUES
('Mark Jones', 10, 10, 112, 1)

INSERT INTO LeaderBoard
([Name], Handicap, Score, Total, Visible)
VALUES
('Mark Jones', 10, 10, 100, 1)

The visible option allows an administration to hide that result from the final display, rather than delete it from the database.

So, in summary:

I need a list of distinct competition entrants, ordered by the [Total] value and if an entrant has more than one entry row then the highest score is taken and the rest discarded for that entrant.

Any help much appreciated! I started off using an INNER JOIN to find duplicates a.[Name] = b.[Name], but it didn't really go anywhere. If anyone asks you to forget the 5 years of T-SQL and switch over to PL/SQL for a "little while", then tell them where to go!! I now can't remember anything from either syntax!

Regards,

Ben

junto
Starting Member

7 Posts

Posted - 2004-03-23 : 10:38:07

I have got this far, which removes most duplicates, but not those which have the same score! Hence, INSERT an exact dupe (Name and Total):

INSERT INTO LeaderBoard
([Name], Handicap, Score, Total, Visible)
VALUES
('Mark Jones', 10, 10, 112, 1)

Then, select:
SELECT [id], [Name], Total
FROM LeaderBoard

and you get:
id Name Total
----------- -------------------------------------------------- -----------
1 John Smith 103
2 Bob Smith 106
3 Paul Jones 98
4 Paul Jones 98
5 John Smith 98
6 Mark Evans 100
7 Mark Jones 112
8 Mark Jones 100
9 Mark Jones 112

(9 row(s) affected)

This query:
SELECT [id], [Name], Total
FROM LeaderBoard
WHERE id NOT IN (SELECT A.id FROM LeaderBoard A INNER JOIN LeaderBoard B ON (A.Name = B.Name)
WHERE A.Total < B.Total)
AND visible = 1
ORDER BY Total Desc

Limits to this:
id Name Total
----------- -------------------------------------------------- -----------
7 Mark Jones 112
9 Mark Jones 112
2 Bob Smith 106
1 John Smith 103
6 Mark Evans 100
3 Paul Jones 98

(6 row(s) affected)

But the duplicate Mark Jones is not removed.

Aaaaargh!! Help me........ ;-)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-23 : 11:07:27

Are you trying to:

SELECT
MAX(lb1.id) AS id,
lb1.Name AS Name,
lb1.Total AS Total
FROM
LeaderBoard lb1
(SELECT Name, MAX(Total) AS Total
FROM LeaderBoard
WHERE visible = 1) lb2 ON lb1.Name = lb2.Name
AND lb1.Total = lb2.Total

I think there's a flaw in your design though. What if you have two people on your Leaderboard with the same name? You really do need to look at the id, because I'm assuming it's uniquely identifying two different people with the same name. Correct?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

junto
Starting Member

7 Posts

Posted - 2004-03-23 : 11:07:57

Hi Duane,

The problem with:
SELECT [id], [Name], MAX(Total) as Total
FROM LeaderBoard
WHERE Visible = 1
GROUP BY [id], [Name]
ORDER BY Total DESC

Is that the resulting set:
id Name Total
----------- -------------------------------------------------- -----------
7 Mark Jones 112
9 Mark Jones 112
2 Bob Smith 106
1 John Smith 103
8 Mark Jones 100
6 Mark Evans 100
3 Paul Jones 98
5 John Smith 98

(8 row(s) affected)

Still shows all three entires for Mark Jones and both entries for John Smith. The desired output I am after should be:

id Name Total
----------- -------------------------------------------------- -----------
7 Mark Jones 112
2 Bob Smith 106
1 John Smith 103
6 Mark Evans 100
3 Paul Jones 98

(8 row(s) affected)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-23 : 11:10:05

Sorry,
I realised it would - so I deleted my post.
Sorry about that!

Duane.

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-03-23 : 11:12:24

It wont work as the ID is are different and you have included the id in the group by clause.

EDIT:
Opps.. I hope derrick has given the solution

- Sekar

junto
Starting Member

7 Posts

Posted - 2004-03-23 : 11:13:24

Hi Derrick,

You are spot on and the original design is the issue, but unfortunately someone else designed it, and then they asked me if it was possible to do this!

The rule of thumb is: If a name exists twice then only include the highest Total only, and exclude the rest by that name from the results.

Therefore, based on your comment:

"I think there's a flaw in your design though. What if you have two people on your Leaderboard with the same name? You really do need to look at the id, because I'm assuming it's uniquely identifying two different people with the same name. Correct?"

It is essentially supposed to be the same person, so there is no ID that uniquely identifies a person.

It's all fun!

junto
Starting Member

7 Posts

Posted - 2004-03-23 : 11:21:18

Hi Sekar,

Similar problem with that too. It doesn't remove the lower duplicate name rows:

id name Total
----------- -------------------------------------------------- -----------
2 Bob Smith 106
1 John Smith 103
6 Mark Evans 100
7 Mark Jones 112
9 Mark Jones 112
3 Paul Jones 98
4 Paul Jones 98

(7 row(s) affected)

If you could "DISTINCT-ly" reference this like this, the concept is something like:

SELECT
[id],
[Name],
(SELECT MAX(Total) FROM LeaderBoard B WHERE A.[Name] = B.[Name]) AS Total
FROM
LeaderBoard A
ORDER BY
Total DESC

But with [Name] as DISTINCT. It is such a pity there isn't DISTINCT for a column, as well as a ROW!

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-23 : 11:39:59

Not sure from thread if you have a solution, but if not, would this help

SELECT	lb1.Name AS Name
	, lb1.Total AS Total
FROM	LeaderBoard lb1 JOIN
	(
	SELECT	ID, MAX(Total) AS Total
	FROM	LeaderBoard
	WHERE	visible = 1
	GROUP BY ID
	) lb2 
	 ON lb1.ID = lb2.ID
	 AND lb1.Total = lb2.Total
ORDER BY lb1.Total Desc

Brain hiccup will rework.

junto
Starting Member

7 Posts

Posted - 2004-03-23 : 11:44:26

Hi Drymchaser,

No still no solution that works. Yours still has the duplicates too:

Name Total
-------------------------------------------------- -----------
Mark Jones 112
Mark Jones 112
Bob Smith 106
John Smith 103
Mark Jones 100
Mark Evans 100
Paul Jones 98
John Smith 98

(8 row(s) affected)

The desired output I am after should be:

id Name Total
----------- -------------------------------------------------- -----------
7 Mark Jones 112
2 Bob Smith 106
1 John Smith 103
6 Mark Evans 100
3 Paul Jones 98

Regards,

Ben

junto
Starting Member

7 Posts

Posted - 2004-03-23 : 11:55:25

I think I have solved it. It is a complete mess, but works:

SELECT TOP 50
[id],
[Name],
(SELECT TOP 1 date_cr FROM LeaderBoard B WHERE A.[Name] = B.[Name] AND visible = 1 AND Total = (SELECT MAX(Total) FROM LeaderBoard B WHERE A.[Name] = B.[Name] AND visible = 1)) AS Date_cr,
Handicap,
(SELECT MAX(Score) FROM LeaderBoard B WHERE A.[Name] = B.[Name] AND visible = 1) AS Score,
(SELECT MAX(Total) FROM LeaderBoard B WHERE A.[Name] = B.[Name] AND visible = 1) AS Total,
visible
FROM
LeaderBoard A
WHERE id NOT IN
(
SELECT
D.id
FROM
LeaderBoard D
INNER JOIN
LeaderBoard E
ON
(D.Name = E.Name)
WHERE
D.id < E.id
)
AND
visible = 1
ORDER BY
Total DESC

Any thoughts on how to simplify, greatly appreciated!! Plus, thanks to all who gave me ideas along the way.

Regards,

Ben

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-23 : 11:55:55

samsekar,

Did that SELECT I gave you work? That should have taken care of both the duplicate IDs, and the highest score where visible = 1.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-03-23 : 11:59:34

Unless there is other data. Using your DDL/DML I got this query to work, but the name thing is troubling because there is no way to uniquely identify a person.


SELECT	a.ID, a.Name, a.Total 
FROM	leaderboard a JOIN 
	(
	SELECT	name, max(total) 'total'
	FROM	leaderboard 
	WHERE	visible = 1
	GROUP BY name
	) b 
	   ON a.name = b.name 
	   AND a.total = b.total
WHERE	a.visible = 1
ORDER BY a.total DESC

Results:

ID          Name            Total       
----------- --------------- ----------- 
7           Mark Jones      112
2           Bob Smith       106
1           John Smith      103
6           Mark Evans      100
3           Paul Jones      98

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources