How to get first record for given SELECT 1

[Laeg]

I have a table tblA with ID fooID and table tblB with ID fooID.
This is a 1:N relationship, for this particular query I want to JOIN table A to B but only SELECT the first record for each join in tblB.

So

tblA
fooID | ... | ...
a
b
c
d

tblB
fooID | foovalue
a 'apple'
a 'orange'
b 'pear'
b 'banana'

So my SELECT would return
a | apple
b | pear

 

[r937]

please explain how you determine which row is the "first" row

remember, there is no row sequence in relational database tables

is there a timestamp on the row?

or perhaps you would like to have the row with the lowest alphabetical foovalue value?

r937.com | rudy.ca

 

[Laeg]

Thankfully I am free to take either foovalue record in tblB so an alphabetical sort would not be a problem. Essentially it is not really the first row that I am looking for and more just ONE record. I realise I did not make that very clear in my initial post.

 

[r937]

SELECT a.fooID
     , MIN(b.foovalue) as foovalue
  FROM tblA AS a
INNER
  JOIN tblB AS b
    ON b.fooID = a.fooID
GROUP
    BY a.fooID

the next thing you're gonna tell me is that there are more columns on the tblB row, and you forgot to mention that you want them included in the results, such that it picks up the entire tblB row that corresponds to the foovalue chosen by the MIN function...

r937.com | rudy.ca

 

[ingresman]

Check out the limit clause on the SELECT statement

 

[r937]

Check out the limit clause on the SELECT statement

that wouldn't work, sorry


r937.com | rudy.ca

 

[Laeg]

lol r937, I'm reminded of a song

http://www.youtube.com/watch?v=z2IkLGFiKx0

 

[ingresman]

Tell me more ??

 

[r937]

tell you more about the LIMIT clause?

well, for one thing, it's no good at getting one row per key value



r937.com | rudy.ca

 

[DonQuichote]

It is if you use it in a subquery. You may need a HAVING condition if you want to make sure at least one exists.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

okay, but srsly, how would you use a subquery with LIMIT and HAVING in this particular situation?



r937.com | rudy.ca

 

[ingresman]

Yes I see I slighty misread the original query, he wants to get the first in the N table for every one of the 1 table (if that makes sense !)
Would a distinct help out ?

 

[r937]

no, DISTINCT wouldn't help

see solution provided earlier



r937.com | rudy.ca

 

[ingresman]

Sorry have been away:
Yep played about with distinct and came to the same conclusion !
Nice to see an interesting problem sometimes !