Solved

How to select one record per day sql question. Simple!!

Posted on 2004-10-01
9
1,548 Views
Last Modified: 2012-05-05
I have a sample query

select a.row_id, b.name, trunc(a.created)
from   tbl a

I would like a query to return one record per day. The table has multiple rows for the same day.!
0
Comment
Question by:cyberonics
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 7

Expert Comment

by:Bigfam5
ID: 12200101

Select Distinct a.row_id, b.name, trunc(a.created)
from   tbl a
where a.created in (select MAX(a.created) from tabl a)

You could use either MAX or MIN
0
 
LVL 7

Expert Comment

by:Bigfam5
ID: 12200131
Sorry mis-read you question.  Leave out the WHERE
0
 

Author Comment

by:cyberonics
ID: 12200155
Bigfam5,

that does not work. I need one record per date. therefore if the table has 500 records and has 50 records a day, I just want to pick one record per day
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 3

Expert Comment

by:oratim
ID: 12200351
select * from tbl where row_id = ( select max(row_id) from tbl where created in (select distinct(trunc(created)) from tbl))


tim
0
 
LVL 3

Expert Comment

by:oratim
ID: 12200508
this is a bit better

select * from tbl where row_id = ( select max(row_id) from tbl group by trunc(created))

0
 
LVL 3

Accepted Solution

by:
oratim earned 20 total points
ID: 12200523
OOPS, fixed:

select * from tbl where row_id in ( select max(row_id) from tbl group by trunc(created))

tim
0
 

Author Comment

by:cyberonics
ID: 12200758
oratim,

I'm testing your query. My question is that will your query return only one row or will it return multiple rows as max usually returns one row!??

0
 
LVL 3

Expert Comment

by:oratim
ID: 12200833
this part

select max(row_id) from tbl group by trunc(created))

will filter the records to one rowid for each date, the MAX function is to avoid having to group by row_id, which would do nothing since I am assuming row_id is a unique index/PK.

then the outer select * will get the rest of the records to go with the row_id' s retrieved from the inner select, with only one record being returned for each date.

hope it works for you, I used a table I already have and it looked right, I just substituted you schema names in place of mine.


Tim

0
 
LVL 9

Expert Comment

by:pratikroy
ID: 12201141
select a.row_id, b.name, max(trunc(a.created))
from   tbl1 a, tbl2 b
wher a.row_id = b.row_id
group by trunc(a.created)

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

710 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