473,325 Members | 2,792 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

selecting only the last record in joined table

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
4 2925
Do you know the Netiquette about cross posting?

Oct 10 '05 #2
Why do you ask? This was only posted to c.d.m.s, AFAICS.

Edward

Oct 10 '05 #3
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
(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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: webhigh | last post by:
Iım not sure if this a PHP question or an MySQL question but here it goes. I have a repeat region of a table called userid What Iım trying to accomplish is being able to edit the record and...
8
by: Henrik Larsson | last post by:
Hi, I need help with selecting the following rows from a table looking like this: ID IP Query 1 -> 1 2.2.2.2 (ie first IP 1 1.1.1.1 <- Query 2 for each...
1
by: GGerard | last post by:
Hello I am working with Access 2000 I have two tables joined on a one to many relationship between two fields: Table1:FieldID (one) is joined to Table2:FieldMyID (many) Field Properties...
1
by: kkrizl | last post by:
I have a form that displays general information about an alarm permit location. There's a subform that shows detailed information about burglar alarms that have gone off at the location. When a...
7
by: Stephen Poley | last post by:
I have the following situation: - a table of employees, keyed on employee-id; - a table of training sessions, keyed on session-id; - a requirement to log who was present at which session, plus...
1
by: accessbee | last post by:
(Sorry this is so long, was trying to fully explain the situation) There are two tables that handle information on our clients. Every client has a unique ClientID, and the ClientID is the Primary Key...
4
by: wishwish20 | last post by:
Hello, I am having trouble with an SQL query which is used as a source on a report. The database is being used to keep track of billing information. I have made an sql query, which has 2 tables....
3
by: Phil Stanton | last post by:
I have a form based on a complex query (Lots of tables) If I delete a record, everything appears to be OK. Get the message "Youa are about to delete 1 record ....". I say yes. The record count...
1
by: sampalmer21 | last post by:
Hi, I want to join two tables together and only select the rows that don't have the same customer first name and last name (see my code below). When I run my code with the DISTINCT keyword, it...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.