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

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.!
cyberonicsAsked:
Who is Participating?
 
oratimConnect With a Mentor Commented:
OOPS, fixed:

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

tim
0
 
Bigfam5Commented:

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
 
Bigfam5Commented:
Sorry mis-read you question.  Leave out the WHERE
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
cyberonicsAuthor Commented:
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
 
oratimCommented:
select * from tbl where row_id = ( select max(row_id) from tbl where created in (select distinct(trunc(created)) from tbl))


tim
0
 
oratimCommented:
this is a bit better

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

0
 
cyberonicsAuthor Commented:
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
 
oratimCommented:
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
 
pratikroyCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.