Least Time for Each Unique Number

[dmmckelv]

Hopefully this will be an easy one for someone.

I am trying to write a SELECT statement that pulls the row with the least time for each Special Number.

Example data would be:

Time Special Number
3:25:00 1
3:26:00 1
3:25:30 2
3:26:30 2

The expected output would be:
3:25:00 1
3:25:30 2

I don't have much because I am really a novice SQL user.

SELECT Time, Special Number
WHERE

Any help would be greatly appreciated!

[RipperJT]

Hi, the following statement should work...

SELECT min(Time),[Special Number] FROM table GROUP By [Special Number]

[dmmckelv]

Thanks for your help. The above statement did work. But, brought on a follow up question, how can I also select the other fields in the table with this statement?

What I want is certain fields from each row (eg. Time, Special Number, and Speed) with the minimum time for each distinct Special Number.

Example Table
Time..... Special Number Speed Power
3:25:00.. 1......................50.......2
3:26:00.. 1......................45.......3
3:25:30.. 2......................60.......6
3:26:30.. 2......................99.......5

The expected output would be:
3:25:00 1........50
3:25:30 2........60

I attempted a couple of statements and wound up with an error stating something to the effect of " Speed is not part of an aggregate function."

[wujtehacjusz]

Join the result of the query

SELECT min(Time),[Special Number] FROM table GROUP By [Special Number]

with the original table.

Something like this could work:

select t1.*, table.speed, table.power from
([INDENT]SELECT min(Time) as Time,[Special Number] FROM table GROUP By [Special Number] [/INDENT]) t1
inner join table t on t1.Time=t.time and t1.[Special Number]=t.[Special Number]

This should work assuming that combination of time and special number uniquely identifies each row.