How to select rows based on duplicates in one column

[chromis]

Hi,

I'm trying to display a table of highscores, but I don't want to display more than one highscore from the same person aka duplicates in the name column.

I tryed this:

$sql = "SELECT DISTINCT name FROM scores INNER JOIN time, milliseconds FROM scores ORDER BY time, milliseconds ASC LIMIT 10;";

I was thinking maybe I need to first select the names from the table using DISTINCT and then INNER JOIN the table based on the ids in the first select somehow removing the name column in the INNER JOIN?

Thanks,

Chromis

[coolsti]

Take a look at my reply to this thread:

http://bytes.com/forum/thread819577.html

You need the same idea as here.

[chromis]

Hi coolsti,

Thanks for the link, I've had look at it and come up with the following:

SELECT scores.name, scores.time, scores.milliseconds FROM scores INNER JOIN (SELECT DISTINCT T2.name FROM scores T2);
 

So am I right in thinking (from what you said in that thread) the first SELECT statement selects the columns that I want to return and the second sub-query selects the rows to return from that table?
Along this line of thought, I've put DISTINCT in the sub query to filter out the duplicate names.
It throws this error:

#1248 - Every derived table must have its own alias
 

I thought the FROM scores T2 is the alias definition?
I've tryed writing it this way as well:

SELECT scores.id, scores.name, scores.time, scores.milliseconds FROM scores INNER JOIN (SELECT DISTINCT T2.name FROM scores T2) T2 ON T2.id = scores.id;
 

But that throws this error:

#1054 - Unknown column 'T2.id' in 'on clause' 
 

This is because I didn't put the T2.id in the sub-query, but the trouble is I don't want to return results based on the DISTINCT id, i want to return results just based on DISTINCT name. Is the solution in using GROUP BY? What do I need to INNER JOIN ON by?

I've read the entire thread but I am still a little confused about the solution for this. What am I doing wrong?

Thanks again.

[BHTanna]

Try following Query...

select s.* from scores s inner join 
(
select top 10 [name],max(score) as score from scores
group by [name]
order by score desc
) m on m.[name]=s.[name]
and m.score=s.score
 

Note : "Score" is the column name which contains data thru which u find highscore.

If there are structural changes, pls revert with table structure.

[coolsti]

Hi,

try what the previous reply suggests, it looks like it will work for you.

But just to clarify for you: The T2 within the select subquery is just an alias for the table used within the subquery. This alias is used to save on some typing of the table names in the subqueries where clause, and maybe to make this subquery table name different from the table in the first part of the query.

However, you also need to give an alias to the entire table that you are creating in the subquery. What you are doing here is creating a temporary table in the subquery which you need to refer to later, and you do this with the alias that you give to it. And the place for this alias is right after the closing parenthesis of the subquery.

You cannot get anywhere with the distinct in your subquery. What you need to do there is build up a table that gives your relationship of name to maximum score, and you do this with a max(score) and group by name.

Doing this, you get a temporary table (let us call it T3 for this explanation) with two columns: name and max(score), and you use this table to select the rows that you wish to see from the first part of the query, by matching "where name=T3.name and score=T3.maxscore" where here I have used the alias max(score) as maxscore. This will then give you all the rows of the first table (which contains other information like the time) which correspond to the maximum score for each name.

[chromis]

Thanks for both of your replies they were very informative, unfortunately I couldn't get BHTanna's solution to work I suspect that the sql is for ms sql? MySQL threw errors about the top 10 statements.

I got a query result back using the query below, however it doesn't order the time and milliseconds quite right:

SELECT scores.id, scores.name, scores.time, scores.milliseconds
FROM scores
INNER JOIN
(
  SELECT T2.name, T2.id
  FROM scores AS T2
  GROUP BY T2.name
  ORDER BY T2.time, T2.milliseconds DESC
) T2 ON T2.id = scores.id;
 

I also tryed this:

SELECT scores.id, scores.name, max(scores.time) AS maxTime, max(scores.milliseconds) AS maxMill
FROM scores
INNER JOIN
(
  SELECT T2.name, T2.id
  FROM scores AS T2
  GROUP BY T2.name
  ORDER BY T2.maxMill, T2.maxTime DESC
) T2 ON T2.id = scores.id;
 

