Help with SQL script

abarefoot
abarefoot used Ask the Experts™
on
I'm having an issue with what I think is my group by.   Below is my script and results.  What I'm needing is one line.

my script
select inv_mast_uid ,unit_price, max(date_created ) as max_date
from po_line 
where inv_mast_uid = '2319'
group by inv_mast_uid, unit_price

Open in new window


my results
inv_mast_uid	    unit_price	    max_date
2319	            12.990000	        2004-04-28 12:45:07.000
2319	            14.700000	        2008-09-05 14:53:38.000
2319	            18.900000	        2009-11-20 10:22:49.000
2319	            19.450000	        2004-04-15 10:20:51.000
2319	            26.580000	        2005-06-03 18:17:30.000

Open in new window


what I'm looking for is this.
inv_mast_uid	    unit_price	    max_date
2319	           18.900000	       2009-11-20 10:22:49.000

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
This is untested but I think you want ROW_NUMBER:
select inv_mast_uid ,unit_price, date_created  as max_date
from (
	select inv_mast_uid ,unit_price, date_created,
		row_number() over(partition by inv_mast_uid order by date_created desc)  as rn
	from po_line 
	where inv_mast_uid = '2319'
) a
where rn=1

Open in new window


The partition by inv_mast_uid  is optional here since you are limiting the results to a single value.  I added it in case you want the MAX dates for several UIDs.

Author

Commented:
Yes that was it.  I've used row number a while back but forgot about it.  Thanks for your help.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Glad to help.

The WINDOW functions are good ones to remember.  They come in real handy and are pretty efficient compared to alternatives.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial