Order before grouping

[lemmin]

I have a table with two columns:

 

ID    Date

1      2010-9-28

1      2010-9-27

2      2010-8-28

2      2010-8-27

 

 

I want to group the ids and return the earliest (and later, the latest) dates corresponding to that id. for example:

 

1      2010-9-28

2      2010-8-28

 

 

The problem is that the grouping is done before the ordering. I would use HAVING, but I don't have any solid value to create a condition. All the solutions I come up with involve a ridiculous amount of sub-queries. Any ideas?

 

Thanks for any help.

[roopurt18]

select min( date ), max( date ) from table group by id

[lemmin]

Thanks for the reply. I guess I didn't mean to say that I wanted to return the dates. I actually need to return the ID column. I tried doing a "WHERE MAX(date) = date" but that threw an error. I also tried with a HAVING clause.

 

This was the best I could come up with:

SELECT
			   (SELECT ID FROM dates WHERE ID = d.ID ORDER BY Date LIMIT 1) sid
			FROM
			   ids d
			GROUP BY
			   d.ID

 

Basically goes through all of the possible ids and gets the max for each one. I can't figure out how to make it work if I want it to only return the id if there are more than one entry with the same id.

 

Thanks for the help.

[kickstart]

Hi

 

Think the code given earlier is almost what you want

 

SELECT id, MIN( date ), MAX( date )
FROM table 
GROUP BY id

 

If you only want ones where there is more than one row for the id

 

SELECT id, MIN( date ), MAX( date ), COUNT(*) AS DateCount
FROM table 
GROUP BY id
HAVING DateCount > 1

 

However I am not certain that is what you want

 

All the best

 

Keith

[roopurt18]

If you want to select the IDs having the max date and min date:

select id from the_table where date = (select max( date ) from the table)
union
select id from the_table where date = (select min(date) from the table)