But mysql threw an error about not being able to find the T2.maxMill and T2.maxTime, if I run the query without the inner join it complains about there not being a GROUP BY clause to go with the min, max functions. I add one in after the inner join and it still throws errors.

I obviously still don't understand this properly, could you explain further please?

Thanks alot.

[BHTanna]

Thanks for both of your replies they were very informative, unfortunately I couldn't get BHTanna's solution to work I suspect that the sql is for ms sql? MySQL threw errors about the top 10 statements.

I got a query result back using the query below, however it doesn't order the time and milliseconds quite right:

SELECT scores.id, scores.name, scores.time, scores.milliseconds
FROM scores
INNER JOIN
(
  SELECT T2.name, T2.id
  FROM scores AS T2
  GROUP BY T2.name
  ORDER BY T2.time, T2.milliseconds DESC
) T2 ON T2.id = scores.id;
 

I also tryed this:

SELECT scores.id, scores.name, max(scores.time) AS maxTime, max(scores.milliseconds) AS maxMill
FROM scores
INNER JOIN
(
  SELECT T2.name, T2.id
  FROM scores AS T2
  GROUP BY T2.name
  ORDER BY T2.maxMill, T2.maxTime DESC
) T2 ON T2.id = scores.id;
 

But mysql threw an error about not being able to find the T2.maxMill and T2.maxTime, if I run the query without the inner join it complains about there not being a GROUP BY clause to go with the min, max functions. I add one in after the inner join and it still throws errors.

I obviously still don't understand this properly, could you explain further please?

Thanks alot.

Yes you are right.. Mine is SQL and u r using My SQL.

I simply want u to identify top 10 names in inner query rather then main / outer query. In you code 2, use max function inside and use join with it.

I dont know exact sytax in MY SQL, still i m trying in your code only.. Modifying yours as per logic....

SELECT scores.id, scores.name, scores.time AS maxTime, scores.milliseconds AS maxMill
FROM scores
INNER JOIN
(
  SELECT name, id,max(time) as maxTime,max(milliseconds) as maxMill
  FROM scores 
  GROUP BY name,id
  ORDER BY time desc, milliseconds desc
)  as T2 ON T2.id = scores.id and t2.maxTime=scores.time and 
t2.maxmill=scores.milliseconds
 

Hope this will help.

[coolsti]

Try this:

SELECT scores.id, scores.name, scores.score, scores.time, scores.milliseconds FROM scores INNER JOIN (SELECT T2.id, max(T2.scores) as maxscore FROM scores T2 group by T2.id) T3 ON scores.id = T3.id and scores.score = T3.maxscore;
 

The order by clause within the subquery does you no help at all. You are not interested in the order of the temporary table T3. Neither do you want to take the max of any column in the first table. The above will work for you unless I have a typo or syntax error.

Note that the first part of the query just spits out your columns in an ordinary select statement. Alone, you would get everything. The subquery makes a table of the relationship id <=> max(score), and here I assume id is a unique key. The join will then pull out only the rows of the first part of the query where the two conditions are met:

scores.id = T3.id AND scores.score = maxscore

which is what you want :)

[chromis]

Thanks again BHTanna but I'm afraid that outputs all the highscores albeit ordered correctly.

Maybe I've not been clear, I would like to output up to 10 rows where the name column is not duplicated and the results are ordered by the lowest time.

coolsti thanks again, unfortunately the code threw an error, I had to modify to this because there isn't a score column (unless I'm not understanding the purpose of scores.score):<br />

SELECT scores.id, scores.name, scores.time, scores.milliseconds 
FROM scores 
INNER JOIN 
(
SELECT T2.id, min(T2.time) as mintime 
FROM scores T2 group by T2.id
)
T3 ON scores.id = T3.id and scores.time = T3.mintime;
 

This just outputs all of the results in no particular order. I then tryed this:

SELECT scores.id, scores.name, scores.time, scores.milliseconds
FROM scores
INNER JOIN
(
SELECT T2.id, min(T2.time) as mintime
FROM scores T2 group by T2.name
) T3 ON scores.id = T3.id and scores.time = T3.mintime;
 

