Home » SQL & PL/SQL » SQL & PL/SQL » Supressing count on timestamp
Supressing count on timestamp [message #406756] Fri, 05 June 2009 11:38 Go to next message
bunnybucukcomel
Messages: 1
Registered: June 2009
Location: United Kingdom
Junior Member
Hi all,

I am having problem to find a way to only display the occurance date (timestamp) per site_id and post_id to only display once

Currently it is displaying like this:


Site_id post_id date count 111111 290 04-JUN-09 10.57.52.000000000 7
111111 290 04-JUN-09 10.47.30.000000000 7
111111 290 04-JUN-09 10.37.09.000000000 7
111111 290 04-JUN-09 10.26.48.000000000 7
111111 290 04-JUN-09 10.16.27.000000000 7
111111 290 04-JUN-09 10.06.06.000000000 7
111111 290 04-JUN-09 09.51.29.000000000 7
120002 2 04-JUN-09 19.51.19.000000000 2
120002 2 04-JUN-09 19.40.58.000000000 2


Managed to change up to:

Site_id post_id date count( 111111 290 04-JUN-09 10.57.52.000000000 7
04-JUN-09 10.47.30.000000000 7
04-JUN-09 10.37.09.000000000 7
04-JUN-09 10.26.48.000000000 7
04-JUN-09 10.16.27.000000000 7
04-JUN-09 10.06.06.000000000 7
04-JUN-09 09.51.29.000000000 7
120002 2 04-JUN-09 19.51.19.000000000 2
04-JUN-09 19.40.58.000000000 2


The only thing I can't remove is the column count(pos.id) over (partition by pos.id).. have u got any ideas how to supress the redundancy?
I've tried partition, rank(),cube, model and every several attempt including grouping_id().
I have also tried to do count(pos_id) but it seems it count each individual as unique. Nothing seems to work....been googling the whole day...



In case u were wondering how i did supressing the other two column is:

select to_number(
decode(lag(site_id)over(order by site_id),
site_id,null,site_id)
) site_id, id
from table_name;


Help is appreciated.

Many thanks
Re: Supressing count on timestamp [message #406757 is a reply to message #406756] Fri, 05 June 2009 11:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Supressing count on timestamp [message #406763 is a reply to message #406756] Fri, 05 June 2009 12:03 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To precise BlackSwan's post:
Post a working Test case: create table and insert statements along with the result you want with these data.
Read "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: How to check entire table count before delete
Next Topic: different results in differen databases on the same tables containing the same data (merged 4)
Goto Forum:
  


Current Time: Sat Dec 10 11:10:31 CST 2016

Total time taken to generate the page: 0.11316 seconds