Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

SQL looking for greater AND equal date

This is SQL 2000....

I have a table and data below. When dates are the same...i want to bring one row back (only bring back a1 row).

When dates are not the same..i want to bring back the oldest date...this row in the example below:
'a2','2011-02-11 10:57:22.080''5433'

How can I do this?
create table #test
(
  descr varchar(3),
  processDate datetime,
  shipId as varchar(4)
)


insert into #test ('a1','2011-02-13 16:57:22.080','1111') --same date for a1
insert into #test ('a1','2011-02-13 16:57:22.080','2222') --same date for a1
insert into #test ('a2','2011-02-11 10:57:22.080''5433') -- older date for a2
insert into #test ('a2','2011-02-12 15:57:22.080','5678') -- newer date for a2

select * from #test t1
 inner join #test t2 on t1.descr = t2.descr
where ??

Open in new window

0
Camillia
Asked:
Camillia
  • 2
1 Solution
 
Ephraim WangoyaCommented:

select shipid, descr, max(processDate)
from #test t1
group by shipid, descr, processDate
0
 
Ephraim WangoyaCommented:
Correction, dont group by processdate

select shipid, descr, max(processDate)
from #test t1
group by shipid, descr
0
 
CamilliaAuthor Commented:
no, that gives me all 4 rows.  I had some bugs in the example above...fixed them below.
create table #test
(
  descr varchar(3),
  processDate datetime,
  shipId  varchar(4)
)


insert into #test values ('a1','2011-02-13 16:57:22.080','1111') --same date for a1
insert into #test values ('a1','2011-02-13 16:57:22.080','2222') --same date for a1
insert into #test values ('a2','2011-02-11 10:57:22.080','5433') -- older date for a2
insert into #test values ('a2','2011-02-12 15:57:22.080','5678') -- newer date for a2

Open in new window

0
 
JoeNuvoCommented:
How about this one?

SELECT A.descr, A.processDate, MIN(A.shipId) AS shipId
FROM #test A
INNER JOIN
(
	SELECT descr, MIN(processDate) As processDate
	FROM #test
	GROUP BY descr
) B ON A.descr = B.descr AND A.processDate = B.processDate
GROUP BY A.descr, A.processDate

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now