This returned just two results.

I then thought I'd see what the sub-query was returning and with a bit of modification it on it's own returns the correct results!

SELECT id, name, min(time) as time, min(milliseconds) as milliseconds
FROM scores GROUP BY name
 

I really appreciate the explanations, I've got a handle on sub-queries now at least, shame it's taken me most the day though...

I'm now going to see if I can do the same thing with an actual sub-query!

Thanks for all your help.

Chromis

[coolsti]

Sorry, I didn't understand the basic task you were trying to solve.

SELECT scores.id, scores.name, scores.time, scores.milliseconds 
FROM scores 
INNER JOIN 
(
SELECT T2.id, min(T2.time) as mintime 
FROM scores T2 group by T2.id
)
T3 ON scores.id = T3.id and scores.time = T3.mintime
order by scores.time asc limit 10;
 

If I understand it now, this should work. You just need to add the order by clause to order the results by something, and of course a limit clause if you want to limit the number of results.

The above should work.

Your subquery:

SELECT id, name, min(time) as time, min(milliseconds) as milliseconds
FROM scores GROUP BY name
 

may indeed work for you here! If the minimum time and the minimum milliseconds are always in the same row for a given name, then it will work. For example if time is the same value as milliseconds except for a unit conversion. However, consider this:

SELECT id, name, min(time) as time, min(milliseconds) as milliseconds, date_of_event
FROM scores GROUP BY name
 

where I add a fictive extra column "date_of_event". The GROUP BY will not maintain the association of name <=> min(time) <=> date_of_event, so if you want to show the correct date_of_event that corresponds to the min(time) for a name, you have to use the more complicated two part query that I suggest.

[BHTanna]

Use limit 10 in sub query

SELECT scores.id, scores.name, scores.time AS maxTime, scores.milliseconds AS maxMill
FROM scores
INNER JOIN
(
SELECT name, id,max(time) as maxTime,max(milliseconds) as maxMill
FROM scores
GROUP BY name,id
ORDER BY time desc, milliseconds desc
Limit 10
) as T2 ON T2.id = scores.id and t2.maxTime=scores.time and
t2.maxmill=scores.milliseconds
Let me explain you what exactly this query is doing.

In you subquery, we are finding top 10 names. And also its group by names which will help us to restrict Name only once if twise scored high in top 10. and that too, his top score using max function.

As your sub query is giving only required top 10 names, and we are using inner join with name as well as the time to fetch exactly the same record which we have identified in subquery. If you need only name then there is no need of outer query. Only inner one (sub query) will help.

Here join is required, when u need more or all columns from the table scores. If you want only name then only our sub query will help u.

Thanks

[coolsti]

BHTanna points out where I may have made an error in my suggested solution. The inner query should be "GROUP BY NAME", not GROUP BY ID and neither should it be GROUP BY ID,NAME.

I assume here that ID is a running unique index so that if you look at all the rows of a given name, say "Steve" for example, there will be a different ID value for each of them. But you want to find the minimum time for all the rows for Steve (and all the other names as well) so you need to do a GROUP BY NAME here. GROUP BY ID would be incorrect, and so would GROUP BY ID,NAME.

I think we are confused in how to do this query correctly because you never actually posted the structure of your tables. Try to post the CREATE TABLE queries for your tables.

- Steve

[chromis]

Sorry I should have made it much more clear to start with, here is my table structure:

CREATE TABLE IF NOT EXISTS `scores` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(30) NOT NULL default 'anonymous',
  `email` text NOT NULL,
  `time` time NOT NULL default '00:00:00',
  `milliseconds` tinyint(4) NOT NULL default '0',
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;
 

and here is what I am now outputting with the SQL statement:

2 Joe Wins Again! 00:00:54 12
12 sam 00:01:21 63
1 stuart 00:01:58 19
11 tony d Man 00:03:06 53

coolsti, I don't quite understand why adding a another column will make the GROUP BY lose the association name <=> min(time) <=> date_of_event, do you mean there can only be a 1 <=> 2 association and not a 1 <=> 2 <=> 3 association?

Btw, how do you output a nice ascii table using phpmyadmin, is it possible or do i have to use the command line mysql?

Thanks.

