By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,286 Members | 1,632 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,286 IT Pros & Developers. It's quick & easy.

selecting only the last record in joined table

P: n/a
Hello everyone, I have a query problem.

I'll put it like this. There is a 'publishers' table, and there is a
'titles' table. Publishers publish titles (of course). Now I want to make a
query (in MS SQL Server) that would return the last title published by every
of the publishers. Quite clear situation. But I can't make it work.

If I use inner join (which I should, because I need data from both tables)
then I get a result showing all publishers and all titles. What I want to
get is all publishers, and only their last title, so I don't have more than
one line for the same publisher, and this line should contain publisher
details and last title details.

I tried using DISTINCT, but it works on a whole resultant row rather then a
column, and since rows are all distnict (because they also contain columns
from titles) this didn't help me.

What I can do is (in my application) first get a list of publishers, and
then loop through them selecting only the last title belonging to each
publisher. I want to see if there is a way to accomplish the same thing with
an SQL query (or maybe a stored procedure, view, or whatever). Anything is
possible, as long as it stays within SQL server and doesn't rely on the
client application.

Of course, both 'publishers' and 'titles' tables have a primary key
('publisherID', and 'titleID'), and 'titles' has a 'publisherID' column
which relates titles with publishers.

Help :)


Oct 9 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Do you know the Netiquette about cross posting?

Oct 10 '05 #2

P: n/a
Why do you ask? This was only posted to c.d.m.s, AFAICS.

Edward

Oct 10 '05 #3

P: n/a
te********@hotmail.com wrote:
Why do you ask? This was only posted to c.d.m.s, AFAICS.


Probably posted elsewhere as a separate posting.

Proper cross posting at least enables a half decent newsreader to mark
the message as read if you have alreay read the same message in another
group.
Oct 10 '05 #4

P: n/a
(te********@hotmail.com) writes:
Why do you ask? This was only posted to c.d.m.s, AFAICS.


It was posted to microsoft.public.sqlserver.programming, but Celko has
never been any good at terminology. (Rumour has it that a on recent
presentation he said "record" when most other database peoploe would
say "row"!). The correct term for Lucius' deed is multi-posting.

I should add that appreciate Celko's warning, even if was a bit terse.
That told me that I did not have to spend any time to compose a reply.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oct 10 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.