Pulling FIRST set of Data in a query

[cmgaviao]

cmgaviao (Programmer) May 1, 2003
Ack, I didn't include enough info in my original post. here goes again with some modifications:

Just a question in passing:

I am writing a data extraction app for the travel industry. Given that a ticket from point A to point B might actually have several segments involved, I'd like to pull the data for the First segment only and combine it with the ticket data.

Heres some sample data sets:

Ticket:
recordkey PaxName TicketNum
blah jim 1
foo sue 2

Recordkey SegmentNum SvcCat DepDate
blah 3 C 1-1-2000
blah 4 C 1-2-2000
blah 5 F 1-5-2000
foo 1 H 1-1-2000
foo 2 Y 1-5-2000

You would think that whoever builds the database would automatically number the segments starting with 1. Unfortunately never the case. So in order to get the first ServiceCategory and DepartureDate, my SQL looks like this:

SELECT TK.RECORDKEY
,TK.PAXNAME
,TS1.SVCCAT
,TS1.DEPDATE

FROM TICKET TK, SEGMENTS TS1,
(SELECT RECORDKEY, MIN(SEGMENTNUM)
FROM SEGMENTS GROUP BY RECORDKEY) TS2
WHERE TK.RECORDKEY = TS1.RECORDKEY
AND TS1.RECORDKEY = TS2.RECORDKEY


Result set should be:

blah Jim C 1-1-2000
foo sue H 1-2-2000


This works, but I can't help thinking there must be some more efficient way to do this. Maybe some function that I'm unfamiliar with.

Anyhow, drop me a line here or at

cmorse at himark.com

Thanks in advance for any assistance.

 

[THoey]

Would the MIN function work?

Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...