2

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?

Patrick Harrington
  • 47,416
  • 5
  • 23
  • 20
MrM
  • 21,709
  • 30
  • 113
  • 139

6 Answers6

14
select Name, MIN(SEQNUM)
from TABLE
Group By Name
Ian Jacobs
  • 5,456
  • 1
  • 23
  • 38
3

I believe this is what you want:

select NAME, min(SEQNUM) as SEQNUM
from TABLE
group by NAME
bastos.sergio
  • 6,684
  • 4
  • 26
  • 36
2
SELECT TOP 1 Name, SeqNum FROM yourtable ORDER BY SeqNum DESC

Will display the one with highest SeqNum

Richard L
  • 1,211
  • 7
  • 10
1

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.

SquareCog
  • 19,421
  • 8
  • 49
  • 63
  • Well I'm not an expert on the MS SQL Server optimizer but it seems to me that a join on a derived table isn't likely to be able to make use of an index on seqnum. For calculating the MIN() yes perhaps, but then to do the join I wouldn't expect so. – Bill Karwin Jan 30 '09 at 00:01
  • That is to say I would expect indexed lookups to help performance more than non-equijoins harm performance. – Bill Karwin Jan 30 '09 at 00:02
0
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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • This seems grossly overly complicated compared to other suggestions here (Ian Jacobs if a SET is required, Richard L if a single record is required) Why is this preferable to either of those? – MatBailie Jan 29 '09 at 22:28
  • Bill, the cardinality of a "greater-then" join is exponential wrt number of repeats. Better to use Ian's query as the inner relation, and join on name and seqnum. – SquareCog Jan 29 '09 at 23:07
  • @SquareCog: You're using the terms "cardinality" and "exponential" incorrectly. Even a cross-product is O(n^2), i.e. polynomial. – Bill Karwin Jan 29 '09 at 23:20
  • Doh. You are correct of course. The point about inefficiency of forcing a join like that remains, though. – SquareCog Jan 29 '09 at 23:45
  • @SquareCog: I see your point but your suggestion solution is also a join. We should not shy away from joins in SQL -- that's how the language is supposed to be used. – Bill Karwin Jan 29 '09 at 23:52
  • My suggestion is an equijoin, which is significantly cheaper, computationally, than a non-equijoin (unless we are in MySQL land and all we ever do is nested loops...) – SquareCog Jan 29 '09 at 23:58
  • regarding indexes -- I am no expert on MS SQL either, but I can guarantee you that Oracle does use indexes in this case, since they are still available for the outer relation. – SquareCog Jan 30 '09 at 00:57
0

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.