Changing this query to group rows and filter out all rows apart from the one with smallest value

I have the following results set:

275     72.87368055555555555555555555555555555556   foo
275     72.87390046296296296296296296296296296296   foo
113     77.06431712962962962962962962962962962963   foo
113     77.07185185185185185185185185185185185185   foo

that I got from this query:

SELECT id, (tbl2.date_modified - tbl1.date_submitted)/86400, some_value
FROM tbl1, tbl2, tbl3
WHERE tbl1.id = tbl2.fid 
AND tbl1.id = tbl3.fid

How can I change it to get this:

275     72.87368055555555555555555555555555555556   foo
113     77.06431712962962962962962962962962962963   foo

i.e. get only the row with the smallest some_number

Sub queries or something ?

Many thanks :).

Answers


if some_value is a field name :

SELECT id, min((tbl2.date_modified - tbl1.date_submitted)/86400), max(some_value)
FROM tbl1, tbl2, tbl3
WHERE tbl1.id = tbl2.fid 
AND tbl1.id = tbl3.fid
GROUP BY tbl1.id

if some_value is a some constant string :

SELECT id, min((tbl2.date_modified - tbl1.date_submitted)/86400), 'some_value'
FROM tbl1, tbl2, tbl3
WHERE tbl1.id = tbl2.fid 
AND tbl1.id = tbl3.fid
GROUP BY tbl1.id

Need Your Help

CanExecute breakpoint doesn't allow me to use the application

c# mvvm icommand

I'm currently toying a little with MVVM pattern and trying commands. I find the default way (every Command class needs to implement from ICommand interface) quite boring, so I've created a new base...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.