Using the Top Clause in the LEFT Hand JOIN in SQL

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

General SQL Server Forums

New to SQL Server Programming

Using the Top Clause in the LEFT Hand JOIN in SQL

Author

Topic

jodders
Starting Member

41 Posts

Posted - 2010-05-19 : 13:13:28

Hi guys,

This query has been bugging me all afternoon and need some assistance after trawling through many threads. Currently this query returns a few rows, and I basically want it to return the 1st row using the TOP clause.(Apparently that clause is the best one to use?) I thought of using LIMIT but I am using MS SQL Server Management 2005 and that doesn't work anymore. Can anybody kindly help?

Ideally, this query will be fed into a report and will look at the most recent recno (record number) and show some comments.

thanks
Seb

SELECT
A.tbl_type,
A.recno,
A.invoice_number,
A.entry_date,
A.comments
FROM
A
INNER JOIN B
ON A.recno=B.recno
where A.invoice_number = '85701007862'
ORDER BY recno DESC

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-19 : 13:18:39

Yes you can use TOP.
What is the problem?

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-19 : 13:20:58

use SELECT TOP 1....

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jodders
Starting Member

41 Posts

Posted - 2010-05-20 : 03:40:25

Thanks guys, I don't know what came over me. I think I wasn't doing something right.

So my query now looks like this

SELECT DISTINCT TOP 1
A.tbl_type,A.recno,A.invoice_number,A.entry_date,A.comments
FROM A INNER JOIN B
ON A.recno=B.recno
-- WHERE invoice_number 857010012398
ORDER BY invoice_number DESC

which only brings back 1 record,something that worked when I specify an invoice number. However, when i comment it out, it doesn't work out to exactly what I am trying to achieve.

My database has a lot of invoices with a comment next to it. I just want it to bring back the most recent comment per invoice. I have looked into using the DISTINCT clause and GROUP clause but no luck at all.Sorry if this sounds really trivia, can you help?

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-20 : 05:45:19

Hi jodders.

I think I understand what you want.

Can you explain where the comments are? Are they in the TABLE A that your script points to. If so whats the point of the join to TABLE B -- you aren't bringing anything back from this table.

Is the comments columns actually on TABLE B and you've just made a typo in the sql you posted?

Can you give use some sample data for the two tables and explain exactly what you want from that sample?

I have a hunch that you'll want to use a derived table or an outer apply clause.

Regards,
Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jodders
Starting Member

41 Posts

Posted - 2010-05-20 : 07:01:57

hi Transact Charlie,

Thanks for trying to help, most appreciated.

The comments information is stored in the table in B. The unique key between Table A and B is the column recno which joins the the two tables together. You are right there mate, there was typo on my part which doesn't help.

SELECT
A.tbl_type,A.recno,A.invoice_number,A.entry_date,B.comments
FROM A INNER JOIN B
ON A.recno=B.recno
where b.tbl_type = '1'
ORDER BY recno DESC

Table B Data:
tbl_type recno comments
1 151 Invoice Paid
1 153 Speak to Mr.X
1 150 Speak to Mrs.Y
1 160 Speak to Mrs.Z
1 161 Speak to Mr.Charlie

Table A Data
tbl_type rec no invoice number entry_date
1 150 857010015765 2010-05-18
1 153 857010015765 2010-05-20
1 160 857010015766 2010-05-18
1 161 857010015766 2010-05-20

Desired output
tbl_type rec no invoice number entry_date Comment
1 153 857010015765 2010-05-19 Speak to Mr.X
1 161 857010015766 2010-05-20 Speak to Mr.Charlie

So for each invoice, it picks up the most recent comment in the table.

Any help would be most welcome.

Regards
jodders

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-21 : 05:23:08

Hi Jodders.

OK -- getting somewhere. So the criteria for finding the most recent thing that happened to an invoice is in tableA and there is a unique value (rec no) to join the tables together. Ok. So one more question. How do you work out the most recent comment? Is it the most recent comment by [entry_date] or the most recent comment by [rec no] (with a higher number being the most recent)?

I've assumed that the comment you want is returned by the highest [rec no]

Try this and see if it fits the bill


DECLARE @tableA TABLE (
	[tbl_type] INT
	, [rec no] INT
	, [invoice Number] BIGINT
	, [entry Date] DATETIME

	PRIMARY KEY ([rec no])
	)

DECLARE @tableB TABLE (
	[tbl_type] INT
	, [rec no] INT
	, [comments] VARCHAR(MAX)

	PRIMARY KEY ([rec no])
	)

INSERT @tableA ([tbl_type], [rec no], [invoice number], [entry Date])
      SELECT 1, 150, 857010015765, '2010-05-18'
UNION SELECT 1, 153, 857010015765, '2010-05-20'
UNION SELECT 1, 160, 857010015766, '2010-05-18'
UNION SELECT 1, 161, 857010015766, '2010-05-20'

INSERT @tableB ([tbl_type], [rec no], [comments])
      SELECT 1, 151, 'Invoice Paid'
UNION SELECT 1, 153, 'Speak to Mr.X'
UNION SELECT 1, 150, 'Speak to Mrs.Y'
UNION SELECT 1, 160, 'Speak to Mrs.Z'
UNION SELECT 1, 161, 'Speak to Mr.Charlie'

SELECT * FROM @tableA
SELECT * FROM @tableB

-- Get highest rec_no per Invoice Number
SELECT
	MAX([rec no]) AS [Highest Rec No]
	, [invoice Number] AS [Invoice Number]
FROM
	@tableA
GROUP BY
	[invoice Number]


-- Use this to get the desired Output

SELECT
	a.[tbl_type]
	, a.[rec no]
	, a.[invoice Number]
	, a.[entry Date]
	, b.[comments]
FROM
	@tablea AS a
	JOIN (
		SELECT
			MAX([rec no]) AS [Highest Rec No]
			, [invoice Number] AS [Invoice Number]
		FROM
			@tableA
		GROUP BY
			[invoice Number]
		)
		AS highA ON
			highA.[Invoice Number] = a.[Invoice Number]
			AND highA.[Highest Rec No] = a.[rec no]

	JOIN @tableb AS b ON b.[rec no] = highA.[Highest Rec No]

I've written your data down into 2 table variables but you should be able to see how this works from running the code. It's safe to run this anywhere.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jodders
Starting Member

41 Posts

Posted - 2010-05-21 : 08:17:03

Hey Charlie,

I can't thank you enough for not only teaching but taking your spare time to helping someone out you barely know. You are a good guy. Thanks for the example query.The most recent comment is indeed the highest record number which you have specified in your query.

I will play about with it and adapt it to my 2 existing tables already.

Have a great weekend!

Jodders

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-05-21 : 12:14:27

Cheers Jodders. You are welcome. Hope you have a good weekend too.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources