How do I Display one Row for the lowest number of a column?

Question

This question is complicated so examples would work best...I have the following table on ODBC, not SQL server Management

NAME     SEQNUM     

JOHN     2          
JOHN     4
JOHN     7
MARY     12
MIKE     4
MIKE     9
PETER    7
PETER    12

So, i want to pull back one name with the lowest seqNum...

NAME     SEQNUM
JOHN     2
MARY     12
MIKE     4
PETER    7

This data will not work with SELECT (MIN(SEQNUM)). That returns a number. I want the actual data to put in my dataset. Does ANYONE know how to do that?

Answer 1

SELECT TOP 1 Name, SeqNum FROM yourtable ORDER BY SeqNum DESC

Will display the one with highest SeqNum

Answer 2

select Name, MIN(SEQNUM)
from TABLE
Group By Name

Answer 3

SELECT t1.*
FROM yourtable t1
 LEFT OUTER JOIN yourtable t2 
 ON (t1.name = t2.name AND t1.seqnum > t2.seqnum)
WHERE t2.seqnum IS NULL;

---

Re. Dems' comment:

If you simply use GROUP BY name then your select-list can only include name and the aggregate function MIN(). It's often the case that you really want the whole row where the minimum value occurs per group.

If you use the TOP solution (or LIMIT if you use MySQL, PostgreSQL, SQLite), then you can't get the minimum for multiple groups based on name, you can only get the minimum for one name.

I read between the lines of the OP's question and guessed that they want the whole row, that there are more columns than those shown in the question (there always are), and that the desired query should return results for multiple names, not just one.

---

Re. SquareCog's comment:

The solution you suggest is also a join:

SELECT t1.*
FROM yourtable t1
  JOIN (SELECT name, MIN(seqnum) AS seqnum FROM yourtable GROUP BY name) t2
  USING (name, seqnum);

However, this solution probably can't use indexes to evaluate the join, whereas the solution I gave can.

Answer 4

I believe this is what you want:

select NAME, min(SEQNUM) as SEQNUM
from TABLE
group by NAME

Answer 5

select name from table t where seq_num = (select max(sequnum) from table tt where t.name = tt.name)

But I like the grouping one better.

Answer 6

Bringing Ian's and Bill's answers together gives you the best of both worlds -- access to the complete record, and not having to do a potentially very expensive join (any join that's not an equijoin can blow things up like you wouldn't believe).

SELECT t1.* 
FROM yourtable t1,
     (SELECT name, MIN(seqnum) as seqnum
      FROM yourtable
      GROUP BY name) t2
WHERE t1.name=t2.name
      AND t1.seqnum=t2.seqnum

You can also use the join syntax to achieve the same result (instead of the WHERE), but in this case I think it's a little clearer what's going on if you use a where.