[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Perform a join on a filtered table

Posted on 2008-02-08
8
Medium Priority
?
200 Views
Last Modified: 2010-03-20
This question is related to another open question I have in the SQL section.

Can someone let me know if the following is possible? Keep in mind that my base table (Table1) contains millions of records, so I'm looking for the most efficient way to achieve this.

To simplify things, let's say Table1 contains 3 fields.  They are CustomerNumber, TransactionDate, and Comments.   Numerous occurences of the same CustomerNumber can appear in the table. The data in Table1 might look like this:

CustomerNumber             TransactionDate                      Comments
       111                                  1/1/1999                      This is an older comment for 111
       111                                 12/31/2007                   This is the newest comment for 111
       222                                   6/30/2004                   This is an older comment for 222
       222                                   10/1/2006                   This is a newer comment for 222
       222                                  1/1/2008                      This is the newest comment for 222

What I need:

I need  the most up-to-date record for each CustomerNumber so that I'll wind up with unique occurences of each CustomerNumber.  The reason I need this is because I will be joining on CustomerNumber from another table. People have already shown me how to get the most up-to-date record (by using MAX(TransactionDate), etc from Table1.  Originally what I was going to do was get the most up-to-date record for each CustomerNumber in Table1 and dump them into a new table. Do I need to do this though?  Or can I somehow run a query on Table1 to get the the unique CustomerNumbers (by selecting the most up to date record for each only) I'm talking about and then join my other table (which also contains a CustomerNumber field) on CustomerNumber of the 'results table/view' from the query I ran on Table1?
0
Comment
Question by:dbfromnewjersey
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 20850871
"Originally what I was going to do was get the most up-to-date record for each CustomerNumber in Table1 and dump them into a new table."

Why?  That looks like a *VERY* expensive (in terms of CPU cycles, disk I/O, disk space, etc.) way to get the job done.

Do you really need to copy the data to a new table, or do you just need to join the records needed in a query (in a report or screen for example)?

It is possible (and quite easy, and usually efficient) to use "MAX" in a sub-query to get just the latest record, then do a join on the resulting records to data from another table that has just one record for each CustomerNumber.
0
 

Author Comment

by:dbfromnewjersey
ID: 20851212
This all has to do with creating a report only.  Let me explain the situation a bit more and then maybe you can show me how to achieve it.  Like I said, I need to get the most up-to-date record for each CustomerNumber from Table1 so that I'll wind up with a table (or temporary table or whatever) of unique CustomerNumbers.  I then need to join (on CustomerNumber only) those results with a different table. That different table contains multiple occurences of CustomerNumber, which is fine.  What I need is  a 'list' of unique CustomerNumbers that I get from Table1 that I can use to join on the CustomerNumber of another table to produce a report.  All I'm trying to do is come up with a table or a temporary table or whatever containing unique CustomerNumbers that I can use to join on the CustomerNumber field of a different table.   It would seem to me though that every time I want to run a report that 'filtering' Table1 as you described and then joining on the filtered results would take a long time.  Either way, please show me how I would do what you described.
0
 
LVL 2

Expert Comment

by:ysd
ID: 20851218
to get the unique customer id, from table 1, u can achieve in two ways....1. on your existing query where you use the max, i.e.  "select customerid, max(transactiondate) from table1 group by customerid"
by adding the group by at the end, you will only have one occurance of the cusid.  Or the other way is you run a query with the keyword "distinct" which will give you unique values of the existing field.
i.e. Select distinct(customerid) from table1
you can try along these lines to achieve exactly what you want...Regarding the temp table....If you are dealling with a very large amount of data, ie. millions of records then its is advisable you use one, to avoid running exception errors due to memory buffer size.

hope it helps
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 2

Accepted Solution

by:
ysd earned 200 total points
ID: 20851219
to get the unique customer id, from table 1, u can achieve in two ways....1. on your existing query where you use the max, i.e.  "select customerid, max(transactiondate) from table1 group by customerid"
by adding the group by at the end, you will only have one occurance of the cusid.  Or the other way is you run a query with the keyword "distinct" which will give you unique values of the existing field.
i.e. Select distinct(customerid) from table1
you can try along these lines to achieve exactly what you want...Regarding the temp table....If you are dealling with a very large amount of data, ie. millions of records then its is advisable you use one, to avoid running exception errors due to memory buffer size.

hope it helps
0
 

Author Comment

by:dbfromnewjersey
ID: 20851431
OK.  Let's say I run the following SQL on Table1 to get the results I need (originally I was going to INSERT these results into a new table but I'll leave the INSERT out for now). Something along the lines of the following will give me unique occurences of CustomerNumber (the most up to date record of each).

