Heh, except the ON in the subquery should be a WHERE, as pointed out by DRE
SELECT A.item, A.date, (select MIN(date) FROM yourTable B WHERE b.itemID =
A.itemID) AS orderDate
FROM yourTable A
ORDER BY orderDate, A.item, A.date
-Jeff
DRE wrote:
>Thanks to Jeff, the issue is resolved. For the
Thanks to Jeff, the issue is resolved. For the curious, here is the solution.
SELECT A.item, A.date, (select MIN(date) FROM yourTable B ON b.itemID
= A.itemID) AS orderDate
FROM yourTable A
ORDER BY orderDate, A.item, A.date
DRE
On Mon, 8 Nov 2004 10:45:39 -0800, DRE <[EMAIL PROTECTED]> wrote
Hi Jeff,
I see what you're trying to get at it but it seems not to work for me.
The query works but its not grouping properly in the output
statement. Heres why I think that is.
In the group by, you have to have all of the non aggregate fields
which include date. Now date is the one that I need
You just need to alter your order by:
order by itemid, date asc
The asc on the date won't affect the itemid order at all.
- Original Message -
From: "DRE" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Thursday, November 04, 2004 2:56 P
It's 16:00 here in Boston, and there are not many thoughts left at all.
I didn't quite understand what you are trying to do. Nor how it is not working now.
>From a general standpoint, there is seldom things you can do in local structures that
>SQL can't do for you better and faster.
(The cf-SQL
sorry that should probably be ASC, not DESC, if you want it to go low-high
-jc
Jeff Congdon wrote:
>make your query like this:
>
>SELECT item, date, MIN(date) AS orderDate
>FROM yourtable
>WHERE itemID = date_itemID
>GROUP BY item, date
>ORDER BY orderDate DESC, item, date
>
>-jc
>
>DRE wrote:
>
make your query like this:
SELECT item, date, MIN(date) AS orderDate
FROM yourtable
WHERE itemID = date_itemID
GROUP BY item, date
ORDER BY orderDate DESC, item, date
-jc
DRE wrote:
>Nobody has any thoughts?
>
>
>On Thu, 4 Nov 2004 08:14:30 -0800, DRE <[EMAIL PROTECTED]> wrote:
>
>
>>Hi,
>>I
Nobody has any thoughts?
On Thu, 4 Nov 2004 08:14:30 -0800, DRE <[EMAIL PROTECTED]> wrote:
> Hi,
> I have a tricky little issue. Maybe someone can help me.
>
> I have a query that returns a grouped output. There are items that
> have mutiple dates and those dates have to be in order.
>
> Kinda
Hi,
I have a tricky little issue. Maybe someone can help me.
I have a query that returns a grouped output. There are items that
have mutiple dates and those dates have to be in order.
Kinda like this.
item z datea dateb datec
item r datee datef dateg
so my query is like
select item, date
wher
9 matches
Mail list logo