Select query - SQL Server Forums

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

SQL Server Development (2000)

Select query

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2004-12-08 : 07:11:48

I have an account number for each of my clients and under that client they could have multiple enteries for each time they have contacted me

what I would like to do is do a select on all of the different account numbers but only return the last two entries for each number.

any hints?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-08 : 07:15:08

something like this:

Select accNumber, contact_date
From myTable as t1
Where (Select count(contact_date) from myTable Where accNumber = t1.accNumber and contact_date >= t1.contact_date)<=2
Order By accNumber, contact_date desc

Go with the flow & have fun! Else fight the flow

jparker
Posting Yak Master

118 Posts

Posted - 2004-12-08 : 07:42:12

Thanks spirit1 for your speedy reply

That worked great and did exactly what was asked but I have now found that the spec has changed a little

I have the following fields :

con_num ' account number
cust_ref ' customer reference
destination ' destination
con_date ' account date
stat_code ' status_code
summ_desc ' summary description
gmt_eve_date ' event day
gmt_eve_time ' Event time

What I am trying to achieve is to first group then order the con_num, gmt_eve_date and gmt_eve_time to give me the most recent events for that particular account number

In addition the account number is fed from a search so could be an exact match or could be LIKE. I am having problems first ordering and grouping the first part and then ensuring I get the right result. I should be getting back about 1000 records but at present I only have about 50

Here is what I have so far. Please don't laugh

Select con_num, cust_ref, l.location as destination, con_date, stat_code, summ_desc, gmt_eve_time, gmt_eve_date
from delivery as t1
 join location l on l.dep_code = t1.dest_dep 
where (select count(con_num) from delivery where con_num like '%8019%') <= 2
order by con_num,  gmt_eve_date desc, gmt_eve_time desc

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-08 : 07:49:18

try this:

Select con_num, cust_ref, l.location as destination, con_date, stat_code, summ_desc, gmt_eve_time, gmt_eve_date
from delivery as t1
join location l on l.dep_code = t1.dest_dep
where con_num like '%8019%' and
(select count(con_date) from delivery where con_num = t1.con_num and con_date >= t1.con_date) <= 2
order by con_num, gmt_eve_date desc, gmt_eve_time desc

Go with the flow & have fun! Else fight the flow

jparker
Posting Yak Master

118 Posts

Posted - 2004-12-08 : 07:54:32

I am getting 21 results from a possible data set of 5126 I would expect that figure to be at least 2000

I have tried losing the join and that makes no difference

if up up the figure <=2 to <= 25 I get up to 5059 i.e. back where we started so I guess its something to do with the where clause

jparker
Posting Yak Master

118 Posts

Posted - 2004-12-08 : 07:57:57

Sorry spirit1,

It was my fault I have worked it out now. I mis-informed you it wasn't on con_date but on gmt_eve_time seems to work fine now.

Thanks for your help

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-08 : 08:01:13

sure. glad you have it worked out...

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources