Help with SELECT - SQL Server Forums

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)

Help with SELECT

Author

Topic

expat
Starting Member

7 Posts

Posted - 2010-06-15 : 18:49:52

Hi guys.

I am new to this forum. I am a Norwegian man, but work for a Thai company in Bangkok.

Here is my problem:
The following table contains tax information and I need to SELECT the newest version. The newest version have the highest Version no. So in this case i need to select TaxId 1 and 3.
Here is the data:

TaxId TaxName TaxSystemName TaxRate Version
1 Service Charge Service Charge Thailand 2.0 1
2 VAT VAT Thailand 7.0 1
3 VAT VAT Thailand 7.5 2

Can please someone help me. I am stuck in this case

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-06-15 : 20:14:01

[CODE]SELECT TaxId, TaxName, TaxSystemName, TaxRate, Version
FROM
(
select TaxId, TaxName, TaxSystemName, TaxRate, Version,
row_number() over (
partition by TaxName
order by
ParseName(Version, 3) * 10000 +
ParseName(Version, 2) * 100 +
ParseName(Version, 1) DESC
) RowNo
from
TaxTable
) a
where
RowNo = 1[/CODE]

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

expat
Starting Member

7 Posts

Posted - 2010-06-15 : 20:27:30

Thank you for your answer, but your query select rows with lowest version no. The higest version no is the last version.

What I need to select is:

TaxId 1 and 3 (TaxId 1 have only one version and TaxId 3 have 2 versions where TaxId 3 is the latest one (version no 2)

I am sorry if my explanation are confusing.

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-16 : 03:36:56

Hi Try this
Hope this is what you are looking for.
----------------------------------------------------------------------------------------
Create Table Taxx (
TaxId INT , TaxName varchar(100) ,TaxSystemName varchar(100), TaxRate int , Version int)
--1 Service Charge Service Charge Thailand 2.0 1
--2 VAT VAT Thailand 7.0 1
--3 VAT VAT Thailand 7.5 2

INSERT INTO Taxx values (1, 'Service Charge', 'Service Charge Thailand', 2.0 ,1)
INSERT INTO Taxx values (2, 'VAT', 'VAT Thailand', 7.0 ,1)
INSERT INTO Taxx values (3, 'VAT', 'VAT Thailand', 7.5 ,2)
Select * from Taxx

Select T.TaxId, T.TaxName , (T.Version)
FROm Taxx T ,
(Select TaxName, max(Version) AS Version From Taxx GROUP BY TaxName) as MTV
WHERE T.TaxName = MTV.TaxName AND T.Version = MTV.Version

----------------------------------------------------------------------------------------

Lets unLearn

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-16 : 05:08:15

what is the data type for version ? integer or string ?

if it just integer, change BK's query


SELECT TaxId, TaxName, TaxSystemName, TaxRate, Version
FROM 
	(
	select TaxId, TaxName, TaxSystemName, TaxRate, Version,
		row_number() over (
			partition by TaxName 
			order by 
				ParseName(Version, 3) * 10000 +
				ParseName(Version, 2) * 100 +
				ParseName(Version, 1) Version DESC
			) RowNo
	from
		TaxTable
	) a
where
	RowNo = 1

KH
[spoiler]Time is always against us[/spoiler]

expat
Starting Member

7 Posts

Posted - 2010-06-16 : 06:13:00

Version is an integer and it work like a charm :)

Thank you all for helping

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources