join help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

join help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-08 : 09:36:24

I have

select min(m.date),max(m.date) from m where type='x' and p='test'

how can I join this so that i can get the id of the min date and the max date instead of just returning the date of the min or the max

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-03-08 : 11:57:58

something like this...

with A as
(
   select
      row_number() over (order by date asc) as asc_rownum,
      row_number() over (order by date desc) as desc_rownum,
      idcolumn,
      date
   from
      yourtable
   where
      type = 'x' and p='test'
)
select
   idcolumn, date from A
where
   asc_rownum = 1 or desc_rownum = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-08 : 12:45:52

also


SELECT idcolumn,
      date
FROM
(
SELECT MIN(date) OVER () AS MinDate,
MAX(date) OVER () AS MaxDate,
idcolumn,
      date
   from
      yourtable
   where
      type = 'x' and p='test'
)t
WHERE date=MinDate 
OR date=MaxDate

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2009-03-08 : 14:10:15

perfect - thanks :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-08 : 14:19:07

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources