Home » SQL & PL/SQL » SQL & PL/SQL » Reg: Latest values....
Reg: Latest values.... [message #214961] Thu, 18 January 2007 14:47 Go to next message
sidagam
Messages: 24
Registered: February 2005
Location: Hyderabad
Junior Member
Hi All,

The problem is prettly old and simple but i need maximum and best solutions....

I have the table with n number of columns with data like below.

ColumnA .........................ColumnN
_________________________________________________
ABCD............................ 01-Jan-2007
EFGH.............................12-Oct-2006
IJKL.............................13-Aug-2006
............................................
............................................
............................................
............................................
ABCD............................ 30-Jul-2006
EFGH.............................21-Mar-2006
IJKL.............................18-Nov-2006
............................................
............................................
............................................
............................................
ABCD............................ 17-Jan-2007
EFGH.............................24-Apr-2006
IJKL.............................05-Sep-2006
............................................
............................................


Then i need the result like..........
ColumnA .........................ColumnN
_________________________________________________
ABCD............................ 17-Jan-2007
EFGH.............................12-Oct-2006
IJKL.............................18-Nov-2006

It means, i need the latest data of ColumnA values. And i need all other columns also in the query....
Expecting all the possible queries and also best feasible query...
Could you all please provide me this...


Thanks in Advance.

Thanks & Regards,

Babu SRSB.

Re: Reg: Latest values.... [message #214973 is a reply to message #214961] Thu, 18 January 2007 15:54 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
So, what is your best shot? Then maybe we can suggest other/better/worse solutions.

[Updated on: Thu, 18 January 2007 15:54]

Report message to a moderator

Re: Reg: Latest values.... [message #214976 is a reply to message #214973] Thu, 18 January 2007 16:10 Go to previous messageGo to next message
sidagam
Messages: 24
Registered: February 2005
Location: Hyderabad
Junior Member
I need all possible solutions...

Smile

Thanks & Regards,

Babu SRSB
Re: Reg: Latest values.... [message #214978 is a reply to message #214961] Thu, 18 January 2007 16:17 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>I need all possible solutions...
Then you had better start typing.
With advice you can have it good, fast or cheap. Pick any 2.
Re: Reg: Latest values.... [message #214980 is a reply to message #214978] Thu, 18 January 2007 17:07 Go to previous messageGo to next message
sidagam
Messages: 24
Registered: February 2005
Location: Hyderabad
Junior Member
My apologies for my insuffecient written communication skills...

Please advise me, if i approached wrong place for this requirement...I think, i can get the help for my requirement...

I have one possible solution is....

SELECT * FROM TABLE_A
WHERE (ColumnA, ColumnN) IN
(SELECT A1.ColumnA, MAX(A1.ColumnN) FROM TABLE_A A1
GROUP BY A1.ColumnA)

But i have 1 small doubt, about performance wise...
This same result we can get with some type of query with better performance.
If anybody have, please provide me...

My apologies once agian, if i hurt anybody...

Thanks & Regards,

Babu SRSB.
Re: Reg: Latest values.... [message #215048 is a reply to message #214980] Fri, 19 January 2007 01:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
And a search for "Top N" didn't reveal anything?

MHE
Re: Reg: Latest values.... [message #215513 is a reply to message #215048] Mon, 22 January 2007 11:25 Go to previous messageGo to next message
sidagam
Messages: 24
Registered: February 2005
Location: Hyderabad
Junior Member
Hi All,

One more query is...

SELECT * FROM
(
SELECT ColumnA,.......ColumnN,
ROW_NUMBER() OVER (PARTITION BY ColumnN ORDER BY ColumnN DESC)RN
FROM TABLE_A
)
WHERE RN = 1

Could any body please provide another type of solution...

Awaiting your valuble queries...

Thanks & Regards,

Babu SRSB.
Re: Reg: Latest values.... [message #215521 is a reply to message #214961] Mon, 22 January 2007 12:37 Go to previous messageGo to next message
mike7
Messages: 3
Registered: January 2007
Junior Member
A minor re-write of your first example is:

SELECT *
FROM TABLE_A
WHERE (ColumnN) = (SELECT MAX(A1.ColumnN)
FROM TABLE_A A1
WHERE a1.columnA = A1.columnA)

If performance is bad, you might need an index on columnA or columns A and N? That's just a guess though. I'm not the best at this and I don't have much information on the table you are using.

Good luck!

Mike
Re: Reg: Latest values.... [message #215540 is a reply to message #215521] Mon, 22 January 2007 16:21 Go to previous messageGo to next message
sidagam
Messages: 24
Registered: February 2005
Location: Hyderabad
Junior Member
Thanks a lot Mike..

My concern is performance.
To test the performance, i doesnot have enough data right now.
Thats the reason, i requested mulitiple queries of the same requirement.

You are correct.
The query you are provided is one more best query...
Once again Thanks....

Thanks & Regards,

Babu SRSB.
Re: Reg: Latest values.... [message #215543 is a reply to message #215540] Mon, 22 January 2007 18:01 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you index (columnA, columnN) AND gather statistics, then Mike's solution will work very well if there are many (say, >100) rows for each value of ColumnA.

If there are only a few values of ColumnA, somthing like the ROW_NUMBER solution posted would be good. Also, this may be a little faster:
SELECT columnA
,      MAX(columnB) KEEP (DENSE_RANK LAST ORDER BY columnN)
,      MAX(columnC) KEEP (DENSE_RANK LAST ORDER BY columnN)
...
,      MAX(columnN
FROM   tbl
GROUP BY columnA


Ross Leishman
Previous Topic: Good SQL statement
Next Topic: small problem in calling function
Goto Forum:
  


Current Time: Wed Dec 07 20:21:29 CST 2016

Total time taken to generate the page: 0.07383 seconds