How can I pull back the FIRST row of a series?

[cmgaviao]

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.

Heres a sample data set:

Recordkey SegmentNum SvcCat DepDate
blah 3 C 1-1-2000
blah 4 C 1-2-2000
blah 5 F 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 TS1.RECORDKEY, TS1.SEGMENTNUM, TS1.SVCCAT, TS1.DEPDATE

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

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.

 

[JohnDTampaBay]

How about this?

SELECT TOP 1 TS1.RECORDKEY, TS1.SEGMENTNUM, TS1.SVCCAT, TS1.DEPDATE FROM SEGMENTS TS1

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[cmgaviao]

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.

 

[cmgaviao]

I wish it were possible to delete a post and repost when you realize you've put in incomplete info...but here goes again:


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) AS SEGNUM
FROM SEGMENTS GROUP BY RECORDKEY) TS2
WHERE TK.RECORDKEY = TS1.RECORDKEY
AND TS1.RECORDKEY = TS2.RECORDKEY
AND TS1.SEGMENTNUM = TS2.SEGNUM


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.