Select   CustomerNumber,
            MAX(TransactionDate),
            Comments
From    Table1
Group By CustomerNumber

OK.  Now how would I join the CustomerNumber field from the results of the above query with the CustomerNumber field of my 'OtherTable'?
I need this all to run on one query; meaning a user might want to do a lookup on CustomerNumber '111'. So if her enters '111' for the query criteria, I want Table1 to be 'filtered' and then a join performed on the CustomerNumber of those filtered results with the CustomerNumber of my 'other table'.

0
 
LVL 6

Assisted Solution

by:jwittenm
jwittenm earned 200 total points
ID: 20852338
something like the following (not optimized)
SQL> desc foo
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 CNUM                                               NUMBER
 TDATE                                              DATE
 COMMENTS                                           VARCHAR2(10)

SQL> desc foo2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 CNUM                                               NUMBER
 COMMENTS2                                          VARCHAR2(10)

SQL> select * from foo;

      CNUM TDATE                COMMENTS
---------- -------------------- ----------
         1 08-FEB-2008 09:50:30 one
         2 09-FEB-2008 09:50:52 two
         2 10-FEB-2008 09:51:15 three
         3 11-FEB-2008 09:52:01 four

SQL> select * from foo2;

      CNUM COMMENTS2
---------- ----------
         1 one
         3 one
         2 one
         2 two
         2 three

select foonum.cnum, comments2 from
(select distinct cnum from(select cnum, max(tdate) from foo group by cnum) foonum,
foo2 f2
where foonum.cnum=f2.cnum
order by cnum;

SQL> l
  1  select foonum.cnum, comments2 from
  2  (select distinct cnum from(select cnum, max(tdate) from foo group by cnum))
 foonum,
  3  foo2 f2
  4  where foonum.cnum=f2.cnum
  5* order by cnum
SQL> /

      CNUM COMMENTS2
---------- ----------
         1 one
         2 one
         2 two
         2 three
         3 one

Now, if you only want one row per cnum, then you need to filter on table 2 (foo2) also.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 200 total points
ID: 20853078
You indicated originally that "I will be joining on CustomerNumber from another table".  So lets call this other table: Table2.  That table is apparently the "driving table" or the starting place that the database must work with, because your application allows users to specify criteria from that table, correct?

Then you need a query like this (if your database is Oracle):

select [column list]
from table2 t2, table1 t1
where t2.[some_column] = [criteria from user]
and t1.CustomerNumber = t2.CustomerNumber
and t1.rowid = (select min(t3.rowid) from table1 t3
where t3.TransactionDate = (select max(t4.TransactionDate)
 from table1 t4
  where t4.CustomerNumber = t2.CustomerNumber));

Notes:
1.  the "min" operator on "t3.rowid" is only to protect you from the (unlikely, I hope) possibility of two rows for the same CustomerNumber having the same TransactionDate.
2. No "group by" is needed, since the sub-queries filter the records in table1 down to just one per CustomerNumber
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 20853135
Also, be careful with the "select distinct..." syntax in Oracle!  The results may surprise you (unpleasantly!) and the performance (or lack thereof!) may seriously disappoint you.  This depends on many things though, so you may or may not be surprised or disappointed.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses

640 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question