[coolsti]

What you want to do with the Group By is to collapse the rows so that the name field becomes unique. And when you do this with the MAX or MIN operator over another column, mysql calculates the MAX or MIN value for each name and uses that rather than some random pick (I think it takes the first occurrance for each name in the table if you do not use MAX or MIN or another similar operator).

But your ID field is unique and so a GROUP BY the ID field in this case (since you do not have a table join in the query where the group by is being done) will result in nothing being grouped, since the ID field is unique and, hence, is already "grouped". So no, here you should not want to do a group by ID.

Hope that helps. This was a fast reply as I am out the door now.

[coolsti]

Hi again,

I have a bit more time to finish my last reply. I hope what I wrote for the reason why the Group By ID will not work is clear to you.

If you had another situation, where not only do you have a name column, but let us say for example you had a city column, for the city where the person lives. Let us say that there can be a Steve in Detroit and a Steve in Boston but these are not the same person, and you want to get the minimum times for both Steve's separately. In this case you need a group by with multiple columns, and it would look like Group By name, city.

But now looking at your table structure, there is another thing that I see that may be wrong. You have the two fields "time" and "milliseconds", but what is the actual time that you want to find the minimum of? The time field? Or the milliseconds field? Or the combination of the two to give the total time? Because this is what you want in your MIN(xxx) function in the subquery.

As for formating your results, I cannot help you here. I use PHP to access my database and PHP makes HTML tables for me to visualize the results. I don't use the application you mention.

[chromis]

Hi coolsti,

Thanks alot that's made things alot clearer. I realise that now I do need to GROUP BY the results by the name and email and from what you said about the time, it sounds like I should be combining the time and milliseconds before using the min() function. You can't find the minimum of two columns I take it?

This leaves me with the following query:

      SELECT scores.id, scores.name, scores.email, scores.time, scores.milliseconds
      FROM scores
      INNER JOIN
      (
      SELECT T2.id, T2.name, T2.email, min(T2.time + T2.milliseconds) AS mintime
      FROM scores T2 GROUP BY T2.name, T2.email
      ) T3 ON scores.name = T3.name AND scores.email = T3.email
 

Which much to my frustration is giving me all the results again.

So, I've selected the columns I want to output, next I've created a temporary table T2 and selected the id, name, email and min() of time + milliseconds, then GROUPed the results by name and email, which means I should have unique name and email values. I've then created another temporary table T3 and JOINed the results from T2 based on the name and email columns.
However, it seems the GROUP BY has been ignored somehow, I'm now getting all results again and in my results mintime is not a column.

Sorry to be a pain, I still can't get this to work.

[BHTanna]

Pls try this one...


SELECT T1.id, T1.name, T1.email, T1.time, T1.milliseconds
FROM scores T1
INNER JOIN
(
SELECT [name], min(time + milliseconds) AS mintime
FROM scores GROUP BY name
) T2 ON T2.name = T1.name AND T2(mintime). = T1.(time + milliseconds)

This will definately work...

[chromis]

Pls try this one...


SELECT T1.id, T1.name, T1.email, T1.time, T1.milliseconds
FROM scores T1
INNER JOIN
(
SELECT [name], min(time + milliseconds) AS mintime
FROM scores GROUP BY name
) T2 ON T2.name = T1.name AND T2(mintime). = T1.(time + milliseconds)

This will definately work...

Hi BHTanna,

Unforunately after some necessary modification it returned incorrect results.

SELECT T1.id, T1.name, T1.email, T1.time, T1.milliseconds
FROM scores T1
INNER JOIN
(
SELECT name, min(time + milliseconds) AS mintime
FROM scores GROUP BY name
) T2 ON T2.name = T1.name AND T2.mintime = (T1.time + T1.milliseconds)
 

produced:

      7      Joe Wins Again!      test      00:00:58      31
    13     sam           00:01:21     87
    1     stuart     sjfbsakldmfasd     00:01:58     19
    11     tony d Man     [e-mail]     00:03:06     53
 

