[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4082
  • Last Modified:

MySQL - Select Max Unique Order By

I am trying to create a list of data where id is sorted highest to lowest, but where each name is unique.

For example:

name : id : text
aaaa : 11 :  stuff
bbbb : 12 :  more stuff
aaaa : 13 :  other stuff
bbbb : 14 :  more other stuff

Query should return only one name for it's highest id only so:

bbbb : 14 :  more other stuff
aaaa : 13 :  other stuff

Thanks for reading.
SELECT name, MAX(id) AS id, text, 
			option 
			FROM dataset1 
			LEFT OUTER JOIN dataset2 ON name_fk = name  
			WHERE something!=1
			GROUP BY name
			ORDER BY id DESC
			LIMIT 15;

Open in new window

0
ncoo
Asked:
ncoo
  • 3
  • 2
2 Solutions
 
imitchieCommented:
SELECT name, id, text,
                        option
                        FROM dataset1
                        LEFT OUTER JOIN dataset2 ON name_fk = name  
                        WHERE something!=1
                           AND id = (SELECT MAX(ID) from dataset1 x where x.name = dataset1.name)
                        GROUP BY name
                        ORDER BY id DESC
                        LIMIT 15;
0
 
ncooAuthor Commented:
Thanks, what about:

For example:

name : id : text : other :
aaaa : 11 :  stuff : 5 :
bbbb : 12 :  more stuff : 10 :
aaaa : 13 :  other stuff : 12 :
bbbb : 14 :  more other stuff : 1 :

Query should return only one name for it's highest other only so:

aaaa : 13 :  other stuff : 12 :
bbbb : 12 :  more stuff : 10 :

the field other is part of table dataset1
0
 
imitchieCommented:
SELECT * FROM
(
SELECT name, id, text, other
FROM dataset1
LEFT OUTER JOIN dataset2 ON name_fk = name  
WHERE something!=1
GROUP BY name
ORDER BY other desc
) AS Y
ORDER BY id DESC
LIMIT 15;
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ursangelCommented:
i hope you always need only the highest id , what ever your field names are.
Most simple way seems like this.
Check this out...
select id, ename, text from Data a
where id in (select max(id) from Data group by ename)
order by id
0
 
ncooAuthor Commented:
The problem with that imitchie is that it sorts the whole table, is there anyway to avoid sorting the whole table before selecting just the 15 records?
0
 
imitchieCommented:
The problem is that the way you have prescribed the problem domain requires that you do sort everything, group them in a Particular order, then Sort them by a secondary order, however the limit 15 is applied to the Secondary order!  You might just want to revisit what you can live with.  Because there is an inherent dependancy in there.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now