SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select max dates which is less than ...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Exir
Posting Yak Master

151 Posts

Posted - 04/20/2013 :  11:39:52  Show Profile  Reply with Quote
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 - 04/20/2013 :  13:00:02  Show Profile  Reply with Quote
We can choose the names which has no date greater than 2012/01/20
how should i write it ?
Go to Top of Page

Exir
Posting Yak Master

151 Posts

Posted - 04/20/2013 :  13:09:08  Show Profile  Reply with Quote
sorry for asking
it was so easy

SELECT * FROM [Table]

except( select * from Table where Date<'2012/01/20')
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/20/2013 :  13:13:38  Show Profile  Reply with Quote
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 :)
Go to Top of Page

Exir
Posting Yak Master

151 Posts

Posted - 04/20/2013 :  13:38:53  Show Profile  Reply with Quote
CRYYYYING

It doesnt return what i want
the challenge on the first post is still exist :(
Go to Top of Page

Exir
Posting Yak Master

151 Posts

Posted - 04/20/2013 :  13:40:53  Show Profile  Reply with Quote
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 ?

Edited by - Exir on 04/20/2013 13:48:19
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 04/20/2013 :  15:08:21  Show Profile  Reply with Quote
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';
Go to Top of Page

Exir
Posting Yak Master

151 Posts

Posted - 04/20/2013 :  15:27:32  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000