Result 7 is not the fastest time. 00:00:54 93 is.
I've come up with the following but still it outputs the wrong results:

      SELECT scores.id, scores.name, scores.email, scores.time, scores.milliseconds
      FROM scores 
      INNER JOIN
      (
      SELECT T2.id, T2.name, T2.email, T2.time, T2.milliseconds, min(T2.time + T2.milliseconds) AS mintime
      FROM scores T2 GROUP BY T2.name, T2.email
      ) T3 ON scores.name = T3.name AND scores.email = T3.email AND (scores.time + scores.milliseconds) = T3.mintime
 

results:

       3      Joe Wins Again!      as      00:01:15      81
     7     Joe Wins Again!     test     00:00:58     31
     13     sam           00:01:21     87
     12     sam     sjhads     00:03:12     63
     1     stuart     sjfbsakldmfasd     00:01:58     19
     11     tony d Man     [e-mail]     00:03:06     53
 

[BHTanna]

Sorry.. I again gone thru your table structure...

pls try this and let me know..

select s.id, s.name, s.email, s.time, s.milliseconds
FROM scores s inner join

(
SELECT [name],mintime,min(milliseconds) as minsec
FROM scores T1
INNER JOIN
(
SELECT [name], min(time) AS mintime
FROM scores GROUP BY name
) T2 ON T2.name = T1.name AND T2(mintime). = T1.(time)
group by [name],mintime
) m
on m.name=s.name,mintime=time,minsec=milliseconds

Hope it will work and let me know, after it works, if u dont get, will explain you.

[coolsti]

Hi coolsti,

Thanks alot that's made things alot clearer. I realise that now I do need to GROUP BY the results by the name and email and from what you said about the time, it sounds like I should be combining the time and milliseconds before using the min() function. You can't find the minimum of two columns I take it?

This leaves me with the following query:

      SELECT scores.id, scores.name, scores.email, scores.time, scores.milliseconds
      FROM scores
      INNER JOIN
      (
      SELECT T2.id, T2.name, T2.email, min(T2.time + T2.milliseconds) AS mintime
      FROM scores T2 GROUP BY T2.name, T2.email
      ) T3 ON scores.name = T3.name AND scores.email = T3.email
 

Which much to my frustration is giving me all the results again.

So, I've selected the columns I want to output, next I've created a temporary table T2 and selected the id, name, email and min() of time + milliseconds, then GROUPed the results by name and email, which means I should have unique name and email values. I've then created another temporary table T3 and JOINed the results from T2 based on the name and email columns.
However, it seems the GROUP BY has been ignored somehow, I'm now getting all results again and in my results mintime is not a column.

Sorry to be a pain, I still can't get this to work.

You are getting there, but you are making small mistakes :)

First of all, your time field is of type TIME and your milliseconds field is of type tinyint, and I am not sure you can find the minimum time by just adding these two fields. MySQL may not understand that and thereby give you the wrong answer, as a TIME variable is stored in a certain format. You may need a more complicated expression (check the Mysql documentation on date and time functions) to calculate the minimum time from these two columns. No, you cannot do a MIN() operation on both columns to get the minimum time. You need to convert the two columns to one number representing the time in either seconds, milliseconds, or whatever, and then do the minimum on that.

The next problem is that when you have calculated this minimum time in your subquery, you also need to calculate the same on all your data because you need to compare this calculated total time in the first part with the minimum time of the subquery in order to do the final selection of only the rows that pertain to the minimum time.

Here is what I mean:

      SELECT scores.id, scores.name, scores.email, scores.time, scores.milliseconds
      FROM scores
      INNER JOIN
      (
      SELECT T2.id, T2.name, T2.email, EXPRESSION(T2.time; T2.milliseconds) AS mintime
      FROM scores T2 GROUP BY T2.name, T2.email
      ) T3 ON scores.name = T3.name AND scores.email = T3.email and 
         EXPRESSION(scores.time;scores.milliseconds) = T3.mintime
 

Notice two things:

1) you need to substitute the "EXRESSION(x;y)" in the two places above with whatever mysql expression will give you the minimum time from the two fields time and milliseconds. (Sorry I have no time right now to find this out for you)

2) I added an "and" clause to the end of the query. You have forgotten this in your attempt, and that is why you got all the rows back.

See if this gets you a bit further.

