I would like to query a database for 3 children whose fathers are the
oldest. The answer isn't necessarily unique: if the oldest father has 6
kids, then any three of his children will do. My first shot was:
SELECT TOP 3 NAME
FROM KID, DAD
WHERE KID.FATHER=DAD. ID
ORDER BY DAD.BIRTHDATE
However, in the above example this yields at least 6 children, as the
top 3 birth dates cover at least the 6 children of the oldest father.
My second attempt was to have the order-by clause enforce distinct rows
by adding a primary key:
SELECT TOP 3 NAME
FROM KID, DAD
WHERE KID.FATHER=DAD. ID
ORDER BY DAD.BIRTHDATE, DAD.ID
This works, although it is a bit slow, due to the double sort.
However, it looks a bit clumsy: is there a better, standard, solution?
Also, when I incorporate this in my Java application, as opposed to
running it as a small test, query performance drops by a factor 50. It
is the one query I haven't been able to incorporate successfully in my
code, and I suspect I hit some special case of resources becoming
scarce. I checked and double checked that I'm closing ResultSets,
Statements and Connections everywhere, I tried to use a new Connection
for this one query, but it didn't make a difference. Are there any
known issues with horrible performance drops in jdbc?
--
Grinnikend door het leven...
oldest. The answer isn't necessarily unique: if the oldest father has 6
kids, then any three of his children will do. My first shot was:
SELECT TOP 3 NAME
FROM KID, DAD
WHERE KID.FATHER=DAD. ID
ORDER BY DAD.BIRTHDATE
However, in the above example this yields at least 6 children, as the
top 3 birth dates cover at least the 6 children of the oldest father.
My second attempt was to have the order-by clause enforce distinct rows
by adding a primary key:
SELECT TOP 3 NAME
FROM KID, DAD
WHERE KID.FATHER=DAD. ID
ORDER BY DAD.BIRTHDATE, DAD.ID
This works, although it is a bit slow, due to the double sort.
However, it looks a bit clumsy: is there a better, standard, solution?
Also, when I incorporate this in my Java application, as opposed to
running it as a small test, query performance drops by a factor 50. It
is the one query I haven't been able to incorporate successfully in my
code, and I suspect I hit some special case of resources becoming
scarce. I checked and double checked that I'm closing ResultSets,
Statements and Connections everywhere, I tried to use a new Connection
for this one query, but it didn't make a difference. Are there any
known issues with horrible performance drops in jdbc?
--
Grinnikend door het leven...
Comment