473,325 Members | 2,771 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Query to return single record from multiple like records

Not sure this can be done but here is my current situation. I have a table containing millions of records. Each record has 28 fields. One of the fields I am trying to return the single record for is Res_code. There can be multiple records with the same Res_code. Some of the matching Res_code records can have different values in any of the 28 fields and some may have exactly the same value in each field.

Example:
Res_Id Trans_Type Location Post_date
12345 N OKC 16-Jul-07
12345 N OKC 16-Jul-07
12345 Y OKC 16-Jul-07
12345 N OKC-AP 23-Jul-07

I have tryed a distict which will not work because I will receive 3 rows back. I have tried to use a partition with the same results. I have tried a sub query with the same results.
The only unique thing about these records is that the Post_date has a time stamp value, and each is different. I tried to do a max to_char using the time stamp but I still get back multiple rows.

Maybe I'm not seeing something that will work. I have asked our Oracle DBAs with no success.

Bottom line....I'm trying to perform a query which will only return me one row for each Res_id. And this row needs to be the most current row....this is where the post_date/time stamp come in.

If someone knows of a way to get these results I would appreciate any assistance.
Mar 6 '08 #1
1 6256
amitpatel66
2,367 Expert 2GB
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT x.res_id, y.transtype,y.location,x.pd FROM
  3. (SELECT res_id,MAX(Post_date) pd FROM table1 GROUP BY res_id) x, table1 y
  4. WHERE x.res_id = y.res_id
  5. AND x.post_date = y.post_date
  6.  
  7.  
Mar 6 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Hammy Hammy | last post by:
Hi all, I have an invoice table query that returns 10 records. ie. there are 10 invoices. When I try to join a subjects table to retrieve the subject name associated with an invoice it returns...
9
by: (Pete Cresswell) | last post by:
I've got some SQL that works as far as returning a recordset from a series of UNION statements. viz: SELECT whatever UNION this UNION that UNION other
2
by: Hammy Hammy | last post by:
Hi all, I have an invoice table query that returns 10 records. ie. there are 10 invoices. When I try to join a subjects table to retrieve the subject name associated with an invoice it returns...
10
by: Kenneth | last post by:
I have a Query that consist of a lot of different sales data, and one of the colums are different date. The date goes from 1jan2003 til 31jan2003. in this Query I only want the salesdata for...
8
by: Bill | last post by:
Hello out there; This may be a challenge but I'm certain it's possible but I can't seem to figure out how. I have a table that has several date fields, e.g., Date1, Date2, Date3, Date4 ......
3
by: Chuck | last post by:
Hi, I have a somewhat unique problem (at least unique to me, and I've been doing this for longer than I care to admit). I have a client that needs to print cards onto perforated card stock (so...
5
by: D. Shane Fowlkes | last post by:
This may be a very basic question but it's something I've never done before. I've looked at a couple of my favorite sites and books and can't find an answer either. I can write a Function to...
3
by: shorti | last post by:
db2 v 8.2 on AIX 5.3 I will try to explain as brief as I can what it is I need. I am building a function that will be called multiple times where I will need to return x amount of records each...
13
by: Neil | last post by:
I'm running an update query in SQL 7 from QA, and it runs forever. Has been running for 20 minutes so far! The query is quite simple: update a single field in a table, based on a join with another...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.