Hi.

This is my "customers" table dataset (simplified example):

Name
---------
Jill
...
John
---------

If if do the following "SELECT 1, Name FROM Customers GROUP BY 1" then it returns the value "Jill" (The first record).

I would like it to instead return the value "John" (The last record). How can I achieve this? (while using the GROUP BY clause)

"John" <eydunl@post.olivant.fo> wrote in message
news:5243C20F-481B-4226-9FA6-401BF9BBDE5B@news.elevatesoft.com...
> Hi.
>
> This is my "customers" table dataset (simplified example):
>
> Name
> ---------
> Jill
> ..
> John
> ---------
>
> If if do the following "SELECT 1, Name FROM Customers GROUP BY 1" then it
> returns the value "Jill" (The first record).
>
> I would like it to instead return the value "John" (The last record). How
> can I achieve this? (while using the GROUP BY clause)
>

You are using the wrong function

SELECT MAX(NAME) AS MaxName from table

Robert

Robert


Neat, I would never have thought of using MAX on an alpha column

Roy Lambert

Roy,

<< Neat, I would never have thought of using MAX on an alpha column >>

At one point with DBISAM (don't ask me when , you couldn't do so.  So,
that's probably where that comes from.

--
Tim Young
Elevate Software
www.elevatesoft.com

Robert, thank you for your suggestion. But, as I understand MAX on a alpha numeric column; it returns the last (max) value in alpha-numerical
order, and not the last record in the current order, as I was hoping for.

"John" <eydunl@post.olivant.fo> schrieb im Newsbeitrag
news:0EBD465A-C47D-4B19-8CE3-B1687158048F@news.elevatesoft.com...
> Robert, thank you for your suggestion. But, as I understand MAX on a alpha
> numeric column; it returns the last (max) value in alpha-numerical
> order, and not the last record in the current order, as I was hoping for.
>

Why don't you do a order by with your current index?

SELECT Name FROM Customers ORDER BY MyPrimaryKey DESC

This way the last record would be the first in your resultset.

Regards
Rolf

Yes, that is what I am doing. But I also need to do a GROUP BY on the same column as ORDER BY, or else it will return to many rows (because of
a JOIN LEFT that I am also doing). And when I use a GROUP BY, then DBISAM fetches the first Name value, and not the last Name value as I was
hoping.

"John" <eydunl@post.olivant.fo> schrieb im Newsbeitrag
news:F1463464-F54D-4B26-A5F3-F4A8783D8ABD@news.elevatesoft.com...
> Yes, that is what I am doing. But I also need to do a GROUP BY on the same
> column as ORDER BY, or else it will return to many rows (because of
> a JOIN LEFT that I am also doing). And when I use a GROUP BY, then DBISAM
> fetches the first Name value, and not the last Name value as I was
> hoping.
>

Have you tried this already?

SELECT Name FROM Customers ORDER BY MyPrimaryKey DESC TOP 1

This will return 1 record only.

John,

<< Yes, that is what I am doing. But I also need to do a GROUP BY on the
same column as ORDER BY, or else it will return to many rows (because of a
JOIN LEFT that I am also doing). And when I use a GROUP BY, then DBISAM
fetches the first Name value, and not the last Name value as I was hoping.
>>

It would help immensely if you were to post the actual SQL statement that
you're using, including the joins, etc., instead of having everyone offer
suggestions using an example SQL statement that does not match your needs.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim, my question was in my first post. It apparently is not possible, which I understand and accept. I have been able to solve my specific need by
using memory-tables. I appreciate all replies that were given.