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.
Author |
Topic |
Exir
Posting Yak Master
151 Posts |
Posted - 2013-04-20 : 11:39:52
|
HiImagine this table :ID - Name - Date--------------------------1 - John - 2013/1/122 - Kate - 2012/12/63 - John - 2011/11/104 - Tom - 2013/04/205 - 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/20how should i write it ? |
|
|
Exir
Posting Yak Master
151 Posts |
Posted - 2013-04-20 : 13:09:08
|
sorry for askingit was so easySELECT * 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 askingit was so easySELECT * FROM [Table] except( select * from Table where Date<'2012/01/20')
That seems to be equivalent toSELECT * 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
|
CRYYYYINGIt doesnt return what i wantthe 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 rowsselect 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 yoursbut obviously your code is better and more professionallythank you very much |
|
|
|
|
|
|
|