------------------------------------------------------
Edit: Ok, I looked this up in the mysql documentation and it MAY be what you want. It depends on what your time column actually represents. Is it the time that you want to minimize (without the milliseconds part)?

      SELECT scores.id, scores.name, scores.email, scores.time, scores.milliseconds
      FROM scores
      INNER JOIN
      (
      SELECT T2.id, T2.name, T2.email, 
         min(time_to_sec(T2.time)+T2.milliseconds/1000) AS mintime
      FROM scores T2 GROUP BY T2.name, T2.email
      ) T3 ON scores.name = T3.name AND scores.email = T3.email 
         and (time_to_sec(scores.time)+scores.milliseconds/1000) = T3.mintime
 

---------------------------------------------------------------------
Edit again: I just tried this using your create table and some fake data, and the above did not work until I modified it like this:

      SELECT scores.id, scores.name, scores.email, scores.time, scores.milliseconds
      FROM scores
      INNER JOIN
      (
      SELECT T2.id, T2.name, T2.email, 
         min(time_to_sec(T2.time)*1000+T2.milliseconds) AS mintime
      FROM scores T2 GROUP BY T2.name, T2.email
      ) T3 ON scores.name = T3.name AND scores.email = T3.email 
         and (time_to_sec(scores.time)*1000+scores.milliseconds) = T3.mintime
 

The equality constraint fails when you divide milliseconds by 1000, most likely because I did not cast the tinyint to a float. It works though if you multiply the time by 1000 instead of dividing the milliseconds by 1000.

[chromis]

Bhtanna:

I tryed to convert your SQL into MySQL but ended up getting very confused with the different table alias's I didn't have time to work it out unforunately. But thanks anyway, I've learnt much from what you have said.

Coolsti:

Thanks for taking all this time to help. I did end up realising that simply adding millseconds won't work due the type conversion as you described it, so I came up with the following (albeit incorrect):

      SELECT scores.id, scores.name, scores.email, scores.time, scores.milliseconds
      FROM scores 
      INNER JOIN
      (
      SELECT T2.id, T2.name, T2.email, T2.time, T2.milliseconds, min(T2.time + (T2.milliseconds / 100)) AS mintime
      FROM scores T2 GROUP BY T2.name, T2.email
      ) T3 ON scores.name = T3.name AND scores.email = T3.email AND (scores.time + (scores.milliseconds / 100)) = T3.mintime
 

Which is similar to what you came up with only I got the expression wrong.

I ran your query and the only thing that wasn't quite right was the order, so I put an ORDER statement and a LIMIT statement for 10 records and hopefully, this is everything I need:

SELECT scores.id, scores.name, scores.email, scores.time, scores.milliseconds
FROM scores
INNER JOIN (
    SELECT T2.id, T2.name, T2.email, T2.time, T2.milliseconds, min( T2.time + ( T2.milliseconds /100 ) ) AS mintime
    FROM scores T2
    GROUP BY T2.name, T2.email
) T3 ON scores.name = T3.name
AND scores.email = T3.email
AND ( scores.time + ( scores.milliseconds /100 ) ) = T3.mintime
ORDER BY mintime
LIMIT 0 , 10 
 

which gives the results:

6      Joe Wins Again!      test      00:00:54      93
3     Joe Wins Again!     as     00:01:15     81
13     sam           00:01:21     87
1     stuart     sjfbsakldmfasd     00:01:58     19
11     tony d Man     [e-mail]     00:03:06     53
12     sam     sjhads     00:03:12     63
 

and that works perfectly now! I've definately learnt alot through doing this and you've both been extremely helpful, thankyou very much!

[coolsti]

Glad to have helped!

Just make sure that your expression for the total time is working correctly for all instances. I am not sure what happens when you just add the expression

time + milliseconds/100

when time is a TIME field and millisecond is a tinyint field. It may conveniently work for your test cases but may fail elsewhere.

In my final solution, I convert the TIME field to seconds and then I multiplied (perhaps incorrectly using 1000 instead of 100) to put it in the same units as the milliseconds field so they could be added.

Your solution may indeed work! This is just to caution you to check.

[chromis]

Ah right yeah I meant to put the order by and limit on your query not mine! You were right in your expression as there are 1000 milliseconds in a second.