|
|
Posted by Lucius on 10/10/05 01:25
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 :)
Navigation:
[Reply to this message]
|