Re: SQL help

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: 1997/10/30
Message-ID: <63a3ao$260@bgtnsc02.worldnet.att.net>#1/1

Try:

	select id,date 
	from visits v1
	where rank in (
		select max(rank) 
		from visits v2
		where v1.id=v2.id);

The subselect should return the highest rank value for a given ID. This query should then return that ID and also the date on which the highest rank occurred. Note that if you have a tie, i.e. two dates with the same rank, that both will be returned.

regards,

Jonathan Gennick

On Tue, 28 Oct 1997 12:34:24 -0500, "H. John C. Hopkins" <john_spam_free_at_hpe.ufl.edu> wrote:

>Table VISITS
>Fields ID, DATE, RANK
>
>ID is not a primary key (i.e., can have multiple occurrences of ID, each
>with different dates and ranks)
>
>Need to select id AND date where highest rank value.
>
>Thanks so much for your input.
>
>-John
>
>
>
>

---

gennick_at_worldnet.att.net
http://home.att.net/~gennick Received on Thu Oct 30 1997 - 00:00:00 CST