SQL

  • Thread starter Thread starter subs
  • Start date Start date
S

subs

ocity ost ozip dcity dstate dzip consignee shipdate
a b c d e f cat 1/1/2008
h f g d e f cat 1/2/2008
p k l o z t cat 1/2/2008
l st mn d e f cat1 1/2/2008
otp ltp mtp dsc sto ppp cat2 2/3/2008
olm tma ots otaa iiiip ddddds sss 2/3/2008
ls st1 mn2 d e f cat1 1/2/2008

hi

i want to write a SQL query which can extract records which have same
dcity, dstate, dzip, shipdate and consignee but have different ocity,
ostate and ozip. In the above example, the first, second , fourth and
last record need to be extracted. The final result has to be sorted
such that you could see the pattern clearly. Can you please help with
SQL query
 
Could you have the same consignee with different dCity,dState, and dZip
values. If not, I wouldn't store all of that information in the table, I'd
have a consignee table that contains all of their address information.

However, given the structure you have provided, I think I would do this by
starting out with a aggregate query that identifies those record consignees
that have multiple shipments. Something like:

SELECT dCity, dState, dZip, consignee
FROM yourTable
GROUP BY dCity, dState, dZip, consignee
HAVING Count(*) > 1

If this is what you are looking for, then, I would join this query to your
table on all three fields and sort by consignee and shipdate.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Back
Top