SQL stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-28 : 09:33:19

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 - 2003-01-28 : 09:43:16

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources