Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sql max column

Posted on 2009-05-14
5
Medium Priority
?
395 Views
Last Modified: 2012-05-07
Hi

I am selecting records mulitple tables as follows

select distinct o.order_id, o.order_date, u.name, a.addressid
from orders o
inner join users u on o.user_id=u.user_id
inner join addresses a on u.user_id=a.user_id
where o.order_status='c'

I also want to add a column to the above which contains the max order_id from the above selected records.  

How can I acheive this?

Wing
0
Comment
Question by:WingYip
  • 3
  • 2
5 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 200 total points
ID: 24384489
select MAX(o.order_id) AS order_id, o.order_date, u.name, a.addressid
from orders o
inner join users u on o.user_id=u.user_id
inner join addresses a on u.user_id=a.user_id
where o.order_status='c'
GROUP BY o.order_date, u.name, a.addressid
0
 
LVL 1

Author Comment

by:WingYip
ID: 24384506
I am an idiot!
0
 
LVL 1

Author Closing Comment

by:WingYip
ID: 31581434
thanks
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24384524
WingYip said:
>>I am an idiot!

No, you just need more coffee :)
0
 
LVL 1

Author Comment

by:WingYip
ID: 24384974
actually that doesnt work as it returns the highest order number within the grouping.

If there are a total of 300 records selected using the query above then I want to return the highest order_id out of those 300 records so that each row will show the same value for the MaxOrderId column (as below)

order_id    MaxOrderId
1                 4
2                 4
3                 4
4                 4
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question