473,320 Members | 1,799 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,320 software developers and data experts.

simple distinct query

I'm trying to query the pubs database for last title for each author.
The query returns each title for each author. How can I operate on max
of title?

Here's what I'm trying:
select distinct au_id,max(title_id)as title from titleauthor
group by au_id,title_id
order by au_id
dito

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
2 2785
SELECT au_id, MAX(title_id) AS title_id
FROM TitleAuthor
GROUP BY au_id

--
David Portas
SQL Server MVP
--
Jul 20 '05 #2
Chris DiTommaso (c_****@yahoo.com) writes:
I'm trying to query the pubs database for last title for each author.
The query returns each title for each author. How can I operate on max
of title?

Here's what I'm trying:
select distinct au_id,max(title_id)as title from titleauthor
group by au_id,title_id
order by au_id


You should not include title_id in the GROUP BY clause. The GROUP BY
should include the columns you want to group by, which is the author in
this case.

Also, take out DISTINCT, it serves on purpose here.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Alex Satrapa | last post by:
I have a table from which I'm trying to extract certain information. For historical reasons, we archive every action on a particular thing ('thing' is identified, funnily enough, by 'id'). So the...
7
by: Riley DeWiley | last post by:
I am continually amazed by SQL's ability to humble me .... I have a toy query into a toy database that looks just like this: SELECT . FROM f, fw, w WHERE f.id = fw.fid and fw.wid = w.id and...
18
by: mathilda | last post by:
My boss has been adamant that SELECT DISTINCT is a faster query than SELECT all other factors being equal. I disagree. We are linking an Access front end to a SQL Server back end and normally are...
5
by: Fred Zuckerman | last post by:
Can someone explain the difference between these 2 queries? "Select Distinct id, account, lastname, firstname from table1" and "Select DistinctRow id, account, lastname, firstname from table1" ...
3
by: orekinbck | last post by:
Hi There Our test database has duplicate data: COMPANYID COMPANYNAME 1 Grupple Group 2 Grupple Group 5 Grupple Group 3 Yada Inc 4 Yada...
8
by: @sh | last post by:
This may be a really simple question, but I always have problems with Distinct queries. In this instance, I have a table of Delivery addresses, some will be exactly the same EXCEPT for the...
4
by: monomaniac21 | last post by:
hi! is it possible to do the aforementioned query - selecting only distinct in 1 col but retrieving all other cols at the same time. regards marc
6
by: issac | last post by:
Hi folks Im trying to do a simple query involving the distinct keyword and an access 2000 db, but have been frittering with it for amost and hour and a half and I cant make it work. This is...
4
by: burgensteen | last post by:
Hi all, New to this forum, so to start 'A big hello to you all'. My question: I have a drop down menu that is created from the username in my database but is distinct: ...
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
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
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...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

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.