35885

How to query row with lowest value, and also to kn

Question:

Consider these two queries:

SELECT *, 'b' AS b FROM someTable ORDER BY a ASC LIMIT 1; SELECT *, MAX(a) AS maxA FROM someTable ORDER BY a ASC LIMIT 1;

The former query returns the row with the lowest value of a, as expected. The latter query returns the first row stored on disk (usually the row with the lowest value for primary key). How can I work around this? My intention is to get the full row of the column with the lowest a value (if there is more than one I only need one, it does not matter which), and additionally I do need the value of the highest age. In a perfect world I would run two queries, but due to the way that objects are serialised in this application I cannot do that without refactoring a lot of code that isn't mine. I actually don't mind if the MySQL engine itself must query twice, the important bit is that the output be returned in a single row. I cannot write a stored procedure for this query, unfortunately. And yes, the * operator is important, I cannot list the needed fields. And there are too many row to return them all!

Note that this question is superficially similar to a previous question, however the question asked there was ill-formed and ambiguous, therefore all the answers addressed the issue that was not my intention (however useful, I did learn much and I'm happy that it turned out that way). This question asks the intended question more clearly and so should attract different answers.

Answer1:

Why not just run this:

SELECT MIN(a) as minA, MAX(a) AS maxA FROM someTable

Unfortunately, MySQL doesn't know window functions. So if you really want to select * along with min/max values, I guess you'll have to resort to a JOIN:

SELECT * FROM ( SELECT * FROM someTable ORDER BY a ASC LIMIT 1 ) t1 CROSS JOIN ( SELECT MIN(a) as minA, MAX(a) AS maxA FROM someTable ) t2

Or to a subselect, as given in Imre L's answer



Answer2:

use a subquery in select part:

SELECT *, 'b' AS b, (SELECT MAX(a) FROM someTable) AS maxA FROM someTable ORDER BY a ASC LIMIT 1;

Recommend

  • OData + EF. Writing geography types
  • Using Django JSON serializer for object that is not a Model
  • Sorting a JSON Object using Linq
  • Customise NewtonSoft.Json for Value Object serialisation [duplicate]
  • How to sort results based on join table in Rails?
  • SQL: Find top-rated article in each category
  • How to find the largest array from a multi dimensional array [duplicate]
  • C++/VS2005: Defining the same class name in two different .cpp files
  • Displaying iOS iAds only to supported countries
  • Why does Redshift need to do a full table scan to find the max value of the DIST/SORT key?
  • Convert a 12 hour time format to 24 hour time format (keeping record of the day) in python
  • Packet modification with netfilter queue?
  • PHP file_exists() anomaly
  • Python adding lots of things to PATH. How do I stop?
  • Java : How to tint this PNG programmatically?
  • Watson Conversation - Why is the ANYTHING ELSE node not chosen
  • pickle.PicklingError: args[0] from __newobj__ args has the wrong class with hadoop python
  • C# - Most efficient way to iterate through multiple arrays/list
  • HttpListener.IsSupported is false on XP SP3
  • Consuming a WCF service in a Java Client using wsHttpBinding
  • Using $compile in a directive triggers AngularJS infinite digest error
  • Possible to “watch” both HAML and SASS at the same time?
  • jQuery ready not fired after rails link_to is clicked
  • Date Conversion from yyyy-mm-dd to dd-mm-yyyy
  • Debug.DrawLine not showing in the GameView
  • CSS Linear-gradient formatting issue accross different browsers
  • C# - Serializing and deserializing static member
  • How would I use PHP exceptions to define a redirect?
  • Sending data from AppleScript to FileMaker records
  • Where to put my custom functions in Wordpress?
  • How to make Safari send if-modified-since header?
  • SVN: Merging two branches together
  • Hibernate gives error error as “Access to DialectResolutionInfo cannot be null when 'hibernate.
  • SetUp method failed while running tests from teamcity
  • How to include full .NET prerequisite for Wix Burn installer
  • What are the advantages and disadvantages of reading an entire file into a single String as opposed
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • Is it possible to post an object from jquery to bottle.py?
  • Python/Django TangoWithDjango Models and Databases