Top Function

[Mary10k]

Hello,

I have a table called Labs and a table called EE's. Each EE has many labs in the lab table. I am trying to figure out how I find the top 2 labs (based on date) and return that along with all of the fields in the EE table. Would I use a sub query?
Something like this? SELECT * FROM ee, WHERE ee.id in (select top 2 from labs). This didn't seem to work.
Thank you.
Mary10k

 

[r937]

that's because you have a dangling comma in front of the WHERE keyword, and also because you said SELECT TOP 2 but didn't say the top 2 of what column

try this --

select ee.foo
     , ee.bar
     , labs.datefield
     , labs.somedata 
  from ee
inner
  join labs 
    on ee.id = labs.ee_id
   and labs.datefield
    in ( select top 2 
                datefield
           from labs
          where ee_id = ee.id )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Mary10k]

Thank you. This works great, but I am not getting the Top 2 that I wanted. This is giving me the first two records in the sub query without selecting the top 2 by date. I thought it would start with the latest date, then the sencond latest date. Any suggestions?

Thank you.

 

[steven290]

couldn't try the max function

...( select top 2 maxfield(datefield)
from labs
group by labs.somedata
having ee_id = ee.id

 

[Mary10k]

I tried the Max function before posting this. It does not return the max 2 dates. It appears to select random top 2 rows.

 

[steven290]

k how about

... in ( select top 2
datefield
from labs
where ee_id = ee.id
order by datefield desc)

 

[Mary10k]

This is still returning 2 records without the top 2 dates in desc order. Could it be due to a time stamp in the field?

 

[r937]

nice catch, steve, i forgot the ORDER BY in the subquery

mary, please show your latest query

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Mary10k]

hI,
Here is the latest :

SELECT EE. MRN,EE.NAME,LABS.Login_Date_Time
FROM EE
INNER JOIN LABS ON LABS.MRN = EE.MRN
AND LABS.Login_Date_Time IN
(SELECT Top 2 Login_Date_Time FROM LABS WHERE EE.MRN = LABS.MRN GROUP BY EE.MRN,
LABS.Login_Date_Time ORDER BY Login_Date_Time DESC);

 

[r937]

you should not have a GROUP BY, it's a correlated subquery

SELECT EE.MRN
     , EE.NAME
     , LABS.Login_Date_Time
  FROM EE
INNER 
  JOIN LABS 
    ON EE.MRN = LABS.MRN 
   AND LABS.Login_Date_Time 
    IN ( SELECT Top 2 
                Login_Date_Time  
           FROM LABS 
          WHERE MRN = EE.MRN
         ORDER 
             BY Login_Date_Time DESC )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[steven290]

try this

SELECT EE. MRN,EE.NAME,LABS.Login_Date_Time
FROM EE
INNER JOIN LABS
ON LABS.MRN = EE.MRN
AND LABS.Login_Date_Time IN
(SELECT Top 2 Login_Date_Time
FROM LABS
WHERE EE.MRN = LABS.MRN
ORDER BY Login_Date_Time DESC);

 

[Mary10k]

Thank you for the suggestions. I originally had a Group By in the query because of the Max function I tried. I just tried the above referenced code and still the same result as before. I am using a passthrough query to my SQL database, could that have anything to do with the reason why I am not getting the top 2 values returned?

Thank you,
Mary10K

 

[r937]

could you please script a few rows of data from each table so that we may test the query on your data

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Mary10k]

Yes, thank you. Here is what the query returned:
MRN Login_Date_Time
00036871 7/9/2002 10:27:00
00036871 9/15/2003 13:15:00

When I open the table, this MRN contains the following rows:
MRN Login_Date_Time
00036871 10/1/2002 10:20:00 AM
00036871 7/9/2002 10:27:00 AM
00036871 1/7/2002 10:12:00 AM
00036871 4/6/2004 11:42:00 AM
00036871 4/2/2002 2:10:00 PM
00036871 2/25/2003 10:26:00 AM
00036871 9/15/2003 1:15:00 PM
00036871 11/1/2004 10:44:00 AM

 

[r937]

your problem appears to be that Login_Date_Time is a varchar

change the subquery to --

 and LABS.Login_Date_Time 
  in ( select Top 2 
              Login_Date_Time  
         from LABS 
        where MRN = EE.MRN
       order 
           by [b]cast( Login_Date_Time 
               as datetime )[/b]  desc )

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Mary10k]

Thank you so much! This produced the results I wanted. You're a champ.

 

[maswien]

Since EE to Lab is one to many, so the number of rows after the inner join should be the number of the rows for table Lab, so following SQL should be same as the above queries:

SELECT top 2 EE.MRN,EE.NAME,LABS.Login_Date_Time
FROM EE
INNER JOIN LABS
ON LABS.MRN = EE.MRN
ORDER BY Login_Date_Time DESC