Solved

help needed with Microsoft SQL Server syntax

Posted on 2011-03-14
5
260 Views
Last Modified: 2012-05-11
select customerid, city, dateordered where customerid = (select customerid, city, max(dateordered)
0
Comment
Question by:Jimmy_inc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 19

Assisted Solution

by:Rikin Shah
Rikin Shah earned 125 total points
ID: 35135504
Try this...

select customerid, city, dateordered from <tablename> where customerid in (select customerid from <tablename> having max(dateordered))
0
 

Author Comment

by:Jimmy_inc
ID: 35135513
Yes that looks better, I will try this
0
 
LVL 41

Accepted Solution

by:
Sharath earned 125 total points
ID: 35135551
try this.
SELECT customerid, 
       city, 
       dateordered 
  FROM your_table t1 
 WHERE t1.dateordered = (SELECT MAX(t2.dateordered) 
                           FROM your_table t2 
                          WHERE t1.customerid = t2.customerid 
                                AND t1.city = t2.city)

Open in new window

0
 
LVL 5

Assisted Solution

by:Angelgeo
Angelgeo earned 125 total points
ID: 35136300

select 
  customerid, city, max(dateordered) as dateordered
from
  tablename
group by customerid, city

-- Or 

select 
  customerid, city, dateordered
from
  tablename
where 
  dateordered in (select max(dateordered) as dateordered
from
  tablename)

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 125 total points
ID: 35139240
See attached.
query.txt
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…

710 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