Simple SQL Grouping question

[TheQuestioner]

I have an SQL question fellow developers. I have a three column table called [tblFlightLegs]. The columns are ([ID] as Int, [FlightNo] as NVarchar, and [IDMaster] as Int). The nature of this table is that many rows can be associated to the same [IDMaster].

What I want to do is create a view where only the lowest [ID] for each [IDMaster] is returned along with it's accompanying [FlightNo]. I've got as far as:-

SELECT min(tblFlightLegs.ID) as id,
min(tblFlightLegs.FlightNo) as [FlightNo],
tblFlightLegs.IDMaster

FROM tblFlightLegs
GROUP BY tblFlightLegs.IDMaster

but this doesn't actually return the lowest [ID]. I'm using SQL Server 7. Please help.

 

[LogicalmanUS]

The Questioner,

Here you go:

SELECT ID, FlightNo, IDMaster
FROM tblFlightLegs
WHERE (ID IN
(SELECT MIN(id) FROM tblFlightLegs GROUP BY IDMaster))

That should do it for you.

Logicalman

 

[TheQuestioner]

LogicalMan,

Thanks, much appreciated. My only concern with such an approach is that the sub query will have to be executed for each main query's row. Is this correct? Thanks anyway, cos I'll still be using what you provided.

TheQuestioner.

 

[LogicalmanUS]

TheQuestioner.

I'm not sure what you were asking there.
The query returns one row for each IDMaster, being the lowest ID number with it's corresponding FlighNo.

Therefore, if the tblFlightLegs table contains the following data:

1 one 1
2 two 1
3 three 1
4 four 3
5 five 3
6 six 2
7 seven 2
8 eight 1
9 nine 3
10 ten 2

The result set is thus:

1 one 1
4 four 3
6 six 2

(Columns are in order ID, FlightNo, IDMaster)

Is this what you were after?

Logicalman

 

[TheQuestioner]

Logicalman,

Sorry, I should have been a bit clearer. Your SQL will definetly do the job. I was just postulating how SQL Server would interpret the sub SQL (SELECT MIN(id) FROM tblFlightLegs GROUP BY IDMaster) within the main SQL. Would it:-

1) Execute it everytime each row from the main SQL was executed OR
2) First retrieve all the rows for the sub SQL, then append them to the main SQL row results as it process each main SQL row?

I'm intending to use this SQL within a view or stored procedure, so maybe SQL Server may optimise it for the latter.

Once again, thanks for your help.

TheQuestioner.

 

[LogicalmanUS]

TheQuestioner,

Very good point.

I have the belief (and I could be wrong on this), that it executes the sub select first, creates a temptable of the results and then executes the main select using the temptable as a lookup.

Thanks for the pointer there, made me think a bit, and that's not a bad thing.

Logicalman

 

[TheQuestioner]

Logicalman,

No problem. Thanks for your help.

TheQuestioner.