max function

[johk02]

Hi,
Are there anyway of getting more columns from a Max query?? I am using query
SELECT MAX(MemberID) AS InitNo
FROM tbl_member

but would like to retrieve more info from that query like

SELECT MAX(MemberID) AS InitNo, MemberNoInitials, ReferredBy
FROM tbl_member

Any suggestions

Thanks
jonas

 

[cheerio]

SELECT MAX(MemberID) AS InitNo, MemberNoInitials, ReferredBy
FROM tbl_member
GROUP BY MemberNoInitials, ReferredBy

 

[johk02]

Thanks mate
It seems to do the trick. I added Order by DESC as well.

Thanks

 

[PHV]

Depending of your real issue you may try this:
SELECT * FROM tbl_member
WHERE MemberID=(SELECT MAX(MemberID) FROM tbl_member)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

johk02,

every field that you include in the select statement that isn't an aggregate (like SUM, AVG, COUNT) you have to add to the Group By statement.

For instance, if you needed to add AField to this query, you would also need to add that field to the Group By clause:

SELECT MAX(MemberID) AS InitNo, MemberNoInitials, ReferredBy, AField
FROM tbl_member
GROUP BY MemberNoInitials, ReferredBy, AField

Leslie