SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQL stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 01/28/2003 :  09:33:19  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Keith writes "Using SQL 2000 on Win 2000:

I have two tables using inner Joins and this is the short version,

SELECT tb1.ID, tb1.Date, tb1.Date2, tb1.xID, tb1.SRnum, tb2.FirstName, tb2.LastName, tb2.xID
FROM tb1 INNER JOIN
Tb2 ON tb1.xID = tb2.xID
WHERE (tb1.SRnum = @SRnum)
ORDER BY tb1.xID, tb1.Date DESC


Basically this will pull a list according to the where clause that looks like:

tb1.ID tb1.Date .....
1 1/02/03
1 6/05/02
1 3/08/02
2 5/02/02
3 ....

What I am trying to do is get the record with the most recent date entered to display on an ASP page and also sorting by column.

What I think I need to do is filter out for the first occasion of tb1.ID and ignore the rest so I can then sort by any column. I can get the correct results sorting by LastName and filtering out the duplicates on the ASP page, but when sorting by tb1.Date it just doesn’t work without being able to do something like creating a temp table to store the first instance. I have not found a decent solution to filtering the first instance.

Thanks Keith"

royv
Constraint Violating Yak Guru

455 Posts

Posted - 01/28/2003 :  09:43:16  Show Profile  Reply with Quote
Use the TOP statement:

SELECT TOP 1 tb1.ID, tb1.Date, tb1.Date2, tb1.xID, tb1.SRnum, tb2.FirstName, tb2.LastName, tb2.xID
FROM tb1 INNER JOIN
Tb2 ON tb1.xID = tb2.xID
WHERE (tb1.SRnum = @SRnum)
ORDER BY tb1.xID, tb1.Date DESC

***************************************
Death must absolutely come to enemies of the code!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000