Sybase Top N in Subquery equivalent

CMiner · #1 (**permalink**) 02-02-10, 12:42

I know SET ROWCOUNT won't work here, I'm looking for any solution that will let me link my data like I need.

Two database tables linked in a one-to-many relationship.

I need to get the data from table 1, and a single field from table 2 based on the earliest date from a date field.

In SQL it would be easy:

SELECT Table1.*, (SELECT TOP 1 datefield FROM Table2 WHERE Table2.linkfield = Table1.linkfield ORDER BY datefield) FROM Table1

I need some way to get the same sort of query in a Sybase database.

Plan B is importing these two tables into SQL or DB2, and running the queries from there.

r937 · #2 (**permalink**) 02-02-10, 13:12

Code:

SELECT Table1.*
     , Table2.some_other_colulmn
  FROM Table1
INNER
  JOIN ( SELECT linkfield
              , MIN(datefield) AS min_date
           FROM Table2
         GROUP
             BY linkfield ) AS m
    ON m.linkfield = Table1.linkfield
INNER
  JOIN Table2
    ON Table2.linkfield = m.linkfield 
   AND Table2.datefield = m.min_date

notice that the SELECT clause contains "some other field" from Table2

if it was really the lowest datefield that you wanted, the query would be a lot simpler

usually, this type of situation wants to have some other column value on the row that has the lowest datefield for each group

Internet Services

Web Development

Digital Marketing

Consumer Tech