SELECT highest value RIGHT character

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SELECT highest value RIGHT character

Author

Topic

zabarrie
Starting Member

2 Posts

Posted - 2011-06-14 : 05:15:38

Hi all,

Gotta poorly maintained, legacy product table which needs to be queried.

Each product has a unique ID varchar. Problem is, whoever maintained this table previously has used a weird ID system.

Each product has a 4 digit ID (e.g. 1234) but whenever an update of that product has been released the previous manager has put a hyphen then another digit (e.g. 1234-2) Yet, it gets worse. Some records have been deleted altogether.

Table looks a little bit like this:

ProdID
--------
1234
1234-2
4567
4567-3
8910
8910-2
8910-4

Essentially, all I need to do is state where the LEFT 4 digits are equal then SELECT the rows with the highest value to the RIGHT of the hypen. Using the above example table, we would need a query that returned the rows with IDs of 1234-2, 4567-3 and 8910-4.

I hope this makes sense. Any help would be greatly appreciated.

Many thanks,

Al

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-06-14 : 05:56:22

Try this may help you But its works on 2005 or higher

Select * from (Select Prodid,
row=dense_rank() over (partition by Cast(Substring(Prodid,1,4) as int) order by Cast(Substring(Prodid,6,7) as int) desc )
from #t) t
Where row=1

In Love... With Me!

zabarrie
Starting Member

2 Posts

Posted - 2011-06-14 : 06:14:22

That worked like a charm! I've checked the numbers against a COUNT query and they match perfectly!

Funny thing is I had considered attempting using some form of RANK query, but could not figure out exactly how it was done; amateurs, eh?

Many thanks for your swift, informative response.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-14 : 06:43:55

No need for windowed functions here.

select max(prodid) from table1 group by left(prodid, 4)

N 56°04'39.26"
E 12°55'05.63"

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-14 : 07:48:04

Hav any products had more than 9 updates or used letters after the hyphen?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-06-14 : 18:34:30

quote:
Originally posted by Peso

No need for windowed functions here.

select max(prodid) from table1 group by left(prodid, 4)

N 56°04'39.26"
E 12°55'05.63"

What happens when there are ten versions of the product? "1234-9" > "1234-10"

=======================================
I have never met a man so ignorant that I couldn't learn something from him. -Galileo Galilei, physicist and astronomer (1564-1642)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-15 : 00:13:54

OP has to tell us if that's the case.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources