select max dates which is less than ...

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 2008 Forums

Transact-SQL (2008)

select max dates which is less than ...

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2013-04-20 : 11:39:52

Hi
Imagine this table :

ID - Name - Date
--------------------------
1 - John - 2013/1/12
2 - Kate - 2012/12/6
3 - John - 2011/11/10
4 - Tom - 2013/04/20
5 - Tom - 2012/01/02
.......

I want to have a query which selects Names which the grater date of their registration is less than special time (for example 2012/01/20)
How is this query in sql ?
thanks for your help

Exir
Posting Yak Master

151 Posts

Posted - 2013-04-20 : 13:00:02

We can choose the names which has no date greater than 2012/01/20
how should i write it ?

Exir
Posting Yak Master

151 Posts

Posted - 2013-04-20 : 13:09:08

sorry for asking
it was so easy

SELECT * FROM [Table]

except( select * from Table where Date<'2012/01/20')

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-20 : 13:13:38

quote:
Originally posted by Exir

sorry for asking
it was so easy

SELECT * FROM [Table]

except( select * from Table where Date<'2012/01/20')

That seems to be equivalent to

SELECT * FROM [Table] WHERE Date >= '20120120'

However, from your original posting, I thought you wanted something slightly different, but if this works, I guess I interpreted it incorrectly :)

Exir
Posting Yak Master

151 Posts

Posted - 2013-04-20 : 13:38:53

CRYYYYING

It doesnt return what i want
the challenge on the first post is still exist :(

Exir
Posting Yak Master

151 Posts

Posted - 2013-04-20 : 13:40:53

quote:
However, from your original posting, I thought you wanted something slightly different, but if this works, I guess I interpreted it incorrectly :)

Yes James you are right, It didnt work. could you please help me ?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-20 : 15:08:21

I didn't 100% understand what you wanted to get, but will either of these give you what you want?

-- users whose the latest registration date is less than Jan 1, 2012
-- Your example data should return no rows
select name from [Table] group by name having max(date) < '20120120';

-- users who have at least one registration date that is less than Jan 1 2012
-- Your example data should return John and Tom.
select name from [Table] group by name having min(date) < '20120120';

Exir
Posting Yak Master

151 Posts

Posted - 2013-04-20 : 15:27:32

quote:

select name from [Table] group by name having max(date) < '20120120';

Thank you. this is what i wanted.

I wrote this code by myself :

( select distinct name from [Table] where date<'20120120')
except (select distinct name from [Table] where date>'20120120')

It works too and has the same answer as yours
but obviously your code is better and more professionally
thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources