Hi,
I need write a script get data from 3 different. All 3 tables will have
emailAddr, modify_date and sum details. But information may be duplicated in
the 3 tables, having same emailAddr but different modify_date eg. My end
result(new table) will contain the entry with latest modify_date if there is
a duplication of emailAddr, else the entry that is only available in single
table will also be insert into the new table.
Thanx..Can you give the primary key foriegn key trlationships with the table
definition and few sample data and expected result?
--
"yingying" wrote:
> Hi,
> I need write a script get data from 3 different. All 3 tables will have
> emailAddr, modify_date and sum details. But information may be duplicated
in
> the 3 tables, having same emailAddr but different modify_date eg. My end
> result(new table) will contain the entry with latest modify_date if there
is
> a duplication of emailAddr, else the entry that is only available in singl
e
> table will also be insert into the new table.
> Thanx..|||The tables are not related to each other.. only field that is similiar is th
e
emailAddr.. this is the field i expected to check..
Eg.. (table structure)
tblA (a_Id, emailAddr, dateModify, fname, lname)
tblB (b_Id, emailAddr, dateModify, addr, contact)
tblC (c_Id, emailAddr, dateModify, work1, work2)
tblResult (r_Id, emailAddr, dateModify)
(data in db)
tblA
1, aa@.abc.com, 29/04/2006, aa, aa
2, bb@.abc.com, 28/04/2006, bb, bb
tblB
1, aa@.abc.com, 30/04/2006, bb, bb
2, cc@.abc.com, 01/05/2006, cc, cc
3, bb@.abc.com, 03/05/2006, bb, bb
tblC
1, aa@.abc.com, 03/05/2006, aa, aa
2, dd@.abc.com, 01/05/2006, dd, dd
(Expected result stored into tblResult)
tblResult
1, aa@.abc.com, 03/05/2006
2, bb@.abc.com, 03/05/2006
3, cc@.abc.com, 01/05/2006
4, dd@.abc.com, 01/05/2006
"Omnibuzz" wrote:
> Can you give the primary key foriegn key trlationships with the table
> definition and few sample data and expected result?
> --
>
>
> "yingying" wrote:
>|||try this and let me know if this was what you required
select emailAddr,max(datemodify) as datemodify
from
(
select emailAddr,datemodify from tblA
union all
select emailAddr,datemodify from tblB
union all
select emailAddr,datemodify from tblC
) as A
group by emailaddr|||of course r_id in the tbl_result can be an identity
--
"yingying" wrote:
> Hi,
> I need write a script get data from 3 different. All 3 tables will have
> emailAddr, modify_date and sum details. But information may be duplicated
in
> the 3 tables, having same emailAddr but different modify_date eg. My end
> result(new table) will contain the entry with latest modify_date if there
is
> a duplication of emailAddr, else the entry that is only available in singl
e
> table will also be insert into the new table.
> Thanx..|||Thanx.. I got the idea how it works..
Another question.. If i was to have same email addr in the same table how am
i goin to get the data with the latest date having the same email addr.
(table structure)
tblA (a_Id, emailAddr, dateModify, lname, fname)
(data in table)
tblA
1, aa@.abc.com, 03/05/2006, aa, aa
2, aa@.abc.com, 01/05/2006, bb, bb
3, aa@.abc.com, 29/04/2006, cc, cc
4, dd@.abc.com, 01/05/2006, dd, dd
5, dd@.abc.com, 02/05/2006, ee, ee
6, ff@.abc.com, 01/05/2006, ff, ff
(Expected result)
1, aa@.abc.com, 03/05/2006, aa, aa
5, dd@.abc.com, 02/05/2006, ee, ee
6, ff@.abc.com, 01/05/2006, ff, ff
"Omnibuzz" wrote:
> try this and let me know if this was what you required
> select emailAddr,max(datemodify) as datemodify
> from
> (
> select emailAddr,datemodify from tblA
> union all
> select emailAddr,datemodify from tblB
> union all
> select emailAddr,datemodify from tblC
> ) as A
> group by emailaddr
>|||The same query will work for your requirement.
--
"yingying" wrote:
> Thanx.. I got the idea how it works..
> Another question.. If i was to have same email addr in the same table how
am
> i goin to get the data with the latest date having the same email addr.
> (table structure)
> tblA (a_Id, emailAddr, dateModify, lname, fname)
> (data in table)
> tblA
> 1, aa@.abc.com, 03/05/2006, aa, aa
> 2, aa@.abc.com, 01/05/2006, bb, bb
> 3, aa@.abc.com, 29/04/2006, cc, cc
> 4, dd@.abc.com, 01/05/2006, dd, dd
> 5, dd@.abc.com, 02/05/2006, ee, ee
> 6, ff@.abc.com, 01/05/2006, ff, ff
> (Expected result)
> 1, aa@.abc.com, 03/05/2006, aa, aa
> 5, dd@.abc.com, 02/05/2006, ee, ee
> 6, ff@.abc.com, 01/05/2006, ff, ff
>
> "Omnibuzz" wrote:
>|||I used same query but it did not return the right values i need..
select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
from
(
select emailAddr, datestamp, fname, lname from tblA
) As A
group by emailAddr, fname, lname
(data in tblA)
1, aa@.abc.com, 29/04/2006, aa, aa
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
4, aa@.abc.com, 03/05/2006, aa2, aa2
(Expected result)
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
4, aa@.abc.com, 03/05/2006, aa2, aa2
but wat i got was
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
1, aa@.abc.com, 29/04/2006, aa, aa
4, aa@.abc.com, 03/05/2006, aa2, aa2
If cases with entry of same email addr, i will need to get the entry with
the latest date. When i didn't include the fname and lname, the result was
ok.. but after i add those 2 fields, the result was not wat i need.
Isit that i need to do this in 2 different steps in order to get the
required data'
Another question, when i have fname, lname in the 'select' query and not
having them in the 'group by', it gave me an error..
-->>
'A.fname' is invalid in the select list because it is not contained in
either an aggregate function or the GROUP BY clause.
what does this mean?
Thanx..
"Omnibuzz" wrote:
> The same query will work for your requirement.
> --
>
>
> "yingying" wrote:
>|||If you want the additional details with the latest date, try
select
emailAddr, datestamp as datemodify, fname, lname
into #temp
from tblA
where datestamp = (
select max(datestamp) from tblA as A2
where A2.emailAddr = tblA.emailAddr
)
nearly equivalent variations (differing in the case
where there are ties for the datestamp value, or
nullable columns) include
...
from tblA
where datestamp = (
select top 1 datestamp from tblA as A2
where A2.emailAddr = tblA.emailAddr
order by datestamp desc
)
and
...
from tblA
where not exists (
select * from tblA as A2
where A2.emailAddr = tblA.emailAddr
and A2.datestamp > tblA.datestamp
)
or in SQL Server 2005,
with Ranked(emailAddr, datestamp, fname, lname, rk) as (
select
emailAddr, datestamp, fname, lname,
rank() over (partition by emailAddr order by datestamp desc)
from tblA
)
select emailAddr, datestamp as datemodify, fname, lname
into #temp
from Ranked
where rk = 1
Steve Kass
Drew University
yingying wrote:
>I used same query but it did not return the right values i need..
>select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
>from
>(
>select emailAddr, datestamp, fname, lname from tblA
> ) As A
>group by emailAddr, fname, lname
>(data in tblA)
>1, aa@.abc.com, 29/04/2006, aa, aa
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>(Expected result)
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>but wat i got was
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>1, aa@.abc.com, 29/04/2006, aa, aa
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>If cases with entry of same email addr, i will need to get the entry with
>the latest date. When i didn't include the fname and lname, the result was
>ok.. but after i add those 2 fields, the result was not wat i need.
>Isit that i need to do this in 2 different steps in order to get the
>required data'
>Another question, when i have fname, lname in the 'select' query and not
>having them in the 'group by', it gave me an error..
>-->>
>'A.fname' is invalid in the select list because it is not contained in
>either an aggregate function or the GROUP BY clause.
>what does this mean?
>Thanx..
>"Omnibuzz" wrote:
>
>|||If you are selecting more columns than what you had specified, then you will
have to use a correlated sub-query.
--
"yingying" wrote:
> I used same query but it did not return the right values i need..
> select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
> from
> (
> select emailAddr, datestamp, fname, lname from tblA
> ) As A
> group by emailAddr, fname, lname
> (data in tblA)
> 1, aa@.abc.com, 29/04/2006, aa, aa
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> (Expected result)
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> but wat i got was
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 1, aa@.abc.com, 29/04/2006, aa, aa
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> If cases with entry of same email addr, i will need to get the entry with
> the latest date. When i didn't include the fname and lname, the result was
> ok.. but after i add those 2 fields, the result was not wat i need.
> Isit that i need to do this in 2 different steps in order to get the
> required data'
> Another question, when i have fname, lname in the 'select' query and not
> having them in the 'group by', it gave me an error..
> -->>
> 'A.fname' is invalid in the select list because it is not contained in
> either an aggregate function or the GROUP BY clause.
> what does this mean?
> Thanx..
> "Omnibuzz" wrote:
>
