- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: How to retrieve the row which has a maximum va...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-30-2007 06:04 PM
тАО01-30-2007 06:04 PM
I am using a GROUP BY in a query as follows -->
SELECT * FROM log GROUP BY name ORDER BY name,arrive,id desc
I want the rows which have the maximum id in the result of each "group by".
Please tell me how to do that ?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-30-2007 06:34 PM
тАО01-30-2007 06:34 PM
Re: How to retrieve the row which has a maximum value in a result of GROUP BY ?
First of all you need to use a group by function in the select.
Valid functions in a GROUP BY statement are count(), sum(), min(), max() , avg() etc..
In your case:
SELECT max(id), arrive, name FROM log
GROUP BY arrive, name
name ORDER BY name,arrive,id desc
You can also then cascade the query by
doing a select on a select, in case you get
multiple rows and you only want the most or least for example
Thus:
Select max(alias)from
(SELECT max(id) alias , arrive, name FROM log
GROUP BY arrive, name
name ORDER BY name,arrive,id desc)
etc .. etc..
If you need to know more let me know:)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-30-2007 06:37 PM
тАО01-30-2007 06:37 PM
Re: How to retrieve the row which has a maximum value in a result of GROUP BY ?
SELECT max(id), arrive, name FROM log
GROUP BY arrive, name
ORDER BY name,arrive,id desc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-30-2007 09:03 PM
тАО01-30-2007 09:03 PM
Re: How to retrieve the row which has a maximum value in a result of GROUP BY ?
SELECT max(id),arrive,name FROM log group by name order by name,arrive
Its working fine.
do u find any problem ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-30-2007 09:38 PM
тАО01-30-2007 09:38 PM
Re: How to retrieve the row which has a maximum value in a result of GROUP BY ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-31-2007 03:48 PM
тАО01-31-2007 03:48 PM
Re: How to retrieve the row which has a maximum value in a result of GROUP BY ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-31-2007 05:36 PM
тАО01-31-2007 05:36 PM
Re: How to retrieve the row which has a maximum value in a result of GROUP BY ?
SELECT id as highest,arrive,start,expire,name FROM log where id in(select max(id) from log group by name) order by name
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-31-2007 07:45 PM
тАО01-31-2007 07:45 PM
Re: How to retrieve the row which has a maximum value in a result of GROUP BY ?
it appears you have improved from the basic input I gave you. Well done.
I translated your script to a situation
with for instance objects and it works well:)
SELECT object_id as highest,object_name ,object_type,created FROM dba_objects
where object_id in (select max(object_id) from dba_objects group by object_type)
order by object_id
I thank you .
Cheers :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-31-2007 09:14 PM
тАО01-31-2007 09:14 PM
Re: How to retrieve the row which has a maximum value in a result of GROUP BY ?
My doubt is that why "highest" is used in the above query ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО01-31-2007 11:14 PM