Hello,
I have following table:
product| ordered_by | date
n1 | A | 2006-01-01
n1 | A | 2006-02-01
n1 | D | 2006-03-01
n1 | B | 2006-05-01
n2 | B | 2006-01-01
n2 | C | 2006-04-01
....
As a result I only want one set per product with the latest "ordered_by".
In this example:
n1, B and
n2, C
So far I solved it within the executing program but I was wondering if there
is an efficient query to do the same.
Thanks,
James 5 4613
James Scott wrote: As a result I only want one set per product with the latest "ordered_by". In this example: n1, B and n2, C
SELECT t1.*
FROM tablename t1
LEFT OUTER JOIN tablename t2 ON t1.product = t2.product AND t1.`date` <
t2.`date`
WHERE t2.`date` IS NULL
In other words, "show me the row where there is no other row with the
same product and a greater date."
Regards,
Bill K.
or...
select product,order_by,max(date) from tablea group by
product,order_by;
or...
select product,order_by,max(date) from tablea group by
product,order_by;
<on*******@firstdbasource.com> wrote in message
news:11*********************@j55g2000cwa.googlegro ups.com... or...
select product,order_by,max(date) from tablea group by product,order_by;
This won't work. read the OPs requirements.
Rich
"James Scott" <js************@nospmahoo.com> wrote in message
news:IM*****************@twister.nyroc.rr.com... Hello,
I have following table: product| ordered_by | date n1 | A | 2006-01-01 n1 | A | 2006-02-01 n1 | D | 2006-03-01 n1 | B | 2006-05-01 n2 | B | 2006-01-01 n2 | C | 2006-04-01 ...
As a result I only want one set per product with the latest "ordered_by". In this example: n1, B and n2, C
So far I solved it within the executing program but I was wondering if
there is an efficient query to do the same.
Thanks, James
If you have subqueries, this will do it:
select product
,orderedby
from [order] as o1
where orderdate = (
select max(orderdate)
from [order] as o2
where o2.product = o1.product)
Rich This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Kali K E |
last post by:
Hi,
I could not understand how I can do the following things in Python.
Please help me.
1. First I have to find the current directory from where the script is
invoked.
2. Next I have to form a...
|
by: mike420 |
last post by:
In the context of LATEX, some Pythonista asked what the big
successes of Lisp were. I think there were at least three *big*
successes.
a. orbitz.com web site uses Lisp for algorithms, etc.
b....
|
by: Franz Steinhaeusler |
last post by:
Hello NG,
I want to retrieve the members of a class
with a baseclass.
But the problem is, how to get the non derived
members.
class a:
def who(self):
print "who"
|
by: Kenneth Keeley |
last post by:
Hi,
I am looking for a sample of how to get the password last set for a user in
active directory in a format that we can read. I am using ASP.Net and C# I
have got as far as get the value. but I...
|
by: henrycortezwu |
last post by:
Hi All,
I'm trying to get the "groups" of a user that belongs to a domain by
just passing the user's userID & domain. Is this possible?
What I tried, and is working is the ff code ( i can get...
|
by: Chuck Faranda |
last post by:
I'm trying to debug my first C program (firmware for PIC MCU). The problem
is getting serial data back from my device. My get commands have to be sent
twice for the PIC to respond properly with...
|
by: DavidPr |
last post by:
I'm getting - parse error, unexpected T_LNUMBER - error messages referring to the last "if" lines of this code.
What I'm trying to do is check the database and if this user's article row isn't...
|
by: Alireza355 |
last post by:
I have two tables:
table1:
number explanation
10 something here
11 something here
12 something here
13 something...
|
by: Sundhas |
last post by:
I made a Webservice Operation whose return type is STRING Following is the code
@WebMethod(operationName = "authorize") public String authorize(@WebParam(name = "Username") String Username)
{ ...
|
by: twomcfly |
last post by:
Hi
I have a SQL Server 2005 database with a list of results for a variety of people over time. i would like to be able to look at any row and from that row know the last 3 results for that person...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
| |