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 |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2007-02-26 : 05:57:58
|
| i have a query select * from customers where pl not in(select pl from archived customers)how can i get only the unique pl and if there is more then one then it should pull up the record of the most recent pl |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-26 : 06:03:57
|
| What is the criteria for "Most Recent"?I am bored of telling this: Please post table structure, sample data and expected output.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-02-26 : 06:13:11
|
| How to know what is the most Recent records??? This is one way.. Select * From Customers c Where C.Date = ( Select Max(C1.Date) From Customers c1 where C1.pl = C2.pl)If its some numeric feild then u can replace the date from my above query to that feild and it should work fineChiraghttp://chirikworld.blogspot.com/ |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2007-02-26 : 06:15:14
|
| it's not numeric -- it's a varchari just want the unique ones |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-02-26 : 06:19:32
|
| if you just want the unique one then you can replace that date column with that varchar column.. hope fully it should work fineChiraghttp://chirikworld.blogspot.com/ |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2007-02-26 : 06:26:36
|
| there is another field in the db date that i can use for most recent records -- but the pl field should not be unique and that's a varchar field |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-02-26 : 06:29:53
|
| It doesnt matter if the pl field is varchar.. just use the above query .. it should pull out the record which are recent once....Chiraghttp://chirikworld.blogspot.com/ |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2007-02-26 : 08:44:43
|
| not really working but i may be doing it wrong... here's my revised query that worksselect count(distinct registration) as mycount from temp where registration not in(select dvla from rep) and sent=0"the above is the count -- now the tricky thing is how do I return the list of records matching that count but in the case where there is more then one record it would pick the last record (there is a date field called dateofo in the table)thanks for your help |
|
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-02-26 : 08:55:21
|
| [code]select * from temp twhere Not Exists ( select 1 from rep r Where r.Dvla = t.registration )And DateInfo = ( Select Max(DateInfo) From Temp t1 Where t.registration = t1.registration )And sent=0[/code]Chiraghttp://chirikworld.blogspot.com/ |
|
|
|
|
|
|
qry help