Solved

help needed with Microsoft SQL Server syntax

Posted on 2011-03-14
5
259 Views
Last Modified: 2012-05-11
select customerid, city, dateordered where customerid = (select customerid, city, max(dateordered)
0
Comment
Question by:Jimmy_inc
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 40

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Need help how to find where my error is in UFD 6 40
date diff with Fiscal Calendar 4 57
Mysql Left Join Case 10 70
Check ALL SP in database make sure there are no errors 17 46
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

820 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